Skip to main content

GroupBy, Pivot Tables & Time Series with Pandas

Introduction​

With clean, well-typed data from the previous chapter, this chapter covers Pandas' analytical core: GroupBy (the equivalent of SQL's GROUP BY), pivot tables and cross-tabulation (multi-dimensional summaries), merging (SQL-style joins across tables), and time series operations (resampling, rolling windows, lag features). Together these answer the executive-level business questions RetailEdge's analytics team was actually asked: which store is most profitable, which category drives the most revenue, how does performance vary by region and quarter.

Concepts​

GroupBy​

  • df.groupby('col')['other_col'].sum() splits the data into groups by col, then aggregates other_col within each group.
  • Named aggregations — df.groupby('col').agg(Total=('Revenue', 'sum'), Count=('OrderID', 'count')) — are the most readable way to build a multi-metric summary table in one call.
  • .groupby([...]) with a list groups by more than one column at once (e.g. Store and Category).
  • .transform() differs from .agg(): it returns a result the same length as the original DataFrame (one value per row, repeated for every row in that row's group) rather than one row per group — this is how you add a "% of my store's total" column without a separate merge.

Pivot tables and cross-tabulation​

  • pd.pivot_table(df, values=, index=, columns=, aggfunc=) reshapes long data into a wide summary grid — rows and columns become two independent groupings, and margins=True adds row/column totals.
  • pd.crosstab(a, b) is a frequency count version of the same idea — how many rows fall into each combination of two categorical columns.

Merging​

  • df.merge(other, on='key', how=...) is Pandas' SQL JOIN. how='inner' keeps only matching rows; how='left' keeps every row from the left table, filling unmatched columns with NaN.
  • Merges chain naturally: df.merge(a, on='x').merge(b, on='y').

Time series​

  • Setting a datetime column as the index (df.set_index('Date')) unlocks .resample() — aggregating to a different frequency ('W' weekly, 'M' monthly).
  • .rolling(window=N).mean() computes a moving average over the last N rows — useful for smoothing out day-of-week noise.
  • .shift(N) and .pct_change() produce lag features and period-over-period growth rates.

Examples​

Basic and named GroupBy:

store_summary = df.groupby('Store')[['Revenue', 'Profit']].sum()

store_report = df.groupby('Store').agg(
    Total_Revenue=('Revenue', 'sum'),
    Total_Profit=('Profit', 'sum'),
    Order_Count=('OrderID', 'count'),
    Avg_Discount=('Discount', 'mean'),
).round(2).reset_index()
store_report['Profit_Margin'] = (store_report['Total_Profit'] / store_report['Total_Revenue'] * 100).round(1)

Multi-level GroupBy:

store_cat = df.groupby(['Store', 'Category'])['Revenue'].sum().reset_index()

quarterly = df.groupby(['Region', 'Quarter']).agg(
    Revenue=('Revenue', 'sum'),
    Orders=('OrderID', 'count'),
).reset_index()

Transform for group-level enrichment:

df['Store_Annual_Revenue'] = df.groupby('Store')['Revenue'].transform('sum')
df['Revenue_Share_Pct'] = (df['Revenue'] / df['Store_Annual_Revenue'] * 100).round(4)

df['Store_Avg_Revenue'] = df.groupby('Store')['Revenue'].transform('mean')
df['Above_Store_Avg'] = df['Revenue'] > df['Store_Avg_Revenue']

Pivot tables and cross-tabulation:

pivot = pd.pivot_table(
    df, values='Revenue', index='Store', columns='Quarter',
    aggfunc='sum', fill_value=0, margins=True, margins_name='Total',
)

ct = pd.crosstab(df['Store'], df['Category'], margins=True)
ct_pct = pd.crosstab(df['Store'], df['Category'], normalize='index').round(3)

Merging:

store_info = pd.DataFrame({
    'Store': ['Mumbai', 'Delhi', 'Pune', 'Jaipur'],
    'Manager': ['Raj Patel', 'Priya Sharma', 'Amit Singh', 'Neha Gupta'],
    'Sq_Ft': [8000, 6500, 5500, 4000],
})

df_enriched = df.merge(store_info, on='Store', how='left')

Time series:

ts = df.set_index('Date').sort_index()

weekly_rev = ts['Revenue'].resample('W').sum()
monthly = ts.resample('M').agg(Revenue=('Revenue', 'sum'), Orders=('Revenue', 'count'))
monthly['MoM_Growth_Pct'] = monthly['Revenue'].pct_change() * 100

ts['Rolling_7d_Rev'] = ts['Revenue'].rolling(window=7).mean()
ts['Revenue_7d_lag'] = ts['Revenue'].shift(7)

Challenges​

1. GroupBy analytics​

Answer these business questions using GroupBy:

  • Which Region has the highest average profit margin (Profit / Revenue)?
  • Build a monthly revenue trend: total revenue per month for the entire year.
  • Which SalesRep has the highest total revenue? The highest average order value?
  • Create a Category x Quarter pivot showing total revenue.
  • For each store, find the month with its highest revenue.
💡 Show Answer
region_margin = df.groupby('Region').apply(lambda g: g['Profit'].sum() / g['Revenue'].sum() * 100)
print('Best margin region:', region_margin.idxmax())

monthly_trend = df.groupby(df['Date'].dt.month)['Revenue'].sum()

rep_revenue = df.groupby('SalesRep')['Revenue'].sum()
rep_avg_order = df.groupby('SalesRep')['Revenue'].mean()
print('Top rep by total revenue:', rep_revenue.idxmax())
print('Top rep by avg order value:', rep_avg_order.idxmax())

cat_quarter = df.groupby(['Category', 'Quarter'])['Revenue'].sum().unstack()

best_month_per_store = (
    df.groupby(['Store', 'Month'])['Revenue'].sum()
    .reset_index()
    .sort_values('Revenue', ascending=False)
    .groupby('Store')
    .first()
)

2. Advanced analytics pipeline​

Build a full executive report for RetailEdge management:

  • A Store Performance Table: Store, Total Revenue, Total Profit, Order Count, Avg Order Value, Profit Margin %, Revenue Rank.
  • A Category Performance Table: Category, Revenue, Profit, Margin %, % of Total Revenue.
  • A Monthly Trend table showing MoM revenue growth % for each month.
  • Merge store_info to find revenue per square foot (Revenue / Sq_Ft) by store.
  • Identify the best-performing SalesRep in each Region.
💡 Show Answer
store_perf = df.groupby('Store').agg(
    Total_Revenue=('Revenue', 'sum'),
    Total_Profit=('Profit', 'sum'),
    Order_Count=('OrderID', 'count'),
).reset_index()
store_perf['Avg_Order_Value'] = store_perf['Total_Revenue'] / store_perf['Order_Count']
store_perf['Profit_Margin_Pct'] = (store_perf['Total_Profit'] / store_perf['Total_Revenue'] * 100).round(1)
store_perf['Revenue_Rank'] = store_perf['Total_Revenue'].rank(ascending=False, method='dense').astype(int)

total_revenue = df['Revenue'].sum()
category_perf = df.groupby('Category').agg(
    Revenue=('Revenue', 'sum'), Profit=('Profit', 'sum'),
).reset_index()
category_perf['Margin_Pct'] = (category_perf['Profit'] / category_perf['Revenue'] * 100).round(1)
category_perf['Pct_Of_Total'] = (category_perf['Revenue'] / total_revenue * 100).round(2)

monthly_trend = df.groupby('Month')['Revenue'].sum().reset_index()
monthly_trend['MoM_Growth_Pct'] = monthly_trend['Revenue'].pct_change() * 100

store_perf = store_perf.merge(store_info[['Store', 'Sq_Ft']], on='Store', how='left')
store_perf['Revenue_Per_SqFt'] = store_perf['Total_Revenue'] / store_perf['Sq_Ft']

top_rep_per_region = (
    df.groupby(['Region', 'SalesRep'])['Revenue'].sum()
    .reset_index()
    .sort_values('Revenue', ascending=False)
    .groupby('Region')
    .first()
)