Exploding a dataframe in pandas

Question

I have a dataframe like this, where the codes column is currently strings.

Station	Codes
1	1,2
1	1
2	1
2	2,5
2	2,3
3	1

I want to see the count of each code ordered by station. I have tried to use the explode function but the default behavior is to overwrite all strings with only one number as NaN.

Station	Codes	Count
1	1	2
1	2	1
2	1	1
2	2	2
2	3	1
2	5	1
3	1	1

Andrej Kesely · Accepted Answer · 2021-04-02 16:34:22Z

3

print(
    df.assign(Codes=df.Codes.str.split(","))
    .explode("Codes")
    .groupby(["Station", "Codes"], as_index=False)
    .size()
    .rename(columns={"size": "Count"})
)

Prints:

   Station Codes  Count
0        1     1      2
1        1     2      1
2        2     1      1
3        2     2      2
4        2     3      1
5        2     5      1
6        3     1      1

answered Apr 2, 2021 at 16:34

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris · Accepted Answer · 2021-04-02 16:35:08Z

2

df['Codes'] = df['Codes'].str.split(',')
df.explode('Codes').groupby('Station')['Codes'].value_counts().reset_index(name='Count')

answered Apr 2, 2021 at 16:35

Chris

16.3k3 gold badges26 silver badges41 bronze badges

1 Comment

silver_turtle Over a year ago

I have tried using this approach as well, but using .split(",") results in NaN values in the cells that only have one number (no comma)

Collectives™ on Stack Overflow

Exploding a dataframe in pandas

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related