Module 6 Lab Companion

↑ Back to Lab Contents

Module 6 Learning Goals

In this lab you clean the text and category columns of a small, deliberately messy customer contact file (15 customers). Text columns are messy in different ways, but two techniques handle most of the work. Standardization makes labels that mean the same thing look the same, for example TX, Texas and tx.. Recoding groups labels into new business categories, for example Premium and VIP into High Value. By the end of the lab you should be able to:

• Inspect messy labels with value_counts(dropna=False) instead of reading rows one by one.
• Clean text with .str.strip(), .str.lower(), .str.upper(), .str.title() and .str.replace().
• Turn cleaned labels into final categories with .map() and a mapping dictionary that covers every label.
• Create a flag with .str.contains() and split a simple field with .str.split().
• Explain the difference between standardizing and recoding, and document each decision.

The main idea: standardize first, then map. Every mapping is a business rule that you must be able to explain.

↑ Back to Lab Contents

Before You Start: Repository and Setup

Modules 5 and 6 share one repository. Do not create a new repository for this lab: keep working in the one you created from tianhaiz/ban6003-week-05-06-pandas-transformation-template for Lab 5.

1. Open your own Week 5–6 repository, not the instructor template and not an older repository. In GitHub Desktop, check the repository name at the top left before you start.
2. Open it in GitHub Codespaces (main path), or in local VS Code with the base conda environment (optional path).
3. Open notebooks/module6_string_categorical_guided_lab.ipynb and run the cells from the top. If you restart the kernel, run from the top again.

0.1 Load pandas and set display options

from pathlib import Path
import pandas as pd

pd.set_option("display.max_columns", None)
pd.set_option("display.width", 120)

0.2 Load the raw file safely

The path starts with .. because the notebook runs inside the notebooks folder; .. means one folder up, the top of your repository.

raw_path = Path("../data/raw/messy_customer_contacts.csv")

if not raw_path.is_file():
    raise FileNotFoundError(f"Required raw input was not found: {raw_path}")

df_raw = pd.read_csv(raw_path)
df_raw.head(10)

↑ Back to Lab Contents

Part 1: First Look at Messy Text and Categories

Inspect before you change anything. Do not assume you know every label.

1.1 Structure of the file

df_raw.info()

df_raw[["state_raw", "segment_raw", "contact_method_raw", "channel_raw", "product_raw"]].head(10)

1.2 Count every state label

df_raw["state_raw"].value_counts(dropna=False)

1.3 Count every contact method label

df_raw["contact_method_raw"].value_counts(dropna=False)

1.4 Count every product label

df_raw["product_raw"].value_counts(dropna=False)

Quick reflection

Write 2–3 short observations in the notebook, for example: “Texas appears as TX, Texas, tx. and TX with a trailing space.” Any concrete observation from the counts is fine.

↑ Back to Lab Contents

Part 2: Keep the Raw Data and Create a Working Copy

Make a working copy

df = df_raw.copy()
df.head()

↑ Back to Lab Contents

Part 3: Basic String Standardization: Customer Names

3.1 Clean the customer names

df["customer_name_clean"] = (
    df["customer_name"]
    .str.strip()
    .str.replace(r"\s+", " ", regex=True)
    .str.title()
)

df[["customer_name", "customer_name_clean"]].head(10)

Cleaning the names does not change who the customer is; it only makes the text consistent. Always compare the raw and clean columns side by side, as the cell does: that is how you spot a step you forgot.

↑ Back to Lab Contents

Part 4: Standardize State Labels

The goal is one two-letter code per state. Work in two steps: first a step 1 column with general cleaning, then a mapping dictionary for the rest.

4.1 Step 1: strip, upper case, remove periods

df["state_step1"] = (
    df["state_raw"]
    .str.strip()
    .str.upper()
    .str.replace(".", "", regex=False)
)

df[["state_raw", "state_step1"]].drop_duplicates().sort_values("state_raw")

4.2 Step 2: map to standard codes

state_map = {
    "TEXAS": "TX",
    "TX": "TX",
    "CALIFORNIA": "CA",
    "CA": "CA",
    "NEW YORK": "NY",
    "NY": "NY",
    "FLORIDA": "FL",
    "FL": "FL",
    "NEVADA": "NV"
}

df["state_clean"] = df["state_step1"].map(state_map)

df[["state_raw", "state_step1", "state_clean"]].drop_duplicates().sort_values("state_raw")

↑ Back to Lab Contents

