Lesson 10 · Data analytics zero to hero
Pandas GroupBy & Aggregation Made Simple | Data Analytics #10
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…
- 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.csv preview132.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.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
What does
df.groupby('Country')['TotalPrice'].sum()return?groupby splits rows into groups by Country, then sum() adds TotalPrice within each group.
In named aggregation
agg(num_orders=('InvoiceNo', 'nunique')), what does 'nunique' do?nunique counts distinct values, so duplicate invoice lines count once.
What is special about
transform('mean')compared withagg('mean')?transform broadcasts the group statistic back onto every original row, which is handy for comparing each row with its group average.
What does
.unstack()do on a result grouped by ['Month', 'Country']?unstack pivots the last index level into columns, creating a table of Month by Country.
What is the difference between
pivot_tableandcrosstab?pivot_table summarizes a values column with an aggfunc, while crosstab counts how often each row/column combination occurs.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Group a small sales DataFrame by Country and print the total TotalPrice per country, sorted highest first.
💡 Hint
groupby('Country')['TotalPrice'].sum().sort_values(ascending=False).
✅ Show solution
import pandas as pd df = pd.DataFrame({ 'Country': ['UK', 'UK', 'France', 'France', 'Norway'], 'TotalPrice': [20.0, 35.5, 12.0, 8.0, 50.0] }) print(df.groupby('Country')['TotalPrice'].sum().sort_values(ascending=False))
Use named aggregation to get, per country, total_revenue (sum of TotalPrice), avg_order_value (mean) and num_orders (nunique of InvoiceNo).
💡 Hint
agg(name=(column, function)).
✅ Show solution
import pandas as pd df = pd.DataFrame({ 'Country': ['UK', 'UK', 'UK', 'France', 'France'], 'InvoiceNo': [1, 1, 2, 3, 4], 'TotalPrice': [10.0, 5.0, 20.0, 8.0, 12.0] }) summary = df.groupby('Country').agg( total_revenue=('TotalPrice', 'sum'), avg_order_value=('TotalPrice', 'mean'), num_orders=('InvoiceNo', 'nunique') ) print(summary)
Add a CountryAvg column with transform('mean'), flag rows above their country's average, and build a pivot_table of total revenue by Month (rows) and Country (columns) with fill_value=0.
💡 Hint
Use groupby(...).transform('mean') and pivot_table(values, index, columns, aggfunc, fill_value).
✅ Show solution
import pandas as pd df = pd.DataFrame({ 'Country': ['UK', 'UK', 'France', 'France', 'UK'], 'Month': ['2011-01', '2011-02', '2011-01', '2011-02', '2011-02'], 'TotalPrice': [10.0, 30.0, 8.0, 12.0, 20.0] }) df['CountryAvg'] = df.groupby('Country')['TotalPrice'].transform('mean') df['AboveAvg'] = df['TotalPrice'] > df['CountryAvg'] print(df) pivot = df.pivot_table(values='TotalPrice', index='Month', columns='Country', aggfunc='sum', fill_value=0) print(pivot)
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



