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 libraryimport pandas as pdimport 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 DataFramedf = pd.DataFrame(Data, columns=['Products', 'States','Sale'])# Display the Outputdisplay(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:
