Pandas: Split the specified dataframe into groups based on all columns and calculate Groupby value counts on the dataframe

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

14. Grouping by All Columns – Value Counts

Write a Pandas program to split the following dataframe into groups based on all columns and calculate GroupBy value counts on the dataframe.

Test Data:

   id  type     book
0   1    10     Math
1   2    15  English
2   1    11  Physics
3   1    20     Math
4   2    21  English
5   1    12  Physics
6   2    14  English

Sample Solution:

Python Code :

import pandas as pd
df = pd.DataFrame( {'id' : [1, 2, 1, 1, 2, 1, 2], 
                    'type' : [10, 15, 11, 20, 21, 12, 14], 
                    'book' : ['Math','English','Physics','Math','English','Physics','English']})

print("Original DataFrame:")
print(df)
result = df.groupby(['id', 'type', 'book']).size().unstack(fill_value=0)
print("\nResult:")
print(result)

Sample Output:

Original DataFrame:
   id  type     book
0   1    10     Math
1   2    15  English
2   1    11  Physics
3   1    20     Math
4   2    21  English
5   1    12  Physics
6   2    14  English

Result:
book     English  Math  Physics
id type                        
1  10          0     1        0
   11          0     0        1
   12          0     0        1
   20          0     1        0
2  14          1     0        0
   15          1     0        0
   21          1     0        0

For more Practice: Solve these Related Problems:

• Write a Pandas program to group the dataframe by all columns and then perform a value count on a specified column within each group.
• Write a Pandas program to split the dataframe into groups based on all columns and then compute the frequency distribution for each group.
• Write a Pandas program to group by all columns and then display the count of unique values in the 'book' column per group.
• Write a Pandas program to group the dataframe by every column and then aggregate using value_counts for a target 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 the following dataframe into groups based on first column and set other column values into a list of values.
Next: Write a Pandas program to split the following dataframe into groups and count unique values of ‘value’ column.