Part 5: Recode Contact Method Labels

Target categories: Email, Phone, Text and Chat.

5.1 Step 1: lower case, no hyphens, no spaces

df["contact_method_step1"] = (
    df["contact_method_raw"]
    .str.strip()
    .str.lower()
    .str.replace("-", "", regex=False)
    .str.replace(" ", "", regex=False)
)

df[["contact_method_raw", "contact_method_step1"]].drop_duplicates().sort_values("contact_method_raw")

5.2 Map to four contact methods

contact_method_map = {
    "email": "Email",
    "phone": "Phone",
    "phonecall": "Phone",
    "sms": "Text",
    "text": "Text",
    "textmessage": "Text",
    "chat": "Chat"
}

df["contact_method_clean"] = df["contact_method_step1"].map(contact_method_map)

df[["contact_method_raw", "contact_method_clean"]].drop_duplicates().sort_values("contact_method_raw")

↑ Back to Lab Contents

Part 6: Recode Customer Segments into Business Categories

6.1 Standardize, then recode segments

df["segment_step1"] = df["segment_raw"].str.strip().str.lower()

segment_map = {
    "premium": "High Value",
    "vip": "High Value",
    "standard": "Standard",
    "basic": "Standard",
    "student": "Student",
    "unknown": "Unknown"
}

df["segment_group"] = df["segment_step1"].map(segment_map)

df[["segment_raw", "segment_group"]].drop_duplicates().sort_values("segment_raw")

6.2 Count the new groups

df["segment_group"].value_counts(dropna=False)

Reflection

Answer in the notebook in 1–2 sentences: what assumption did we make when we grouped premium and vip together as High Value?

↑ Back to Lab Contents

Part 7: Use .str.contains() to Create Flags

issue_text is free text, not a category: each customer describes the problem in their own words. You do not recode it. Instead you search it for keywords.

7.1 Flag issues that mention fraud

df["possible_fraud_issue"] = df["issue_text"].str.contains(
    "fraud|unauthorized",
    case=False,
    na=False
)

df[["issue_text", "possible_fraud_issue"]].head(15)

↑ Back to Lab Contents

Part 8: Use .str.split() for Simple Parsing

8.1 Split clean names into first and last name

df[["first_name", "last_name"]] = df["customer_name_clean"].str.split(" ", n=1, expand=True)

df[["customer_name_clean", "first_name", "last_name"]].head(10)

↑ Back to Lab Contents

Part 9: Clean Product Categories

9.1 Step 1: lower case, underscores to spaces

df["product_step1"] = (
    df["product_raw"]
    .str.strip()
    .str.lower()
    .str.replace("_", " ", regex=False)
    .str.replace(r"\s+", " ", regex=True)
)

df[["product_raw", "product_step1"]].drop_duplicates().sort_values("product_raw")

9.2 Map to readable product names

product_map = {
    "loan application": "Loan Application",
    "checking": "Checking Account",
    "checking account": "Checking Account",
    "credit card": "Credit Card",
    "mortgage": "Mortgage",
    "auto loan": "Auto Loan",
    "student loan": "Student Loan",
    "personal loan": "Personal Loan"
}

df["product_clean"] = df["product_step1"].map(product_map)

df[["product_raw", "product_clean"]].drop_duplicates().sort_values("product_raw")

↑ Back to Lab Contents

Parts 10–11: Review Table and Recoding Decision Log

10.1 Compact review table

guided_review = df[[
    "customer_id",
    "state_clean",
    "segment_group",
    "contact_method_clean",
    "product_clean",
    "possible_fraud_issue",
    "issue_text",
]].copy()

guided_review.head(10)

Part 11: Recoding decision log

Choose two transformations from state, contact method, segment or product. For each, fill in the four lines in the notebook: the raw labels combined, the resulting category, why the decision is reasonable, and one detail that could be lost.

What to check: name real labels from the counts above (for example SMS, sms and Text Message → Text). For a recoding decision such as segments, write the assumption and the detail lost, for example the difference between Premium and VIP.

↑ Back to Lab Contents

Part 12, Exercise 1: Standardize Channel Labels

df["channel_step1"] = (
    df["channel_raw"]
    .str.strip()
    .str.lower()
    .str.replace("-", " ", regex=False)
    .str.replace(r"\s+", " ", regex=True)
)

df["channel_step1"].value_counts(dropna=False)

channel_step1
web organic         3
mobile app          3
branch              2
web paid            2
call center         2
partner referral    2
unknown             1

