Pandas GroupBy - Count the occurrences of each combination

In this article, we will GroupBy two columns and count the occurrences of each combination in Pandas. Grouping by elements means organizing data into subsets based on column values, like grouping all rows with the same "State" or "Product." After grouping, we can perform operations like counting, summarizing, or calculating values within each group.

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.

Example DataFrame

We will start by creating a simple DataFrame for demonstration purposes:

Output:

Method 1: Using df.size()

The size() method returns the total number of elements in a group. We can use groupby() followed by size() to count the occurrences of each combination of values in two columns. 

Output:

Explanation:

• groupby(['States', 'Products']): Groups the data based on the combinations of 'States' and 'Products'.
• .size(): Returns the number of elements (i.e., occurrences) for each combination of 'States' and 'Products'.

Method 2: Using df.count()

count() function counts the non-NA/null values across a specified column or axis. It can be used to count occurrences based on any column.

Output:

Explanation:['Sale'].count() counts the non-null values in the 'Sale' column for each group of 'States' and 'Products'.

Method 3: Using reset_index()

reset_index() method is used to reset the index of the DataFrame, converting the grouped data back into a normal DataFrame.

Output:

Explanation:

• agg('count'): Performs aggregation (count) on the 'Sale' column for each group.
• reset_index(): Converts the resulting GroupBy object back into a normal DataFrame.

Method 4: Using pivot()

The pivot() function in Pandas is used to reshape data, creating a pivot table based on three columns.

Output:

Explanation:

• as_index=False: Ensures the grouped columns are not used as index columns.
• .count(): Counts occurrences in the DataFrame.
• .pivot(): Reshapes the data to create a table where each 'States' becomes a row and 'Products' becomes columns.
• fillna(0): Fills missing values with 0, in case a product does not exist in a state.

Related Articles:

• Pandas
• df.size()
• DataFrame.groupby()
• reset_index()
• pivot
• count()