Data Analysis and Aggregation

# CHAPTER 16

Data Analysis and Aggregation #

1. Chapter Introduction #

2. The Mechanics of groupby() #

The groupby() function implements a "Split-Apply-Combine" workflow:

1. 1. Split: Split the dataset into groups based on a category (e.g., City).

1. 2. Apply: Apply a mathematical function to each group (e.g., Sum the Revenue).

1. 3. Combine: Combine the results into a new summary DataFrame.

import pandas as pd

# Sales Data
data = {
    "City": ["NY", "LA", "NY", "SF", "LA", "NY"],
    "Salesman": ["Alice", "Bob", "Charlie", "Dave", "Eve", "Frank"],
    "Revenue": [500, 300, 400, 700, 250, 600]
}
df = pd.DataFrame(data)

# Goal: What is the total revenue for each city?
# 1. Group by the 'City' column
# 2. Select the 'Revenue' column to do math on
# 3. Apply the .sum() aggregation function
city_revenue = df.groupby('City')['Revenue'].sum()

print(city_revenue)
# Output:
# City
# LA     550
# NY    1500
# SF     700
# Name: Revenue, dtype: int64

3. Multiple Aggregations at Once #

What if you want to know the Total Revenue, the Average Revenue, AND the Number of sales for each city simultaneously? You use the .agg() method.

# Pass a list of aggregation functions
summary = df.groupby('City')['Revenue'].agg(['sum', 'mean', 'count'])

print(summary)
# Output:
#        sum        mean  count
# City                         
# LA     550  275.000000      2
# NY    1500  500.000000      3
# SF     700  700.000000      1

4. Grouping by Multiple Columns #

You can split the data into sub-groups by passing a list to groupby().

# Add a 'Year' column
df['Year'] = [2022, 2022, 2023, 2023, 2023, 2022]

# Group by City first, then by Year
yearly_city_rev = df.groupby(['City', 'Year'])['Revenue'].sum()
print(yearly_city_rev)

5. Pivot Tables in Pandas #

If you are coming from Excel, you probably love Pivot Tables. Pandas has a dedicated function for them which is often more intuitive than complex groupby chains.

# Values: The number to aggregate (Revenue)
# Index: The rows (City)
# Columns: The columns (Year)
# aggfunc: The math to apply (sum)

pivot = pd.pivot_table(df, 
                       values='Revenue', 
                       index='City', 
                       columns='Year', 
                       aggfunc='sum',
                       fill_value=0) # Fills missing combos with 0

print(pivot)
# Output:
# Year  2022  2023
# City            
# LA     300   250
# NY    1100   400
# SF       0   700

6. Mini Project: HR Analytics #

Find out which department has the highest average salary.

hr_data = pd.DataFrame({
    "Dept": ["IT", "Sales", "IT", "HR", "Sales", "HR"],
    "Role": ["Dev", "Rep", "Dev", "Manager", "Manager", "Recruiter"],
    "Salary": [90000, 50000, 95000, 80000, 120000, 60000]
})

# 1. Group by Dept and calculate average salary
avg_salaries = hr_data.groupby('Dept')['Salary'].mean()

# 2. Sort to find the highest
sorted_salaries = avg_salaries.sort_values(ascending=False)

print("Average Salaries by Department:")
print(sorted_salaries)

# 3. Identify the highest paying department
print(f"\nHighest paying dept: {sorted_salaries.index[0]}")

7. Common Mistakes #

• Forgetting the Aggregation Function: If you type df.groupby('City') and hit enter, it won't show you data. It will just return a <pandas.core.groupby.DataFrameGroupBy object>. You MUST tell it what math to do (e.g., .sum(), .mean(), .count()).

• Grouping on unique identifiers: Do not groupby('CustomerID') if every customer is unique. Grouping only works on categorical columns where items repeat (like City, Department, Gender, Month).

8. MCQs #

9. Interview Questions #

• Q: Explain the "Split-Apply-Combine" strategy behind the .groupby() function.

• Q: In what scenario would you prefer using pd.pivottable() over standard .groupby()?

11. Next Chapter Recommendation #