Pandas GroupBy - Count the occurrences of each combination

Last Updated : 20 Mar, 2023

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)