Skip to content
Related Articles
Open in App
Not now

Related Articles

Pandas GroupBy – Count the occurrences of each combination

Improve Article
Save Article
Like Article
  • Last Updated : 20 Mar, 2023
Improve Article
Save Article
Like Article

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:

 


My Personal Notes arrow_drop_up
Like Article
Save Article
Related Articles

Start Your Coding Journey Now!