Complex Groupby Operations in Pandas

In this lesson, we'll keep exploring the power of the groupby function in the Pandas library. Groupby is a crucial tool for data analysis, allowing us to split data into different groups and then apply aggregates to those groups. This can be very useful in numerous real-life applications, such as summarizing sales data by product and region or understanding passenger statistics in a Titanic dataset.

Our goal today is to understand how to use the groupby function in Pandas for more advanced, multi-level aggregations. We'll work through an example involving grouping by multiple columns and applying multiple aggregation functions to several fields.

Before diving into complex groupby operations, let's review the basics. The groupby function in Pandas is used to split the data into groups based on some criteria. You can then apply various aggregation functions to these groups.

Let's start with a basic example. Suppose we have a simple dataset about students and their scores.

import pandas as pd

# Simple dataset
data = {
    'student': ['Alice', 'Bob', 'Charlie', 'Alice', 'Bob', 'Charlie'],
    'subject': ['Math', 'Math', 'Math', 'English', 'English', 'English'],
    'score': [85, 90, 95, 88, 93, 97]
}

df = pd.DataFrame(data)

# Basic groupby operation
grouped = df.groupby('student')['score'].mean()
print("\nAverage score per student:")
print(grouped)
# Output:
# student
# Alice      86.5
# Bob        91.5
# Charlie    96.0
# Name: score, dtype: float64

In this example, we grouped the DataFrame by student and calculated the mean score for each student. This is a fundamental operation that helps in summarizing the data efficiently.

Now that we understand the basics, let's move on to more complex groupby operations. Sometimes, you might want to group data by multiple columns. For instance, in the Titanic dataset, you might want to analyze data based on both the class of the passenger and the town they embarked from.

Grouping by multiple columns allows for more detailed summaries and insights from the data. Consider the following example: We group the Titanic dataset by class and embark_town and then apply multiple aggregation functions to different columns.

import seaborn as sns

titanic = sns.load_dataset('titanic')

# Detailed grouping with multiple aggregations
grouped_details = titanic.groupby(['class', 'embark_town'], observed=True).agg({
    'fare': ['mean', 'max', 'min'],
    'age': ['mean', 'std', 'count']
})
print(grouped_details)

Note the observed=True parameter. By default, groupby includes all possible combinations of the grouping columns, even if some combinations do not appear in the data. For example, imagine there are no passengers of the first class embarking from the "Queenstown". Though this combination is possible, it won't show up in the dataset.

Setting observed=True ensures the result only includes the combinations observed in the data, which can make the output more concise and easier to interpret. Also, in the future versions of pandas, the observed will be equal to True by default.

Let's break down this example step-by-step:

1. Group by Multiple Columns: titanic.groupby(['class', 'embark_town'])

   • We first group the data by class and embark_town. This means that we will have a separate group for each combination of class and embarkation town.

2. Apply Different Aggregations: .agg({ ... })

   • Inside the agg function, we specify the columns and the aggregation functions we want to apply. For the fare column, we calculate the mean, maximum, and minimum values. For the age column, we calculate the mean, standard deviation, and count.

This approach provides a detailed summary of the data, allowing us to understand various aspects of each group.