Session 3 lab: Clean it, check it, log it¶

# Course data setup. Run this cell first; you do not need to understand it yet.
# It downloads the Superstore sample from Tableau's website, checks it is the
# exact version this course was written for, and prepares the order-lines table.
import hashlib
import os
import urllib.request

import matplotlib.pyplot as plt
import pandas as pd

SOURCE_URL = "https://public.tableau.com/app/sample-data/sample_-_superstore.xls"
EXPECTED_SHA256 = "5045ed4fec49cae2cc0dfc5ab0d204ba09efa767fe00c996822499e8cc42fbbd"
SOURCE_FILE = os.environ.get("COURSE_XLS_PATH", "sample_-_superstore.xls")

try:
    import xlrd  # noqa: F401  (needed to read .xls files)
except ImportError:
    import subprocess
    import sys

    subprocess.check_call([sys.executable, "-m", "pip", "install", "-q", "xlrd"])

if not os.path.exists(SOURCE_FILE):
    urllib.request.urlretrieve(SOURCE_URL, SOURCE_FILE)

with open(SOURCE_FILE, "rb") as f:
    digest = hashlib.sha256(f.read()).hexdigest()
if digest != EXPECTED_SHA256:
    print("WARNING: Tableau has changed this sample file since the course was written.")
    print("Your numbers will differ from the lesson. Ask your facilitator for the course copy.")


def load_orders():
    """Return one row per order line, orders placed 2023-01-01 to 2025-12-31."""
    raw = pd.read_excel(SOURCE_FILE, sheet_name="Orders")
    returns = pd.read_excel(SOURCE_FILE, sheet_name="Returns")

    # Orders with more than one Customer ID (a known data issue), found before filtering.
    ids_per_order = raw.groupby("Order ID")["Customer ID"].nunique()
    conflict_orders = set(ids_per_order[ids_per_order > 1].index)

    in_scope = (raw["Order Date"] >= "2023-01-01") & (raw["Order Date"] < "2026-01-01")
    df = raw.loc[in_scope].copy()

    df = df.rename(columns={
        "Row ID": "row_id", "Order ID": "order_id", "Order Date": "order_date",
        "Ship Date": "ship_date", "Ship Mode": "ship_mode", "Customer ID": "customer_id",
        "Segment": "segment", "Country/Region": "country_region", "Region": "region",
        "Category": "category", "Sub-Category": "sub_category", "Product ID": "product_id",
        "Product Name": "product_name", "Sales": "sales", "Quantity": "quantity",
        "Discount": "discount", "Profit": "profit",
    })
    df["returned_order_flag"] = df["order_id"].isin(set(returns["Order ID"])).astype(int)
    df["customer_order_conflict"] = df["order_id"].isin(conflict_orders).astype(int)
    df["customer_analysis_eligible"] = 1 - df["customer_order_conflict"]
    df["order_to_ship_days"] = (df["ship_date"] - df["order_date"]).dt.days
    df["order_month"] = df["order_date"].dt.to_period("M").dt.to_timestamp()

    columns = [
        "row_id", "order_id", "order_date", "ship_date", "ship_mode", "customer_id",
        "segment", "country_region", "region", "category", "sub_category", "product_id",
        "product_name", "sales", "quantity", "discount", "profit", "returned_order_flag",
        "customer_analysis_eligible", "customer_order_conflict", "order_to_ship_days",
        "order_month",
    ]
    return df[columns].sort_values("row_id").reset_index(drop=True)


pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})
print("Setup complete.")

Setup complete.

import hashlib
import io


def fetch(url, expected_sha256):
    """Download a file into memory and check its SHA-256 fingerprint."""
    with urllib.request.urlopen(url) as response:
        data = response.read()
    if hashlib.sha256(data).hexdigest() != expected_sha256:
        print("WARNING: this file has changed since the course was written:", url)
        print("Your numbers may differ from the lab. Ask your facilitator.")
    return data


world_cup_bytes = fetch(
    "https://public.tableau.com/app/sample-data/world_cup_results.xlsx",
    "dbad641c16c914c14fef0c95bf6f918fa5d2746671e4dc269b6a1ec3e2a3cc45",
)
mobile_bytes = fetch(
    "https://public.tableau.com/app/sample-data/mobile_os_usage.csv",
    "0db44110e84a31c06491af89a30949cd8ced1c42fac56488cb04ca4f9e449995",
)

