pandas groupby count as new column

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

Here is what the above code is Doing:
1. We’re using the groupby method to group the dataframe by the item and color columns.
2. We’re using the count method to count the number of items in each group.
3. We’re using the reset_index method to reset the index of the resulting dataframe.
4. We’re giving the resulting dataframe a name of “count”.

The resulting dataframe will have three columns: item, color, and count. The item and color columns will be the same as the columns we grouped by. The count column will be the number of items in each group.

Let’s say we want to know how many items of each color there are for each item. We can do this by using the groupby method to group by the item and color columns, and then using the count method to count the number of items in each group.

In [13]: df.groupby([“item”, “color”])[“id”].count()
Out[13]:
item color
car black 2
truck blue 1
red 2
Name: id, dtype: int64

The resulting dataframe will have three columns: item, color, and count. The item and color columns will be the same as the columns we grouped by. The count column will be the number of items in each group.