Altcademy - a Forbes magazine logo Best Coding Bootcamp 2023

How to groupby multiple columns in Pandas

Understanding GroupBy in Pandas

When you're working with data, one of the most common tasks is to categorize or segment the data based on certain conditions or criteria. This is where the concept of "grouping" comes into play. In the world of data analysis with Python, the Pandas library offers a powerful tool for this purpose, known as groupby.

Imagine you're sorting laundry; you might group clothes by color, fabric type, or the temperature they need to be washed at. Similarly, groupby allows you to organize your data into groups that share a common trait.

The Basics of GroupBy

Before we dive into the more complex use of grouping by multiple columns, let's ensure we understand the basic operation of groupby. The groupby method in Pandas essentially splits the data into different groups depending on a key of our choice.

Here's a simple analogy: think of groupby as a way of creating buckets where each bucket has items that are alike in some manner. For example, if you have a list of people with their names and cities, grouping by 'city' would create buckets where each bucket contains people from the same city.

Grouping by a Single Column

Let's start with a simple example where we group by one column. Suppose we have the following DataFrame:

import pandas as pd

# Create a sample DataFrame
data = {
    'Name': ['Alice', 'Bob', 'Charlie', 'Alice', 'Bob'],
    'City': ['NY', 'LA', 'LA', 'NY', 'NY'],
    'Sales': [100, 200, 150, 130, 120]

df = pd.DataFrame(data)

# Group by the 'City' column
grouped = df.groupby('City')

# Display the first entry in each group
for name, group in grouped:
    print("\nCity:", name)

When you run this code, you'll see that the DataFrame is split into groups based on unique city names. Each group is a smaller DataFrame containing only the rows that match the city name.

Grouping by Multiple Columns

Now, let's extend this concept to multiple columns. When you group by more than one column, you create multi-dimensional buckets. Using our previous analogy, it's like sorting the laundry by color and then by fabric type within each color category.

Here's how you can group by multiple columns:

# Group by both 'Name' and 'City'
multi_grouped = df.groupby(['Name', 'City'])

# Display the first entry in each group
for (name, city), group in multi_grouped:
    print(f"\nName: {name}, City: {city}")

In this example, we grouped our DataFrame by both 'Name' and 'City'. The result is a grouping where each combination of name and city has its own bucket.

Aggregating Data After Grouping

After grouping, you often want to perform some sort of operation on each group—like summing up numbers, calculating averages, or finding maximum values. This is known as "aggregation".

Let's aggregate our sales data to find the total sales per city:

# Sum the 'Sales' for each city
city_sales_total = df.groupby('City')['Sales'].sum()

Here, we've grouped by 'City' and then summed the 'Sales' within each city. The result is a Series where the index is the city names, and the values are the total sales.

More Complex Aggregations

Sometimes, you might want to perform different aggregations for different columns. Pandas makes this easy with the agg method.

Imagine you want to know the total sales and the average sales per city. Here's how you can do it:

# Use 'agg' to perform multiple aggregations
city_stats = df.groupby('City').agg({'Sales': ['sum', 'mean']})

The agg method takes a dictionary where the keys are the columns you want to aggregate, and the values are lists of the aggregation functions you want to apply.

Understanding MultiIndex in GroupBy Results

When you group by multiple columns, the resulting DataFrame or Series will have a MultiIndex. A MultiIndex is like having multiple layers of indices or labels for each row. It might sound confusing, but think of it as a hierarchical structure for your index, similar to folders and subfolders on your computer.

Here's an example of accessing data in a DataFrame with a MultiIndex:

# Print the sales data for Alice in NY
print(multi_grouped.get_group(('Alice', 'NY')))

Pivoting After Grouping

Sometimes, after grouping, you may want to reshape your data for better readability or analysis. This is where "pivoting" comes in. Pivoting is like rearranging the data from a stacked format (like a pile of books) to a spread-out format (like books on a shelf).

Here's how you can pivot your grouped data:

# Reset the index and pivot the DataFrame
pivot_df = city_stats.reset_index()
pivot_df.columns = ['City', 'Total Sales', 'Average Sales']

Visualizing Grouped Data

Visualizing your grouped data can be incredibly insightful. For example, you could create a bar chart to show total sales per city. Here's a simple way to do it using the matplotlib library:

import matplotlib.pyplot as plt

# Plot total sales per city
plt.ylabel('Total Sales')
plt.title('Total Sales by City')

This will give you a bar chart where each city is on the x-axis, and the height of the bars represents the total sales.


Grouping data by multiple columns with Pandas is a powerful way to drill down into your data and find patterns that may not be immediately obvious. It's like organizing a messy room into neatly labeled boxes, making it easier to find exactly what you're looking for.

Whether you're summing up sales figures, averaging test scores, or segmenting customers by demographics, Pandas' groupby function is an essential tool in your data analysis toolkit. It allows you to slice and dice your data in meaningful ways, paving the path for insightful analysis and compelling visualizations.

Remember, the key to mastering data analysis is practice. So, grab a dataset that interests you and start exploring it with groupby. As you become more comfortable with these concepts, you'll find that what once seemed like a daunting pile of numbers can be transformed into a well-organized, insightful narrative that tells a story about the underlying data.