7.4 Grouping and Aggregation

Advanced DataFrame Operations

Grouping and aggregation are powerful operations that allow us to summarize and analyze data in a DataFrame. Grouping involves splitting the data into groups based on the values in one or more columns, while aggregation involves applying a function to each group to produce a summary statistic.

Let’s say we have a DataFrame with sales data for a company. We can group the data by the region column and calculate the total sales for each region.

Whenever we use the groupby method, it returns a DataFrameGroupBy object. This object is not a DataFrame itself, but it can be used to perform aggregation operations on the grouped data. We can use various aggregation functions such as sum(), mean(), count(), etc., to summarize the data.

import pandas as pd

# Sample sales data
data = {
    "region": ["North", "South", "East", "West", "North", "South"],
    "sales": [100, 200, 150, 300, 250, 100]
}

df = pd.DataFrame(data)

# Group by region and calculate total sales
grouped = df.groupby("region").sum()

grouped

	sales
region
East	150
North	350
South	300
West	300

We do not have to use built-in aggregation functions. We can also define our own custom aggregation functions using the agg() method. For example, we can calculate the average and maximum sales for each region to create an entirely new DataFrame.

# Group by region and calculate average and maximum sales
custom_agg = df.groupby("region").agg(
    {
        "sales": "mean",
    }
)
custom_agg

	sales
region
East	150.0
North	175.0
South	150.0
West	300.0

The agg() method allows us to specify multiple aggregation functions for different columns. In this case, we calculated the mean, maximum, and total sales for each region.

But this can be very limiting. Because if you notice, the dictionary takes in the column name as the key and the aggregation function as the value. This means that we can only apply one aggregation function per column. What if we want to apply multiple aggregation functions to the same column?

We can solve this problem by passing a list of aggregation functions to the agg() method. This way, we can apply multiple functions to the same column and get a more comprehensive summary of the data.

# Group by region and calculate multiple aggregations for sales
summary = df.groupby("region")["sales"].agg(["min","max","mean","sum"])
summary

	min	max	mean	sum
region
East	150	150	150.0	150
North	100	250	175.0	350
South	100	200	150.0	300
West	300	300	300.0	300

Another way to do the same thing is to use tuples to specify the new column names along with the aggregation functions. This way, we can have more control over the names of the resulting columns. This solution provides more flexibility and allows us to create a more customized summary of the data.

summary = df.groupby("region").agg(
    sales_min=("sales", "min"),
    sales_max=("sales", "max"),
    sales_mean=("sales", "mean"),
    sales_sum=("sales", "sum"),
    sales_median=("sales", "median"),
    sales_count=("sales", "count"),
    sales_std=("sales", "std"),
)
summary

	sales_min	sales_max	sales_mean	sales_sum	sales_median	sales_count	sales_std
region
East	150	150	150.0	150	150.0	1	NaN
North	100	250	175.0	350	175.0	2	106.066017
South	100	200	150.0	300	150.0	2	70.710678
West	300	300	300.0	300	300.0	1	NaN