matches_raw = pd.read_excel(io.BytesIO(world_cup_bytes), sheet_name="WorldCupMatches")
cups = pd.read_excel(io.BytesIO(world_cup_bytes), sheet_name="WorldCups")
mobile_raw = pd.read_csv(io.BytesIO(mobile_bytes))

print("World Cup matches:", matches_raw.shape)
print("World Cup tournaments:", cups.shape)
print("Mobile OS usage:", mobile_raw.shape)

World Cup matches: (852, 12)
World Cup tournaments: (20, 10)
Mobile OS usage: (1898, 3)

cleaning_log = []


def log_step(dataset, change, why, before, after):
    """Add one row to the cleaning log."""
    cleaning_log.append({"dataset": dataset, "change": change, "why": why,
                         "rows_before": before, "rows_after": after})


print("Cleaning log ready.")

Cleaning log ready.

matches_raw.head()

print(matches_raw.dtypes)
print()
print("Missing values per column:")
print(matches_raw.isna().sum())

Year                    int64
Date           datetime64[ns]
Time                   object
Round                  object
Stadium                object
City                   object
Country                object
HomeTeam               object
HomeGoals               int64
AwayGoals               int64
AwayTeam               object
Observation            object
dtype: object

Missing values per column:
Year           0
Date           0
Time           0
Round          0
Stadium        0
City           0
Country        0
HomeTeam       0
HomeGoals      0
AwayGoals      0
AwayTeam       0
Observation    0
dtype: int64

empty_notes = matches_raw["Observation"].astype(str).str.strip() == ""
print("Observation cells that are empty text:", empty_notes.sum(), "of", len(matches_raw))

Observation cells that are empty text: 787 of 852

check = pd.DataFrame({
    "match_rows": matches_raw.groupby("Year").size(),
    "official_matches": cups.set_index("Year")["MatchesPlayed"],
})
check["difference"] = check["match_rows"] - check["official_matches"]
check[check["difference"] != 0]

is_copy = matches_raw.duplicated()
print("Exact duplicate rows:", is_copy.sum())
print("Years affected:", matches_raw.loc[is_copy, "Year"].value_counts().to_dict())

Exact duplicate rows: 16
Years affected: {2014: 16}

pairs = matches_raw[matches_raw.duplicated(keep=False)]
pairs.sort_values(["Date", "HomeTeam"])[["Year", "Date", "Round", "HomeTeam", "HomeGoals", "AwayGoals", "AwayTeam"]].head(6)

before = len(matches_raw)
matches = matches_raw.drop_duplicates().copy()
log_step("World Cup matches", "Removed exact duplicate rows",
         "Same match recorded twice; match count exceeded the official total", before, len(matches))

check["match_rows_clean"] = matches.groupby("Year").size()
print("Years where clean rows differ from the official count:",
      (check["match_rows_clean"] != check["official_matches"]).sum())

Years where clean rows differ from the official count: 0

for column in ["City", "Stadium", "HomeTeam", "AwayTeam"]:
    text = matches[column].astype(str)
    print(f"{column:9} values with leading or trailing spaces: {(text != text.str.strip()).sum()}")

City      values with leading or trailing spaces: 836
Stadium   values with leading or trailing spaces: 0
HomeTeam  values with leading or trailing spaces: 0
AwayTeam  values with leading or trailing spaces: 0

matches["City"] = matches["City"].str.strip()
log_step("World Cup matches", "Stripped spaces from City", "Every City value ended in a space",
         len(matches), len(matches))
print("Cities with spaces left:", (matches["City"] != matches["City"].str.strip()).sum())

Cities with spaces left: 0

text_columns = ["City", "Stadium", "HomeTeam", "AwayTeam"]
broken = matches[text_columns].apply(lambda column: column.astype(str).str.contains("�"))
print("Rows with a broken character:", broken.any(axis=1).sum())
pd.Series(pd.unique(matches[text_columns].where(broken).stack())).head(12)

Rows with a broken character: 73





0                                   Stade V�lodrome
1     Maracan� - Est�dio Jornalista M�rio Filho
2                                             Malm�
3                                        Norrk�Ping
4                           Nou Camp - Estadio Le�n
5                                        D�Sseldorf
6                      Estadio Jos� Mar�a Minella
7                 Estadio Ol�mpico Chateau Carreras
8                     Estadio Municipal de Bala�dos
9                                         La Coru�A
10                   Estadio Ol�mpico Universitario
11                                    C�te d'Ivoire
dtype: object

