Pandas: Split a given dataframe into groups and create a new column with count from GroupBy

Last update on April 04 2025 13:44:52 (UTC/GMT +8 hours)

17. Creating New Column with Group Count

Write a Pandas program to split a given dataframe into groups and create a new column with count from GroupBy.

Test Data:

  book_name book_type  book_id
0     Book1      Math        1
1     Book2   Physics        2
2     Book3  Computer        3
3     Book4   Science        4
4     Book1      Math        1
5     Book2   Physics        2
6     Book3  Computer        3
7     Book5   English        5

Sample Solution:

Python Code :

import pandas as pd
pd.set_option('display.max_rows', None)
df = pd.DataFrame({
'book_name':['Book1','Book2','Book3','Book4','Book1','Book2','Book3','Book5'],
'book_type':['Math','Physics','Computer','Science','Math','Physics','Computer','English'],
'book_id':[1,2,3,4,1,2,3,5]})
print("Original Orders DataFrame:")
print(df)
print("\nNew column with count from groupby:")
result = df.groupby(["book_name", "book_type"])["book_type"].count().reset_index(name="count")
print(result)

Sample Output:

Original Orders DataFrame:
  book_name book_type  book_id
0     Book1      Math        1
1     Book2   Physics        2
2     Book3  Computer        3
3     Book4   Science        4
4     Book1      Math        1
5     Book2   Physics        2
6     Book3  Computer        3
7     Book5   English        5

New column with count from groupby:
  book_name book_type  count
0     Book1      Math      2
1     Book2   Physics      2
2     Book3  Computer      2
3     Book4   Science      1
4     Book5   English      1

For more Practice: Solve these Related Problems:

• Write a Pandas program to group a dataframe and then add a new column that represents the count of rows in each group.
• Write a Pandas program to split a dataframe into groups and then assign each row a group count based on its group.
• Write a Pandas program to group the data and then merge the group sizes as a new column into the original dataframe.
• Write a Pandas program to calculate the count of each group and then join this count back to the dataframe as an additional column.

Python Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to split a given dataframe into groups and list all the keys from the GroupBy object.
Next: Write a Pandas program to split a given dataframe into groups with bin counts.