CODEWITHSUNDEEP
pythonpandasdataframecount
ninelondon
ninelondon

Reputation: 149

Python DataFrame: count of occurances based on another column

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

Team place
A 1
A 1
A 1
A 2
A 3
A 1
A 1
B 2
B 2

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

Upvotes: 1

Views: 66

Answers (2)

timgeb
timgeb

Reputation: 78780

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)
)

Upvotes: 1

mozway
mozway

Reputation: 262284

You could use pandas.crosstab:

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
all as columns
(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

Upvotes: 1

Related Questions