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