Python DataFrame: count of occurances based on another column

Question

I have a Python Data Frame of teams and a place that they have achieved (1, 2 or 3)

I want to manipulate the df to look like this below. So it is a count of how often a team has achieved each place.

Team	1	2	3
A	5	1	1
B	0	2	0

mozway · Accepted Answer · 2022-02-01 13:57:05Z

1

pd.crosstab(df['Team'], df['place'])

or a simple groupby+size and unstack:

(df.groupby(['Team', 'place']).size()
   .unstack('place', fill_value=0)
)

output:

place  1  2  3
Team          
A      5  1  1
B      0  2  0

(pd.crosstab(df['Team'], df['place'])
   .rename_axis(columns=None)
   .reset_index()
)

output:

  Team  1  2  3
0   A   5  1  1
1   B   0  2  0

answered Feb 1, 2022 at 13:57

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Comments

timgeb · Accepted Answer · 2022-02-01 13:58:41Z

1

You can get the value counts for each group and then unstack the index. The rest is twiddling to get your exact output.

(df.groupby('Team')['place']
   .value_counts()
   .unstack(fill_value=0)
   .reset_index()
   .rename_axis(None, axis=1)
)

answered Feb 1, 2022 at 13:50

timgeb

79.2k20 gold badges129 silver badges150 bronze badges

I realized while posting that this is in fact a simple crosstab :p