Data Aggregation with Pandas

1. The Core Concept: Split-Apply-Combine

Before writing any code, it's crucial to understand the conceptual model behind pandas' grouping operations. This model is called Split-Apply-Combine, a three-step process that allows you to perform complex calculations across different subsets of your data.

Here's a breakdown of the three steps:

1. Split: The data is partitioned into groups based on some criteria (e.g., all rows with the same 'Country' value go into one group, all rows with another 'Country' value go into a second, and so on).
2. Apply: A function is applied independently to each group. This could be a simple aggregation like sum() or mean(), or a more complex transformation.
3. Combine: The results of the function applications are collected and combined into a new data structure (like a new DataFrame or Series).

To see this process in action, watch the following short video. It provides an excellent visual walkthrough.

2. Performing GroupBy and Simple Aggregations

Now that you understand the theory, let's apply it. We'll start with some basic aggregations before moving to groupby(). We'll be using a dataset from a developer survey. The primary video for this lesson provides all the code and data.

As you saw, you can get statistics like the median salary for all developers. But what if you want the median salary per country? This is where groupby() comes in. The syntax is straightforward: you call .groupby() on your DataFrame with the column you want to group by, and then you chain the aggregation function you want to apply.

df.groupby('Country')['ConvertedComp'].median()

Let's continue with the video to see exactly how this works.

3. Multiple and Custom Aggregations with .agg()

Simple aggregations like .mean() or .sum() are powerful, but often you need more flexibility. You might want to:

• Apply several aggregation functions at once (e.g., get the mean and median).
• Apply different functions to different columns.
• Rename the output columns for clarity.

This is where the .agg() method shines. It allows you to define your aggregations with precision using a list or a dictionary.

The .agg() method is incredibly versatile. Here is a visual summary of its most common uses:

The dictionary method is particularly useful. For example, if you wanted to find the total sales and the average number of units sold per product:

df.groupby('product').agg({
    'sales': 'sum',
    'units': 'mean'
})

4. An Advanced Application: Combining GroupBy, Apply, and Concat

To truly harness the power of pandas for complex analysis, you often need to combine several operations. The next segment of the video walks through a complete, practical example: calculating the percentage of developers in each country who know Python.

This example is more advanced and ties together several concepts:

• Grouping by country.
• Using .apply() with a custom lambda function to perform a string search on each group (a task that can't be done with a simple aggregation like .sum()).
• Creating two separate summary Series (total respondents per country and Python users per country).
• Combining these Series into a new DataFrame using pd.concat().
• Performing arithmetic on the columns of the new DataFrame to get the final percentage.

Following this entire workflow will give you a real sense of how data analysis is performed in practice.

Your Turn: Practice Exercise

Let's use the video game sales dataset from our last lesson to practice these new skills.

Task (5-10 minutes):

1. Run the code below to load the dataset.
2. Find the average Global_Sales for each Publisher. Display the top 5 publishers with the highest average sales. (Hint: you'll need to chain .nlargest(5) after your aggregation).
3. Group the data by Platform and Genre. Find the total NA_Sales (North American sales) for each combination.
4. Use the .agg() method to find the following statistics for each Genre:
   • The total Global_Sales (sum).
   • The average EU_Sales (mean).
   • The number of games released (Name column, count).

# 1. Load the dataset
import pandas as pd
url = 'https://gist.githubusercontent.com/gwalker/8568092/raw/bb6ded017a3507973c1143c683fa6d4825595568/vgsales.csv'
df_vgsales = pd.read_csv(url)

import pandas as pd




# 1. Load the dataset
url = 'https://gist.githubusercontent.com/gwalker/8568092/raw/bb6ded017a3507973c1143c683fa6d4825595568/vgsales.csv'
df_vgsales = pd.read_csv(url)




# 2. Find the average Global_Sales per Publisher and display the top 5
avg_sales_by_publisher = df_vgsales.groupby('Publisher')['Global_Sales'].mean()
top_5_publishers = avg_sales_by_publisher.nlargest(5)
print("--- Top 5 Publishers by Average Global Sales ---")
print(top_5_publishers)




# 3. Group by Platform and Genre, find total NA_Sales
na_sales_by_platform_genre = df_vgsales.groupby(['Platform', 'Genre'])['NA_Sales'].sum()
print("\n--- Total NA Sales by Platform and Genre (first 10) ---")
print(na_sales_by_platform_genre.head(10))




# 4. Use .agg() to get multiple stats for each Genre
genre_stats = df_vgsales.groupby('Genre').agg(
    Total_Global_Sales=('Global_Sales', 'sum'),
    Average_EU_Sales=('EU_Sales', 'mean'),
    Number_of_Games=('Name', 'count')
)
print("\n--- Comprehensive Statistics by Genre ---")
print(genre_stats)

Conclusion

Excellent work. You've now unlocked one of the most essential and powerful capabilities in the pandas library. Being able to group data and compute summary statistics is the foundation of data analysis and feature engineering.

Key Takeaways:

• Split-Apply-Combine is the fundamental paradigm for groupby operations.
• You can apply simple aggregations like .mean(), .sum(), and .count() to grouped data to quickly generate insights.
• The .agg() method provides maximum flexibility, allowing you to apply multiple functions, target different columns, and name your results clearly.
• Complex analytical questions can be answered by chaining groupby with other pandas methods like .apply() and .concat().

Preview of the Next Lesson:
Now that you can extract and summarize meaningful statistics from your data, what's the next logical step? Visualizing them! A table showing average sales per country is useful, but a bar chart of that same data is instantly understandable. In our next lesson, "Create basic plots (line, scatter, histogram) using Matplotlib," you will learn how to turn your aggregated data into insightful charts and graphs. This will bring your data analysis to life and allow you to communicate your findings effectively.