Pandas create new column with count from groupby

Published on Aug. 22, 2023, 12:12 p.m.

Pandas create new column that looks like the following:

id        item        color
01        truck       red
02        truck       red
03        car         black
04        truck       blue
05        car         black

I am trying to create a df.

item      color       count
truck     red          2
truck     blue         1
car       black        2

That’s not a new column, that’s a new data frame:

In [11]: df.groupby(["item", "color"]).count()
Out[11]:
             id
item  color
car   black   2
truck blue    1
      red     2

Use reset_index:

In [12]: df.groupby(["item", "color"])["id"].count().reset_index(name="count")
Out[12]:
    item  color  count
0    car  black      2
1  truck   blue      1
2  truck    red      2

To get a “new column” , you can use transform :

In [13]: df.groupby(["item", "color"])["id"].transform("count")
Out[13]:
0    2
1    2
2    2
3    1
4    2
dtype: int64