matches["text_broken_flag"] = broken.any(axis=1).astype(int)
log_step("World Cup matches", "Flagged rows with broken characters (not corrected)",
         "Encoding damage; original letters cannot be recovered from the file", len(matches), len(matches))
matches["text_broken_flag"].value_counts()

text_broken_flag
0    763
1     73
Name: count, dtype: int64

teams = pd.concat([matches["HomeTeam"], matches["AwayTeam"]])
teams[teams.str.contains("Iran|Germany")].value_counts()

Germany FR    62
Germany       44
Iran           6
IR Iran        6
Name: count, dtype: int64

orders_raw = pd.read_excel(SOURCE_FILE, sheet_name="Orders")
returns_raw = pd.read_excel(SOURCE_FILE, sheet_name="Returns")
people_raw = pd.read_excel(SOURCE_FILE, sheet_name="People")

print("Orders: ", orders_raw.shape)
print("Returns:", returns_raw.shape)
print("People: ", people_raw.shape)
people_raw

Orders:  (10194, 21)
Returns: (296, 2)
People:  (4, 2)

print("Returns: is Order ID unique?", returns_raw["Order ID"].is_unique)
print("People:  is Region unique?  ", people_raw["Region"].is_unique)
print("Returns: values in Returned:", returns_raw["Returned"].unique())

Returns: is Order ID unique? True
People:  is Region unique?   True
Returns: values in Returned: ['Yes']

step1 = orders_raw.merge(returns_raw, on="Order ID", how="left",
                         validate="many_to_one", indicator="returns_match")
step2 = step1.merge(people_raw, on="Region", how="left",
                    validate="many_to_one", indicator="people_match")

print("Rows: orders", len(orders_raw), "| after Returns", len(step1), "| after People", len(step2))
print()
print(step2["returns_match"].value_counts())
print()
print(step2["people_match"].value_counts())

Rows: orders 10194 | after Returns 10194 | after People 10194

returns_match
left_only     9394
both           800
right_only       0
Name: count, dtype: int64

people_match
both          10194
left_only         0
right_only        0
Name: count, dtype: int64

print("Sales before joins:", round(orders_raw["Sales"].sum(), 2))
print("Sales after joins: ", round(step2["Sales"].sum(), 2))
print("Lines without a manager:", step2["Regional Manager"].isna().sum())
print("Listed orders found in Orders:", returns_raw["Order ID"].isin(orders_raw["Order ID"]).sum(),
      "of", len(returns_raw))

Sales before joins: 2326534.35
Sales after joins:  2326534.35
Lines without a manager: 0
Listed orders found in Orders: 296 of 296

orders_joined = step2.drop(columns=["returns_match", "people_match"])
orders_joined["returned_listed"] = orders_joined["Returned"].eq("Yes").astype(int)

log_step("Superstore", "Left-joined Returns (on Order ID) and People (on Region)",
         "Add returns listing and regional manager to each line; validated many-to-one",
         len(orders_raw), len(orders_joined))
orders_joined[["Order ID", "Region", "Regional Manager", "Returned", "returned_listed"]].head()

print(mobile_raw.dtypes)
print()
print("Missing values per column:")
print(mobile_raw.isna().sum())
mobile_raw.head()

Date                        object
Mobile Operating System     object
Percent of Usage           float64
dtype: object

Missing values per column:
Date                       1772
Mobile Operating System       0
Percent of Usage              0
dtype: int64

dated = mobile_raw.dropna(subset=["Date"])
dated.groupby("Date").agg(
    systems=("Mobile Operating System", "nunique"),
    percent_total=("Percent of Usage", "sum"),
)

before = len(mobile_raw)
mobile = mobile_raw.dropna(subset=["Date"]).copy()
mobile["Date"] = pd.to_datetime(mobile["Date"])

month_totals = mobile.groupby("Date")["Percent of Usage"].sum()
complete_months = month_totals[(month_totals - 100).abs() < 0.5].index
mobile["complete_month"] = mobile["Date"].isin(complete_months).astype(int)

log_step("Mobile OS", "Kept only rows with a date; flagged incomplete months",
         "Undated rows cannot be placed in time; row order is not evidence", before, len(mobile))
mobile.groupby("Date")["complete_month"].first()

Date
2008-12-01    1
2009-01-01    1
2009-02-01    1
2009-03-01    1
2009-04-01    0
Name: complete_month, dtype: int64

# 1. Run the cell: it charts the share of page views for two systems, complete months only.
# 2. Change the list to two or three other systems, for example "BlackBerry OS" or "Android".
# 3. Rewrite the title so it says exactly what the chart shows, including the period.
selected = ["iOS", "SymbianOS"]

