w3resource

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

Pandas Grouping and Aggregating: Split-Apply-Combine Exercise-17 with Solution

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

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.

What is the difficulty level of this exercise?

Test your Python skills with w3resource's quiz



Python: Tips of the Day

Python: Time library

Time library provides lots of time related functions and methods and is good to know whether you're developing a website or apps and games or working with data science or trading financial markets. Time is essential in most development pursuits and Python's standard time library comes very handy for that.

Let's check out a few simple examples:

moment=time.strftime("%Y-%b-%d__%H_%M_%S",time.localtime())

import time
time_now=time.strftime("%H:%M:%S",time.localtime())
print(time_now)
date_now=time.strftime("%Y-%b-%d",time.localtime())
print(date_now)

Output:

11:36:34
2020-Nov-30