Skip to content
Related Articles

Related Articles

Pandas GroupBy – Count the occurrences of each combination

Improve Article
Save Article
  • Last Updated : 03 Oct, 2022
Improve Article
Save 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 DataFrame into groups. It will generate the number of similar data counts present in a particular column of the data frame.

Create a Dictionary to Count Occurrences of Elements in Pandas

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:

 

Method 1: 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: 

 

Method 2: 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:

 

Method 3: Count the occurrences of elements using reset_index() 

It is a method to reset the index of a Data Frame.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:

 

Method 4: 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
Related Articles

Start Your Coding Journey Now!