Mathew K AnalyticsLesson 10 · Data analytics zero to hero
Pandas GroupBy & Aggregation Made Simple | Data Analytics #10
Video ten of the 30-part series, a deep dive into groupby: the single most important tool for turning raw rows into a real summary. We're continuing with…
- CourseData analytics zero to hero
- Lesson10 of 30
- Video12 min
- FormatJupyter notebook · 11 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- online_retail_sample.csv132.5 KB
📓 Full notebook
Download .ipynbData Analytics Zero to Hero, Video 10: GroupBy and Aggregation#
- Video ten of the 30-part series, a deep dive into groupby: the single most important tool for turning raw rows into a real summary.
- We're continuing with the real, cleaned Online Retail extract.
- Let's jump straight in.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- Place online_retail_sample.csv in the same folder as this notebook.
import pandas as pd
df = pd.read_csv('online_retail_sample.csv')
df = df.dropna(subset=['CustomerID']).drop_duplicates()
df = df[(df['Quantity'] > 0) & (df['UnitPrice'] > 0)]
df['TotalPrice'] = df['Quantity'] * df['UnitPrice']
print(df.shape)
Part 1: Basic groupby#
by_country = df.groupby('Country')
print(type(by_country))
print(by_country['TotalPrice'].sum())
revenue_by_country = df.groupby('Country')['TotalPrice'].sum().sort_values(ascending=False)
print(revenue_by_country.head(5))
Part 2: Multiple Aggregations with agg#
summary = df.groupby('Country')['TotalPrice'].agg(['sum', 'mean', 'count'])
print(summary.head(5))
named = df.groupby('Country').agg(
total_revenue=('TotalPrice', 'sum'),
avg_order_value=('TotalPrice', 'mean'),
num_orders=('InvoiceNo', 'nunique')
)
print(named.sort_values('total_revenue', ascending=False).head(5))
Part 3: Grouping by Multiple Columns#
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
df['Month'] = df['InvoiceDate'].dt.to_period('M')
monthly_country = df.groupby(['Month', 'Country'])['TotalPrice'].sum()
print(monthly_country.head(6))
print(monthly_country.unstack().fillna(0).round(2))
Part 4: transform#
df['CountryAvgOrder'] = df.groupby('Country')['TotalPrice'].transform('mean')
print(df[['Country', 'TotalPrice', 'CountryAvgOrder']].head(5))
df['AboveCountryAvg'] = df['TotalPrice'] > df['CountryAvgOrder']
print(df['AboveCountryAvg'].value_counts())
Part 5: pivot_table and crosstab#
pivot = df.pivot_table(values='TotalPrice', index='Month', columns='Country', aggfunc='sum', fill_value=0)
print(pivot.round(2))
top_countries = revenue_by_country.head(4).index
subset = df[df['Country'].isin(top_countries)]
ct = pd.crosstab(subset['Country'], subset['Month'])
print(ct)
Wrap-Up: What You Learned#
- groupby splits data into groups; nothing computes until you aggregate.
- Multiple aggregations at once with agg, including named aggregation for clean output columns.
- Grouping by multiple columns, and reshaping the multi-level result with unstack.
- transform, for broadcasting a group statistic back to every original row.
- pivot_table and crosstab, for spreadsheet-style summaries and combination counts.
- All of it run against the same real, cleaned retail data. Video eleven covers merging, joining, and reshaping combining multiple real tables together, which is exactly what most real analyses actually require. Subscribe so it lands automatically see you there.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
12 minLesson 13
Mastering Resampling and Aggregation of Time Series Data with Pandas in Python
28 minLesson 1
Python Data Analytics #01: Acquiring & Auditing a Real Retail Dataset (Pandas Tutorial)
31 minLesson 16
Python Data Analytics #16: Backtesting a Simple Trading Strategy in Python
12 minLesson 17