Small Group Brainstorm

In your group, come up with 2–3 questions you’d like to answer using these datasets. Think about business insights a grocery retailer might want.

Then we’ll take a few responses…

From Questions to Analysis

A lot of the insights you’ve just brainstormed will require:

• Manipulating & wrangling data to prepare it for analysis
  • Cleaning up column names for clarity
    
  • Creating new columns based on existing ones
    
• Aggregating data
  • Summarizing at different levels (by product, by customer, by time)
    
  • Computing summary statistics
    
• Joining datasets
  • Combining related tables to get a complete picture

Why Is This Important?

• Data rarely comes perfectly ready for analysis.
• Column names might be unclear or inconsistent.
• Some columns might not be needed at all.
• You might need to create new columns from existing ones.
• Clean, well-structured data = easier, faster, and more accurate analysis.

Messy Data

Our complete journey data is not too bad; however, let’s look at this raw Ames data.

What do you notice?

import pandas as pd

ames = pd.read_csv("../data/ames_raw.csv")
ames.head()

Examples from Our Data

• Columns differ (Order, MS SubClass, SalePrice) - might want to standardize.
• Do we really need all these columns (Order, PID) - might want to drop irrelevant ones.
• Could we add new columns (price_per_sqft)
• We’ve got missing values - (houses without pools or misc features)

Renaming Columns

We can easily rename specific columns

ames.rename(columns={
    'MS SubClass': 'ms_subclass',
    'MS Zoning': 'ms_zoning'
    }, inplace=True)

ames.head()

Reformatting Many Columns

😳

(
  ames.columns
  .str.lower()               # convert to lowercase
  .str.replace(' ', '_')     # replace spaces with underscores
  .str.replace('-', '_')     # replace hyphens with underscores
  .str.strip()               # strip out extra leading/ending spaces
)

Index(['order', 'pid', 'ms_subclass', 'ms_zoning', 'lot_frontage', 'lot_area',
       'street', 'alley', 'lot_shape', 'land_contour', 'utilities',
       'lot_config', 'land_slope', 'neighborhood', 'condition_1',
       'condition_2', 'bldg_type', 'house_style', 'overall_qual',
       'overall_cond', 'year_built', 'year_remod/add', 'roof_style',
       'roof_matl', 'exterior_1st', 'exterior_2nd', 'mas_vnr_type',
       'mas_vnr_area', 'exter_qual', 'exter_cond', 'foundation', 'bsmt_qual',
       'bsmt_cond', 'bsmt_exposure', 'bsmtfin_type_1', 'bsmtfin_sf_1',
       'bsmtfin_type_2', 'bsmtfin_sf_2', 'bsmt_unf_sf', 'total_bsmt_sf',
       'heating', 'heating_qc', 'central_air', 'electrical', '1st_flr_sf',
       '2nd_flr_sf', 'low_qual_fin_sf', 'gr_liv_area', 'bsmt_full_bath',
       'bsmt_half_bath', 'full_bath', 'half_bath', 'bedroom_abvgr',
       'kitchen_abvgr', 'kitchen_qual', 'totrms_abvgrd', 'functional',
       'fireplaces', 'fireplace_qu', 'garage_type', 'garage_yr_blt',
       'garage_finish', 'garage_cars', 'garage_area', 'garage_qual',
       'garage_cond', 'paved_drive', 'wood_deck_sf', 'open_porch_sf',
       'enclosed_porch', '3ssn_porch', 'screen_porch', 'pool_area', 'pool_qc',
       'fence', 'misc_feature', 'misc_val', 'mo_sold', 'yr_sold', 'sale_type',
       'sale_condition', 'saleprice'],
      dtype='object')

Reformatting Many Columns

😎

ames.columns = (
  ames.columns.
  str.lower()               # convert to lowercase
  .str.replace(' ', '_')    # replace spaces with underscores
  .str.replace('-', '_')    # replace hyphens with underscores
  .str.strip()              # strip out extra leading/ending spaces
)

ames.head()

Dropping Columns

Last week we learned how to select columns of interest.

But we may also want to invert that thinking and just drop columns of disinterest!

cols = ['gr_liv_area', 'saleprice', 'overall_qual']

ames[cols].head()

cols = ['order', 'pid', 'ms_subclass']

ames.drop(columns=cols, inplace=True)
ames.head()

Adding/Modifying New Columns

Why we might do this:

• To create new metrics that don’t exist in the raw data (e.g., price_per_sqft, unit_price).
• To transform existing data into a more useful format (e.g., converting grams to pounds).
• To transform values (e.g., months 1, 2, 3 → "Jan", "Feb", "Mar").

Adding/Modifying New Columns

Why we might do this:

• To create new metrics that don’t exist in the raw data (e.g., price_per_sqft, unit_price).
• To transform existing data into a more useful format (e.g., converting grams to pounds).
• To transform values (e.g., months 1, 2, 3 → "Jan", "Feb", "Mar").

# Create a price per sqft column
ames['price_per_sqft'] = ames['saleprice'] / ames['gr_liv_area']

ames.head()

Adding/Modifying New Columns

Why we might do this:

• To create new metrics that don’t exist in the raw data (e.g., price_per_sqft, unit_price).
• To transform existing data into a more useful format (e.g., converting grams to pounds).
• To transform values (e.g., months 1, 2, 3 → "Jan", "Feb", "Mar").