subset = mobile[(mobile["complete_month"] == 1) & mobile["Mobile Operating System"].isin(selected)]
chart = subset.pivot(index="Date", columns="Mobile Operating System", values="Percent of Usage")
ax = chart.plot(marker="o", title="Share of mobile page views, Dec 2008 – Mar 2009 (Statcounter)")
ax.set_xlabel("")
ax.set_ylabel("Percent of page views")
plt.tight_layout()
plt.show()

# 1. Run the cell and find the tournament where raw and clean differ.
# 2. Change "goals_per_match" to "matches" to compare match counts instead.
# 3. Then compare the clean goals with the official GoalsScored column in cups.
def per_tournament(table):
    goals = table["HomeGoals"] + table["AwayGoals"]
    return pd.DataFrame({
        "matches": table.groupby("Year").size(),
        "goals": goals.groupby(table["Year"]).sum(),
    }).assign(goals_per_match=lambda t: t["goals"] / t["matches"])


comparison = pd.DataFrame({
    "raw": per_tournament(matches_raw)["goals_per_match"],
    "clean": per_tournament(matches)["goals_per_match"],
})
comparison.tail(5)

# 1. Edit the mapping: add or remove names you decide to combine.
# 2. Fill in the reason in log_step. If you change nothing, log that decision too.
name_map = {"IR Iran": "Iran"}

before_names = pd.concat([matches["HomeTeam"], matches["AwayTeam"]]).nunique()
matches["HomeTeam"] = matches["HomeTeam"].replace(name_map)
matches["AwayTeam"] = matches["AwayTeam"].replace(name_map)
after_names = pd.concat([matches["HomeTeam"], matches["AwayTeam"]]).nunique()

log_step("World Cup matches", f"Renamed teams: {name_map}", "Write your reason here",
         len(matches), len(matches))
print("Different team names before:", before_names, "| after:", after_names)

Different team names before: 83 | after: 82

pd.DataFrame(cleaning_log)

# 1. Run the cell. Which month's column does not add up to about 100?
# 2. Change sort_values("2008-12-01") to sort by another month.
wide = mobile.pivot(index="Mobile Operating System", columns="Date", values="Percent of Usage")
wide.columns = wide.columns.strftime("%Y-%m-%d")

print("Column totals:")
print(wide.sum().round(2))
wide.sort_values("2008-12-01", ascending=False).head(8)

Column totals:
Date
2008-12-01   100.00
2009-01-01   100.00
2009-02-01   100.01
2009-03-01    99.99
2009-04-01    99.17
dtype: float64

long_again = wide.reset_index().melt(id_vars="Mobile Operating System", var_name="Date",
                                     value_name="Percent of Usage").dropna()
print("Rows in mobile:", len(mobile), "| rows after reshaping back:", len(long_again))

Rows in mobile: 126 | rows after reshaping back: 126

# 1. Run the cell.
# 2. Change the period to only 2025 ("2025-01-01" to "2026-01-01") and run again.
start, end = "2023-01-01", "2026-01-01"

order_dates = orders_raw.groupby("Order ID")["Order Date"].min()
listed_dates = order_dates.reindex(returns_raw["Order ID"])
in_period = (listed_dates >= start) & (listed_dates < end)

print("Orders on the returns list:", len(returns_raw))
print("Of those, placed in the period:", in_period.sum())

Orders on the returns list: 296
Of those, placed in the period: 191

# The colleague's code. It runs without errors, but the total is wrong.
# 1. Find out why the joined table has more rows than orders_raw.
# 2. Fix the targets table or the join so every order line appears exactly once.
# 3. Add validate= so the same mistake can never pass silently again.
targets = pd.DataFrame({
    "Region": ["West", "West", "East", "Central", "South"],
    "version": ["original", "revised", "original", "original", "original"],
    "target": [700_000, 760_000, 650_000, 500_000, 400_000],
})

with_targets = orders_raw.merge(targets, on="Region", how="left")
print("Rows:", len(orders_raw), "->", len(with_targets))
print("Total Sales reported by the colleague:", round(with_targets["Sales"].sum(), 2))
print("Total Sales in the Orders sheet:      ", round(orders_raw["Sales"].sum(), 2))

Rows: 10194 -> 13447
Total Sales reported by the colleague: 3066347.96
Total Sales in the Orders sheet:       2326534.35

mobile_raw.iloc[118:132]