How to add multiple columns as a result of groupby pandas python

Question

Let's say I have a dataframe:

date | brand | color
--------------------
2017 | BMW   | red
2017 | GM    | blue
2017 | BMW   | blue
2017 | BMW   | red
2018 | BMW   | green
2018 | GM    | blue
2018 | GM    | blue
2018 | GM    | red

As a result I want to have something like:

date | brand | red | blue | green
---------------------------------
2017 | BMW   |  2  |  1   |   0
     |  GM   |  0  |  1   |   0
2018 | BMW   |  0  |  0   |   1
     |  GM   |  1  |  2   |   0

I found that I need to use groupby + size, something like:

df[df['color'] == 'red'].groupby([df['date'], df['brand']]).size()

But this gives me Series only for single color, while I want to have complete dataframe as shown higher.

Why are you filtering your dataframe to a single colour with df['color'] == 'red'? — roganjosh
– roganjosh, Commented Sep 30, 2017 at 21:28

BENY · Accepted Answer · 2017-09-30 22:33:34Z

As simple as you saw..

Option 1 crosstab

pd.crosstab([df['date'],df['brand']], df['color'])
Out[30]: 
 color          blue   green   red
date   brand                      
2017   BMW         1       0     2
       GM          1       0     0
2018   BMW         0       1     0
       GM          2       0     1

Option 2 : groupby and unstack

df.groupby(['date ',' brand ',' color'])[' color'].count().unstack(-1).fillna(0)
Out[40]: 
 color          blue   green   red
date   brand                      
2017   BMW       1.0     0.0   2.0
       GM        1.0     0.0   0.0
2018   BMW       0.0     1.0   0.0
       GM        2.0     0.0   1.0

Option 3 pivot_table

pd.pivot_table(df.reset_index(),index=['date','brand'],columns='color',values='index',aggfunc='count').fillna(0)
Out[57]: 
color          blue   green   red
date brand                       
2017  BMW       1.0     0.0   2.0
      GM        1.0     0.0   0.0
2018  BMW       0.0     1.0   0.0
      GM        2.0     0.0   1.0

Yale Newman · Accepted Answer · 2017-09-30 22:11:21Z

0

df.groupby(['date','brand'])['red','blue','green'].count()

or...

df.groupby(['date','brand']).agg('count')

edited Sep 30, 2017 at 22:11

answered Sep 30, 2017 at 21:44

Yale Newman

1,2414 gold badges14 silver badges25 bronze badges

2 Comments

roganjosh Over a year ago

Surely this can be done with agg? Don't hard-code the colours.

Yale Newman Over a year ago

you can do something like df.groupby(['date','brand']).agg({'green':'sum','red':count','blue':'max}) otherwise .agg('count') will apply the function to each column unless specified with df.groupby(['date','brand'])['green'].agg('count')

Collectives™ on Stack Overflow

How to add multiple columns as a result of groupby pandas python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related