pandas groupby aggregate column name based on value

Asked Nov 20 '20 at 06:10

Active Nov 20 '20 at 06:14

Viewed 52 times

I'm trying to do a Pandas Groupby that counts the distinct value of the group by column and then uses that name to create a NamedAgg column with that name. For example:

animals = pd.DataFrame({'kind': ['cat', 'dog', 'cat', 'dog'],'color': ['red', 'black', 'red', 'yellow']})
animals

kind.     color
cat.      red
dog.      black
cat.      red
dog.      yellow

I tried using this code:

animals.groupby('kind').agg(
red=(animals['color']=='red', 'count'),
black=(animals['color']=='black', 'count'),
yellow=(animals['color']=='yellow', 'count'))

I wanted to get this result:

kind.     red.      black.      yellow
cat.       2
dog.                  1.         1

I researched and tried everything

asked Nov 20 '20 at 06:10

sabic2003

2

Use `animals.pivot_table(index='kind', columns='color', aggfunc='size')` – jezrael Nov 20 '20 at 06:11
1

Or `animals.groupby(['kind', 'color']).size().unstack()` – jezrael Nov 20 '20 at 06:13
1

Thank you. I can't tell you how frustrated I am because I send hours trying to find the solution myself and it literally took you 30 seconds to answer this. :) – sabic2003 Nov 20 '20 at 06:21

pandas groupby aggregate column name based on value

0 Answers0