Welcome to Week 4

Today’s plan:

  • Week 3 questions
  • Meet the Complete Journey data
  • Manipulating data
  • Summarizing data
  • Joining data

Questions from Week 3?

Share with the Class

Open floor for questions about:

  • DataFrames vs. Series
  • Selecting columns, filtering rows, and .loc[]
  • The homework, quiz, or last Thursday’s lab

Stuck on a homework problem?

Let me know which one — we can work through it together in Thursday’s lab.

📓 Follow Along in the Notebook

Week 4 Lecture Notebook

Open the companion notebook in Colab now and follow along as we work through today’s examples on manipulating, summarizing, and joining data.

Open In Colab

Meet the Complete Journey Data

One year of real grocery purchases from 2,000+ households, spread across several related tables.

# !pip install completejourney-py   # Colab only
from completejourney_py import get_data

cj_data = get_data()
cj_data.keys()
dict_keys(['campaign_descriptions', 'coupons', 'promotions', 'campaigns', 'demographics', 'transactions', 'coupon_redemptions', 'products'])

Docs: bit.ly/completejourney_py

Small Group Brainstorm

In your group:

  1. Browse the datasets available in Complete Journey: Complete Journey dataset guide
  2. Come up with 2–3 questions a grocery retailer would want answered with this data.

Example questions:

  • What income level is buying the most?
  • Which department and product is the most commonly purchased?
  • Which coupon was used the most?

Manipulating Data

🎯 What Would You Fix?

Real data rarely arrives ready for analysis. Import the raw Ames housing data in your notebook:

import pandas as pd

url = "https://raw.githubusercontent.com/bradleyboehmke/uc-bana-4080/main/data/ames_raw.csv"
ames = pd.read_csv(url)

Spend a couple of minutes looking through it with your Week 3 tools — .head(), .columns, .info(), .describe().

What would you want to change before you start an analysis?

Four Common Data Issues

Note

Datasets can be messy in many different ways. Today we’ll illustrate four common data issues you’ll often need to address.

  1. Inconsistent column names — MS SubClass, SalePrice, Year Remod/Add
  2. Columns we don’t need — Order, PID
  3. Metrics that don’t exist yet — price per square foot?
  4. Missing values — lots of NaN
ames.head()
Order PID MS SubClass MS Zoning Lot Frontage Lot Area Street Alley Lot Shape Land Contour ... Pool Area Pool QC Fence Misc Feature Misc Val Mo Sold Yr Sold Sale Type Sale Condition SalePrice
0 1 526301100 20 RL 141.0 31770 Pave NaN IR1 Lvl ... 0 NaN NaN NaN 0 5 2010 WD Normal 215000
1 2 526350040 20 RH 80.0 11622 Pave NaN Reg Lvl ... 0 NaN MnPrv NaN 0 6 2010 WD Normal 105000
2 3 526351010 20 RL 81.0 14267 Pave NaN IR1 Lvl ... 0 NaN NaN Gar2 12500 6 2010 WD Normal 172000
3 4 526353030 20 RL 93.0 11160 Pave NaN Reg Lvl ... 0 NaN NaN NaN 0 4 2010 WD Normal 244000
4 5 527105010 60 RL 74.0 13830 Pave NaN IR1 Lvl ... 0 NaN MnPrv NaN 0 3 2010 WD Normal 189900

5 rows × 82 columns

Cleaning Up Column Names

A few columns → rename() with an {old: new} dict

ames = ames.rename(columns={
    'MS SubClass': 'ms_subclass',
    'MS Zoning': 'ms_zoning'
})
ames.columns[:5]
Index(['Order', 'PID', 'ms_subclass', 'ms_zoning', 'Lot Frontage'], dtype='object')

All 82 columns → .str methods on .columns

ames.columns = (
    ames.columns
    .str.lower()               # lowercase
    .str.replace(' ', '_')     # spaces  -> underscores
    .str.replace('/', '_')     # slashes -> underscores
)
ames.columns[:5]
Index(['order', 'pid', 'ms_subclass', 'ms_zoning', 'lot_frontage'], dtype='object')

Assign it back — or nothing changes

pandas methods return a new result; they don’t change ames on their own. Running ames.rename(...) by itself prints a renamed copy and throws it away. That’s why every example today starts with ames = ... or ames.columns = ....

Selecting vs. Dropping Columns

Last week you selected the columns you wanted. Sometimes it’s easier to drop the ones you don’t.

Keep columns of interest

cols = ['neighborhood', 'gr_liv_area', 'saleprice']
ames[cols].head(3)
neighborhood gr_liv_area saleprice
0 NAmes 1656 215000
1 NAmes 896 105000
2 NAmes 1329 172000

Drop columns of disinterest

ames = ames.drop(columns=['order', 'pid'])
ames.shape
(2930, 80)

Tip

Rule of thumb: keeping a few columns → select. Removing a few columns → drop.

Creating New Columns

Build a new metric from existing columns

ames['price_per_sqft'] = (
    ames['saleprice'] / ames['gr_liv_area']
)

ames[['saleprice', 'gr_liv_area', 'price_per_sqft']].head(3)
saleprice gr_liv_area price_per_sqft
0 215000 1656 129.830918
1 105000 896 117.187500
2 172000 1329 129.420617

Translate values with .map() and a dict

months = {1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr',
          5: 'May', 6: 'Jun', 7: 'Jul', 8: 'Aug',
          9: 'Sep', 10: 'Oct', 11: 'Nov', 12: 'Dec'}

ames['month_sold'] = ames['mo_sold'].map(months)
ames[['mo_sold', 'month_sold']].head(3)
mo_sold month_sold
0 5 May
1 6 Jun
2 6 Jun

Note

The pattern is always the same: df['new_col'] = <something computed from other columns>.

Handling Missing Values

Step 1 — Find it

ames.isnull().sum().sort_values(ascending=False).head()
pool_qc         2917
misc_feature    2824
alley           2732
fence           2358
mas_vnr_type    1775
dtype: int64

Step 2 — Ask why. 2,917 homes are missing pool_qc… and 2,917 homes have a pool_area of 0.

Step 3 — Fill it with what it actually means

ames['pool_qc'] = ames['pool_qc'].fillna('no pool')
ames['pool_qc'].value_counts()
pool_qc
no pool    2917
Ex            4
Gd            4
TA            3
Fa            2
Name: count, dtype: int64

Important

Missing isn’t always an error. Here NaN means “there’s no pool” — so the right fix is a label, not a deleted row or a made-up average. Understand why data is missing before you decide how to handle it.

🎯 Your Turn: Clean It Up

Back to Complete Journey. In your notebook:

  1. In products, create a clean_category column: product_category in lowercase with spaces replaced by underscores.
    • Optional: some products are missing a category. See if you can fill these in with "unknown".
  2. In transactions, create a unit_price column: sales_value divided by quantity.
  3. Sort transactions by unit_price, highest first. What looks strange?
products = cj_data['products']
transactions = cj_data['transactions']

Your Turn Solutions 🎯

1 — Chain .str methods on a column, then assign the result to a new column:

products = cj_data['products']

products['clean_category'] = (
    products['product_category']
    .str.lower()
    .str.replace(' ', '_')
)
products[['product_category', 'clean_category']].head(3)
product_category clean_category
0 FRZN ICE frzn_ice
1 None None
2 BREAD bread

Optional — fill the missing categories. .fillna() is just one more link in the chain:

products['clean_category'] = (
    products['product_category']
    .str.lower()
    .str.replace(' ', '_')
    .fillna('unknown')
)
print(f"Missing categories filled: {products['product_category'].isnull().sum()}")
products[['product_category', 'clean_category']].head(3)
Missing categories filled: 540
product_category clean_category
0 FRZN ICE frzn_ice
1 None unknown
2 BREAD bread

2 & 3 — Compute, then sort:

transactions = cj_data['transactions']

transactions['unit_price'] = transactions['sales_value'] / transactions['quantity']
transactions.sort_values('unit_price', ascending=False)[['product_id', 'sales_value', 'quantity', 'unit_price']].head(3)
product_id sales_value quantity unit_price
255710 1102453 1.000000e-02 0 inf
1222646 836043 8.881784e-16 0 inf
434804 878234 1.000000e-02 0 inf

Warning

inf? Some transactions have a quantity of 0, and dividing by zero gives infinity. The code ran fine — the data surprised us. Always sanity-check a new column before you build an analysis on it.

Summarizing Data

From Rows to Answers

transactions has 1.4 million rows. Nobody wants to read them — they want answers:

  • What are total sales?
  • Which households spend the most?
  • Which stores bring in the most revenue?

Note

Most business questions are aggregate questions — they collapse many rows into a few numbers, often one number per group.

Summarizing the Whole Table

One statistic, one column

total_sales = transactions['sales_value'].sum()
print(f"Total sales: ${total_sales:,.2f}")
Total sales: $4,596,039.58

Several statistics, several columns → .agg()

transactions.agg({
    'sales_value': ['sum', 'mean'],
    'quantity': ['sum', 'max']
})
sales_value quantity
sum 4.596040e+06 152923108.0
mean 3.128032e+00 NaN
max NaN 89638.0

Tip

.agg() takes a dict of {column: statistic(s)}. Common statistics: 'sum', 'mean', 'median', 'min', 'max', 'count', 'nunique'.

The Groupby Model

Grouped aggregation always follows the same three steps: split the rows into groups, apply a summary to each group, combine the results into a new DataFrame.

transactions.groupby('store_id', as_index=False).agg({'sales_value': 'sum'}).head(3)
store_id sales_value
0 2 13.99
1 12 12.06
2 19 20.00

Group → Aggregate → Sort

Business Question: Who are our top 5 households by total spend — and how many shopping trips did it take them?

(
    transactions
    .groupby('household_id', as_index=False)
    .agg({'sales_value': 'sum', 'basket_id': 'nunique'})
    .rename(columns={'basket_id': 'n_baskets'})
    .nlargest(5, 'sales_value')
)
household_id sales_value n_baskets
1015 1023 24879.75 375
1593 1609 16581.36 245
1440 1453 13376.23 502
2294 2322 13187.54 178
1417 1430 12849.31 202

Tip

  • 'nunique' counts distinct baskets — a basket with 20 items is still one trip.
  • The result is a count of baskets, not a basket ID — so .rename() it to say what it holds.
  • Want a group for every combination of two columns? Pass a list: .groupby(['household_id', 'store_id']).
  • as_index=False keeps the group labels as a normal column instead of the index.

🎯 Your Turn: Summarize It

Using transactions:

  1. Find the top 10 products (product_id) by total sales_value.
  2. For each store, compute total sales and the number of unique baskets. Which store brings in the most revenue?
  3. Look back at your answer to #1. Anything surprising about the #1 product?

Your Turn Solutions 🎯

1 — Top 10 products

(
    transactions
    .groupby('product_id', as_index=False)
    .agg({'sales_value': 'sum'})
    .nlargest(10, 'sales_value')
)
product_id sales_value
42369 6534178 303116.02
42340 6533889 27467.61
23098 1029743 22729.71
42365 6534166 20477.54
42333 6533765 19451.66
27971 1082185 17219.59
12526 916122 16120.01
30193 1106523 15629.95
19879 995242 15602.59
39480 5569230 13410.46

2 — Store performance

(
    transactions
    .groupby('store_id', as_index=False)
    .agg({'sales_value': 'sum', 'basket_id': 'nunique'})
    .rename(columns={'basket_id': 'n_baskets'})
    .sort_values('sales_value', ascending=False)
    .head()
)
store_id sales_value n_baskets
103 367 148169.67 4194
124 406 120904.39 3037
135 429 86883.92 2323
97 361 81196.08 2850
87 343 80700.57 2929

Important

3 — Product 6534178 brings in about 10× more than any other product. So… what is it?

transactions can’t tell us — it only stores the ID. The product details live in a different table.

Joining Data

What Is Product 6534178?

Organizations store data in separate tables — it’s more efficient, and different teams collect different pieces.

To answer a question that spans tables, we join them on a key: a column that appears in both.

  • product_id → transactions + products
  • household_id → transactions + demographics
  • coupon_upc → coupons + coupon_redemptions

Warning

A key needs the same meaning and the same data type in both tables — otherwise rows fail to match, or match wrong.

Four Types of Joins

The join type decides what happens to rows that don’t find a match.

Inner — only rows with a match in both tables

Right — all rows from the right table, matches from the left

Left — all rows from the left table, matches from the right

Outer — all rows from both tables

Joining with .merge()

# how = 'inner', 'left', 'right', or 'outer'
left_df.merge(right_df, on='key_column', how='inner')

Tip

pd.merge(left_df, right_df, on='key_column', how='inner') does the same thing — you’ll see both.

Mystery solved: summarize first, then join on the product details.

top_products = (
    transactions
    .groupby('product_id', as_index=False)
    .agg({'sales_value': 'sum'})
    .nlargest(5, 'sales_value')
)

product_info = products[['product_id', 'department', 'product_type']]
top_products.merge(product_info, on='product_id', how='left')
product_id sales_value department product_type
0 6534178 303116.02 FUEL GASOLINE-REG UNLEADED
1 6533889 27467.61 MISCELLANEOUS GASOLINE-REG UNLEADED
2 1029743 22729.71 GROCERY FLUID MILK WHITE ONLY
3 6534166 20477.54 MISCELLANEOUS GASOLINE-REG UNLEADED
4 6533765 19451.66 FUEL GASOLINE-REG UNLEADED

Important

Four of our top five “products” are gasoline — the kiosk at the store. Our real grocery best seller is a gallon of milk. A join turned an ID into an insight.

🎯 Predict It

  • transactions has 1,469,307 rows from 2,469 households.
  • demographics has one row for each of 801 households.

If we join them on household_id, how many rows come back?

  1. With an inner join?
  2. With a left join (transactions on the left)?

Write down your guesses — then run both in your notebook.

The Join Type Changes Your Data

demographics = cj_data['demographics']

inner = transactions.merge(demographics, on='household_id', how='inner')
left = transactions.merge(demographics, on='household_id', how='left')

print(f"transactions: {len(transactions):,} rows")
print(f"inner join:   {len(inner):,} rows")
print(f"left join:    {len(left):,} rows  ({left['age'].isnull().sum():,} with no demographics)")
transactions: 1,469,307 rows
inner join:   828,850 rows
left join:    1,469,307 rows  (640,457 with no demographics)

Important

Only 801 of 2,469 households filled out a demographic survey.

  • Inner quietly drops more than 40% of all transactions.
  • Left keeps every transaction, but fills the missing demographics with NaN.

Neither one is wrong — but you have to choose, and know what you’ve chosen, before you summarize.

🎯 Your Turn: Join It

Business Question: Our marketing team wants to know which age group spends the most.

  1. Join transactions and demographics. Which join type makes sense here?
  2. For each age group, compute total sales and the number of unique households.
  3. Bonus: add a spend_per_household column. Does the same age group still come out on top?

Your Turn Solution 🎯

Join → group → aggregate — then use last hour’s skill to build a new column:

age_spend = (
    transactions
    .merge(demographics, on='household_id', how='inner')
    .groupby('age', as_index=False)
    .agg({'sales_value': 'sum', 'household_id': 'nunique'})
    .rename(columns={'household_id': 'n_households'})
)

age_spend['spend_per_household'] = age_spend['sales_value'] / age_spend['n_households']
age_spend.sort_values('spend_per_household', ascending=False)
age sales_value n_households spend_per_household
2 35-44 724357.40 194 3733.801031
3 45-54 971822.19 288 3374.382604
1 25-34 453372.46 142 3192.763803
4 55-64 173153.70 59 2934.808475
0 19-24 125673.05 46 2732.022826
5 65+ 176600.84 72 2452.789444

Important

45–54 spends the most in total — but they’re also the biggest group. Per household, 35–44 comes out on top.

Total vs. per-household is exactly the kind of decision that changes a marketing recommendation. Every step today — manipulate, summarize, join — played a part in getting there.

Let’s Wrap This Up

🧾 What You Learned

Code What it does
df = df.rename(columns={'old': 'new'}) Rename specific columns
df.columns = df.columns.str.lower().str.replace(' ', '_') Clean every column name at once
df = df.drop(columns=['a', 'b']) Remove columns you don’t need
df['new'] = df['a'] / df['b'] Create a new column from existing ones
df['col'].map({old: new}) Translate values using a dict
df.isnull().sum() Count missing values in each column
df['col'] = df['col'].fillna(value) Fill missing values — once you know why they’re missing
df.agg({'col': ['sum', 'mean']}) Several statistics across the whole table
df.groupby('grp', as_index=False).agg({...}) One row of statistics per group
'nunique' Count distinct values (baskets, households)
df.sort_values('col', ascending=False) · df.nlargest(n, 'col') Rank your results
left.merge(right, on='key', how='inner') Join tables — how decides what happens to unmatched rows

Important

The ideas underneath all of it:

  1. Assign it back. pandas returns new results — save them, or they’re gone.
  2. Question → manipulate → summarize → join → interpret. Most business questions use all three skills.
  3. Check what your join did. Compare row counts before and after, every time.

Thursday’s Lab & Questions 🙋

This Week’s Lab: RetailMax Analytics

You’ll play a junior analyst answering questions from leadership with the Complete Journey data — manipulating, summarizing, and joining to find the answers.

Finish the Week 4 readings before Thursday so you can hit the ground running!

Any final questions?

  • Manipulating, summarizing, or joining?
  • Choosing a join type?
  • Thursday’s lab or the homework?