# dict containing mapping of {old values: new values}
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'
}

# use map function to apply changes to a column
ames['mo_sold'] = ames['mo_sold'].map(months)
ames.head()

Handling Missing Values

We can always check for missing values with isnull().

ames.isnull().sum().sort_values(ascending=False)

pool_qc           2917
misc_feature      2824
alley             2732
fence             2358
mas_vnr_type      1775
                  ... 
yr_sold              0
sale_type            0
sale_condition       0
saleprice            0
price_per_sqft       0
Length: 80, dtype: int64

Handling Missing Values

ames[['pool_area', 'pool_qc']].head()

ames['pool_qc'].value_counts()

pool_qc
Ex    4
Gd    4
TA    3
Fa    2
Name: count, dtype: int64

ames['pool_qc'].fillna('no pool', inplace=True)
ames[['pool_area', 'pool_qc']].head()

ames[['pool_qc']].value_counts()

pool_qc
no pool    2917
Ex            4
Gd            4
TA            3
Fa            2
Name: count, dtype: int64

Simple Aggregation

Last week we saw how we can compute various summary stats for a given column:

avg_price = ames['saleprice'].mean()
print(f"Avg Sale Price: ${avg_price:,.2f}")

Avg Sale Price: $180,796.06

min_ppsqft = ames['price_per_sqft'].min()
max_ppsqft = ames['price_per_sqft'].max()
print(f"Min & Max Price per Sqft: ${min_ppsqft:,.2f} - ${max_ppsqft:,.2f}")

Min & Max Price per Sqft: $15.37 - $276.25

cols = ['gr_liv_area', 'saleprice', 'price_per_sqft']
ames[cols].mean()

gr_liv_area         1499.690444
saleprice         180796.060068
price_per_sqft       121.303619
dtype: float64

Multiple Aggregations

But, when we want to get more complicated and get:

• Multiple stats for different columns and…
• Multiple types of stats per column

Then we should start using .aggregate() / .agg()

ames.aggregate({
    'saleprice': ['mean', 'median'],
    'price_per_sqft': ['mean', 'min', 'max']
})

ames.agg({
    'saleprice': ['mean', 'median'],
    'price_per_sqft': ['mean', 'min', 'max']
})

Group-level Aggregations

That’s great and all but in many real-world analyses, we’re interested in summarizing within groups rather than across the whole dataset.

• Total home sales by neighborhood
• Average square footage by number of bedrooms
• Median sale price by year
• Maximum temperature by month

The Groupby Model

Grouped aggregation in Pandas always follows the same three-step process:

1. Group the data using groupby()
2. Apply a summary method like .sum(), .agg(), or .describe()
3. Return a DataFrame of group-level summaries

Avg Sale Price By Neighborhood

(
  ames.
  groupby('neighborhood', as_index=False).
  agg({'saleprice': ['mean', 'median']})
)

Group-by Multiple Variables

(
  ames.
  groupby(['neighborhood', 'mo_sold'], as_index=False).
  agg({'saleprice': 'mean'})
)

🧑‍💻 Code With Me: Store Performance

Business Question: Which stores are performing best? Our operations team wants to understand store-level performance.

# Look at store information in our transactions
transactions['store_id'].value_counts().head()

store_id
367    41334
406    32602
356    27910
292    26692
381    24398
Name: count, dtype: int64

# Fill in the blanks together!
store_performance = (
    transactions
    .groupby('_______', as_index=False)
    .agg({
        'sales_value': ['_______', '_______'],  # sum, mean
        'basket_id': '_______'                  # count (for # of transactions)
    })
)

store_performance.head()

Complete Journey

Think back to your brainstorm from earlier - Which of your group’s questions require information from more than one dataset?

Joining Data

The Importance of Keys

• Keys are the columns used to match rows between two datasets.

The Importance of Keys

• Keys are the columns used to match rows between two datasets.

Types of Joins

There are 4 primary types of joins you’ll read about this week:

Types of Joins

There are 4 primary types of joins you’ll read about this week:

Types of Joins

There are 4 primary types of joins you’ll read about this week:

Types of Joins

There are 4 primary types of joins you’ll read about this week:

Types of Joins

There are 4 primary types of joins you’ll read about this week:

Pandas merge() Basics

We use merge() to join datasets.

pd.merge(
  left_df,          # left DF
  right_df,         # right DF
  on='key_column',  # column(s) to join on
  how='inner'       # type of join
  )      

(
  left_df.               # start with left DF
  merge(right_df,        # right DF
        on='key_column', # column(s) to join on
        how='inner'      # type of join
        )           
)

Example

What is the total sales value for the top 10 selling products?

transactions = cj_data["transactions"]
products = cj_data["products"]

(
    transactions
    .merge(products, how='inner', on='product_id')
    .groupby(['product_id', 'product_category'], as_index=False)
    .agg({'sales_value': 'sum'})
    .nlargest(10, 'sales_value')
)

🧑‍💻 Code With Me: Family Spending Analysis

Continuing our analysis: Now let’s compare average spending by family type.

# Fill in the blanks together!
family_spending = (
    family_data
    .groupby('_______', as_index=False)
    .agg({'sales_value': ['_______', '_______', 'count']})  # mean, sum
)

family_spending

🧾 Quick Reference