channel_map = {
    "web organic": "Web Organic",
    "web paid": "Web Paid",
    "mobile app": "Mobile App",
    "branch": "Branch",
    "call center": "Call Center",
    "partner referral": "Partner Referral",
    "unknown": "Unknown"
}

df["channel_clean"] = df["channel_step1"].map(channel_map)

df[["channel_raw", "channel_clean"]].drop_duplicates().sort_values("channel_raw")

↑ Back to Lab Contents

Part 12, Exercise 2: Create a Digital Channel Flag

digital_channels = ["Web Organic", "Web Paid", "Mobile App"]

df["digital_channel"] = df["channel_clean"].isin(digital_channels)

df[["channel_clean", "digital_channel"]].drop_duplicates().sort_values("channel_clean")

       channel_clean  digital_channel
3             Branch            False
7        Call Center            False
2         Mobile App             True
9   Partner Referral            False
14           Unknown            False
0        Web Organic             True
5           Web Paid             True

↑ Back to Lab Contents

Part 12, Exercise 3: Create State Region Categories

region_map = {
    "TX": "South",
    "FL": "South",
    "CA": "West",
    "NV": "West",
    "NY": "Northeast"
}

df["state_region"] = df["state_clean"].map(region_map)

df[["state_clean", "state_region"]].drop_duplicates().sort_values("state_clean")

   state_clean state_region
3           CA         West
9           FL        South
14          NV         West
6           NY    Northeast
0           TX        South

↑ Back to Lab Contents

Part 13: Build, Verify, and Export the Analysis Dataset

Build and export the analysis file

The final file keeps only the columns needed for the channel-and-region analysis. It leaves out names, email addresses and the free-text issue details.

ready_to_export = (
    "channel_clean" in df.columns
    and "digital_channel" in df.columns
    and "state_region" in df.columns
)

if not ready_to_export:
    print("Complete Exercises 1-3 before exporting the analysis dataset.")
else:
    analysis_df = df[[
        "customer_id",
        "state_clean",
        "state_region",
        "segment_group",
        "contact_method_clean",
        "channel_clean",
        "digital_channel",
        "product_clean",
        "possible_fraud_issue",
    ]].copy()

    print("Rows preserved:", len(analysis_df) == len(df_raw))
    print("Output columns:", analysis_df.columns.tolist())

    output_dir = Path("../data/processed")
    output_dir.mkdir(parents=True, exist_ok=True)
    output_path = output_dir / "customer_contacts_transformed_module6.csv"
    analysis_df.to_csv(output_path, index=False)

    print(f"Saved {len(analysis_df)} rows to: {output_path}")
    display(analysis_df.head())

↑ Back to Lab Contents

Part 14: Offline Assignment: Recoding Fitness Check

Fitness assessment

Write 4–5 sentences: cite at least two results from your checks, explain one business assumption behind the digital or region grouping, name one detail lost through recoding, and say whether you would approve the mappings for this analysis.

↑ Back to Lab Contents

Common Problems

If a cell fails, read the last line of the error message first. Then find it below.

Still stuck? Post the error message and the cell number in the course Q&A discussion. Do not post your full answers.

↑ Back to Lab Contents

Save and Submit

1. Complete Exercises 1–3, both decision-log entries, both reflections, the offline assignment and the fitness assessment.
2. Restart the kernel and run all cells from top to bottom. Confirm there are no errors.
3. Confirm that data/processed/customer_contacts_transformed_module6.csv exists.
4. Save the notebook, then commit and push (GitHub Desktop, the Codespaces Source Control panel, or git add ., git commit, git push).
5. Open your repository on github.com and confirm the notebook and the CSV file are there. Submit your Week 5–6 repository link in Canvas.

↑ Back to Lab Contents

Lab Wrap-Up

You used one pattern on five columns:

• Inspect: value_counts(dropna=False) shows every label, including hidden spaces.
• Standardize: .str.strip(), case functions and .str.replace() turn many spellings into one simple form.
• Map: a dictionary that covers every label turns the simple form into the final category; missing values show what is not covered.
• Recode with care: grouping changes meaning, so record the full mapping and the assumption.
• Search and split: .str.contains() flags keywords in free text; .str.split() separates combined fields.

Before any recoding decision, ask which labels are truly equivalent, what meaning might be lost, and whether another analyst could follow your rule. Module 7 moves from single columns to whole tables: summarizing rows with groupby() and reshaping tables.