Open In App
Related Articles

Pandas GroupBy – Count the occurrences of each combination

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

In this article, we will GroupBy two columns and count the occurrences of each combination in Pandas

DataFrame.groupby() method is used to separate the Pandas DataFrame into groups. It will generate the number of similar data counts present in a particular column of the data frame.

Count Occurrences of Combination in Pandas

Creating Dataframe.

Python3

# Import library
import pandas as pd
import numpy as np
 
# initialise data of lists.
Data = {'Products':['Box','Color','Pencil',
                    'Eraser','Color',
                    'Pencil','Eraser','Color',
                    'Color','Eraser','Eraser','Pencil'],
 
       'States':['Jammu','Kolkata','Bihar',
                 'Gujarat','Kolkata',
                 'Bihar','Jammu','Bihar','Gujarat',
                 'Jammu','Kolkata','Bihar'],
 
       'Sale':[14,24,31,12,13,7,9,31,18,16,18,14]}
 
# Create DataFrame
df = pd.DataFrame(Data, columns=['Products',
                                 'States','Sale'])
 
# Display the Output
display(df)

                    

Output:

 

Count the occurrences of elements using df.size()

It returns a total number of elements, it is compared by multiplying rows and columns returned by the shape method. 

Python3

new = df.groupby(['States','Products']).size()
display(new)

                    

 Output: 

 

Count the occurrences of elements using df.count()

It is used to count() the no. of non-NA/null observations across the given axis. It works with non-floating type data as well. 

Python3

new = df.groupby(['States','Products'])['Sale'].count()
display(new)

                    

Output:

 

Count the occurrences of elements using reset_index() 

It is a method to reset the index of a Pandas DataFrame.reset_index() method sets a list of integers ranging from 0 to the length of data as an index.  

Python3

new = df.groupby(['States',
        'Products'])['Sale'].agg('count').reset_index()
display(new)

                    

Output:

 

Count the occurrences of elements using the pivot() 

It produces a pivot table based on 3 columns of the DataFrame. Uses unique values from index/columns and fills them with values.

Python3

new = df.groupby(['States','Products']
                 ,as_index = False
                ).count().pivot('States'
                    ,'Products').fillna(0)
display(new)

                    

Output:

 



Last Updated : 20 Mar, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads