Pandas groupby/pivot table for counting values in 2 columns

Question

I have a pandas DataFrame like so

week  player_a  player_b
 1      True      True
 1      True     False
 1     False     False
 2     False      True
 2     False     False
 2      True     False

and I would like to count the number of True and False for each player by week, but I cannot seem to finagle this into a convenient pandas groupby or pivot table operation. The desired result would look like:

week            True  False
      player
 1   player_a     2     1
     player_b     1     2
 2   player_a     1     2
     player_b     1     2

jezrael · Accepted Answer · 2019-10-29 12:06:15Z

Use DataFrame.melt first and then count crosstab:

df1 = df.melt('week', var_name='player')

df = pd.crosstab([df1['week'], df1['player']], df1['value'])

Or use DataFrame.pivot_table:

df = df1.pivot_table(index=['week', 'player'], columns='value', fill_value=0, aggfunc='size')

Or aggregate counts by GroupBy.size and reshape by Series.unstack:

df = df1.groupby(['week', 'player', 'value']).size().unstack(fill_value=0)

print (df)
value          False  True 
week player                
1    player_a      1      2
     player_b      2      1
2    player_a      2      1
     player_b      2      1

And solution with DataFrame.stack, SeriesGroupBy.value_counts and unstack:

df = df.set_index('week').stack().groupby(level=[0,1]).value_counts().unstack(fill_value=0)
print (df)
               False  True 
week                       
1    player_a      1      2
     player_b      2      1
2    player_a      2      1
     player_b      2      1

ramez · Accepted Answer · 2019-10-29 12:11:53Z

6

Use pandas pivot_table function and aggregate by size.

df = df.pivot_table(index=['week','player'], columns='value', aggfunc='size', fill_value=0)

answered Oct 29, 2019 at 12:11

ramez

4314 silver badges22 bronze badges

Collectives™ on Stack Overflow

Pandas groupby/pivot table for counting values in 2 columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related