5 Best Ways to Group By and Sum in Python Pandas

💡 Problem Formulation: Often in data analysis, we are faced with large datasets where we need to perform aggregated computations. For instance, suppose we have a sales dataset and want to sum up sales per region. We’d need to group our data by the ‘region’ column and then sum the ‘sales’ within each group. In this article, we’ll explore five different methods to accomplish ‘group by’ and ‘sum’ operations using the Python Pandas library with illustrative examples.

Method 1: Using groupby and sum

Arguably the most common method for grouping and summing in Pandas is using the groupby method followed by sum. This is straightforward and efficient for most use cases, where you can specify one or more columns to group by and aggregate over specific columns.

Here’s an example:

import pandas as pd

# Create sample DataFrame
df = pd.DataFrame({
    'region': ['West', 'East', 'West', 'East', 'West'],
    'sales': [200, 300, 240, 150, 400]
})

# Group by the 'region' column and sum up the 'sales'
grouped_sales = df.groupby('region')['sales'].sum()
print(grouped_sales)

The output:

region
East    450
West    840
Name: sales, dtype: int64

In this code snippet, we created a DataFrame with a ‘region’ and ‘sales’ column, used the groupby function on ‘region’ to create grouped data, and then applied the sum function on the ‘sales’ column to get the total sales per region.

Method 2: Aggregate with Custom Functions

Pandas’ aggregate() method takes a more flexible approach allowing for multiple aggregation operations at once, including the use of custom functions. This can be particularly powerful when you need more complex summaries than just the sum.

Here’s an example:

import pandas as pd

# Define a custom aggregation function
def total_sales(series):
    return series.sum()

df = pd.DataFrame({
    'region': ['North', 'South', 'North', 'South'],
    'sales': [120, 150, 220, 200]
})

# Use the custom function 'total_sales' with aggregate()
grouped_custom = df.groupby('region')['sales'].aggregate(total_sales)
print(grouped_custom)

The output:

region
North    340
South    350
Name: sales, dtype: int64

Here, we implemented a custom function total_sales() that returns the sum of a series. After grouping our DataFrame by the ‘region’ column, we pass our custom function to the aggregate() method to sum sales.

Method 3: Using pivot_table

By using the pivot_table method, we can achieve much more than just group by and sum. It enables us to reorient our dataset, offering a multi-index approach to aggregate data which is handy for creating pivot tables.

Here’s an example:

import pandas as pd

df = pd.DataFrame({
    'region': ['East', 'West', 'East', 'West', 'East'],
    'product': ['A', 'A', 'B', 'B', 'B'],
    'sales': [100, 200, 300, 400, 500]
})

pivot = df.pivot_table(index='region', columns='product', values='sales', aggfunc='sum')
print(pivot)

The output:

product    A    B
region           
East      100  800
West      200  400

This example illustrates the creation of a pivot table which sums the ‘sales’ column after grouping by both ‘region’ and ‘product’. The pivot_table makes it convenient to look at the data from multiple dimensions.

Method 4: Using crosstab

The crosstab function in Pandas is another way to summarize data through cross-tabulation. It is typically used for creating contingency tables but can be used with aggregation functions like sum.

Here’s an example:

import pandas as pd

df = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South', 'North'],
    'product': ['A', 'B', 'A', 'B', 'B'],
    'sales': [120, 200, 150, 100, 300]
})

cross = pd.crosstab(index=df['region'], columns=df['product'], values=df['sales'], aggfunc='sum')
print(cross)

The output:

product    A      B
region              
North    120.0  500.0
South    150.0  100.0

In this case, crosstab generates a contingency table showing the sum of sales per product for each region. This method is very useful for multi-dimensional summarization.

Bonus One-Liner Method 5: Using agg

For quick single-line aggregations, the agg method can be very concise. This method is similar to aggregate but can be used directly after the groupby.

Here’s an example:

import pandas as pd

df = pd.DataFrame({
    'region': ['East', 'West', 'East', 'West'],
    'sales': [150, 100, 300, 400]
})

# One-liner using `agg` to sum sales per region
summation = df.groupby('region').agg({'sales': 'sum'})
print(summation)

The output:

        sales
region       
East      450
West      500

This one-liner groups the DataFrame by ‘region’ and then sums up the ‘sales’ per region using the agg method and a dictionary to specify the aggregation operation.

Summary/Discussion

• Method 1: groupby with sum. Straightforward and widely used. It’s fast but limited to simple aggregation.
• Method 2: aggregate with a custom function. Offers flexibility with custom aggregations. Slightly more complex syntax.
• Method 3: pivot_table. Great for multi-index grouping and more complex data reshaping. Can be less intuitive for simple tasks.
• Method 4: crosstab. Ideal for multi-dimensional summarization. Not as common for aggregation, so might be less familiar to some users.
• Bonus Method 5: agg one-liner. Quick and concise for simple aggregations. Might not be as clear when reading code.