Data Wrangling for Demos: Turning Messy Client Data into Something Shippable

Part 4 of the Python for FDE track. Last updated: October 2026.

Every FDE has a war story about client data. Mine involved three "final" spreadsheets, dates in four formats, and a currency column that mixed dollars, euros, and the word "TBD". Data wrangling — reading, cleaning, and merging messy data — is the unglamorous skill that decides whether your demo survives contact with reality. This post builds the full cleaning pipeline with pandas and the "demo data" mindset: the data doesn't need to be perfect, it needs to be shippable.

Reading everything the client sends you

Clients send CSVs, Excel workbooks, and JSON dumps. Read them all with explicit dtypes — IDs must stay strings (leading zeros!), dates must parse as dates:

import pandas as pd

orders = pd.read_csv("orders.csv", dtype={"order_id": str}, parse_dates=["order_date"])
catalog = pd.read_excel("catalog.xlsx", sheet_name="SKUs")
events = pd.read_json("events.json")          # list-of-dicts JSON
print(orders.shape, catalog.shape, events.shape)
# (1240, 8) (312, 5) (9041, 4)

Inspect before you touch

Never clean blind. Five lines of inspection tell you where the bodies are buried — wrong dtypes, missing values, duplicate keys:

print(orders.dtypes)                          # is total really a number?
print(orders.isna().sum())                    # missing values per column
print(orders["status"].value_counts(dropna=False).head())
dupes = orders.duplicated(subset=["order_id"]).sum()
print("duplicate order_ids:", dupes)
# duplicate order_ids: 9

Cleaning: strings and numbers

The three eternal messes: inconsistent capitalization, currency symbols inside numeric columns, and spelling variants of the same status. Fix them with vectorized string ops — no Python loops:

df = orders.copy()
df["customer"] = df["customer"].str.strip().str.title()
df["total"] = (df["total"].astype(str)
                 .str.replace(r"[\$,]", "", regex=True)   # "$1,240.00" -> "1240.00"
                 .astype(float))
df["status"] = df["status"].str.lower().replace({"cancelled": "canceled"})
df = df.dropna(subset=["order_id", "total"])             # can't demo without these
print(df.shape)
# (1231, 8)

Cleaning: dates and duplicates

Coerce bad dates to NaT instead of crashing, drop the unparseable, dedupe keeping the latest record, and derive a month column your dashboard will group by:

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df = df.dropna(subset=["order_date"])
df = df.sort_values("order_date").drop_duplicates("order_id", keep="last")
df["month"] = df["order_date"].dt.to_period("M")
print(df["month"].value_counts().head(3))
# 2026-08    214
# 2026-09    208
# 2026-07    197

Merging: join client data to reference data

Demos get interesting when two datasets meet — orders joined to the product catalog. Normalize the join keys before merging (case and whitespace mismatches are the #1 silent merge killer), and use indicator=True so you can see exactly which rows failed to match:

catalog["sku"] = catalog["sku"].astype(str).str.strip().str.upper()
df["sku"] = df["sku"].astype(str).str.strip().str.upper()

merged = df.merge(catalog[["sku", "category", "list_price"]],
                  on="sku", how="left", indicator=True)
print(merged["_merge"].value_counts())
# both          1189
# left_only       42
missing = merged.loc[merged["_merge"] == "left_only", "sku"].unique()
print("SKUs with no catalog entry:", len(missing))
# SKUs with no catalog entry: 7
merged = merged.drop(columns="_merge")

Exporting: shippable artifacts

Cleaned data is a deliverable. Write it out in the formats the demo and the client both consume — CSV for the pipeline, Excel for the stakeholder who lives in spreadsheets, JSON for the API:

merged.to_csv("demo_ready/orders_clean.csv", index=False)
merged.to_excel("demo_ready/orders_clean.xlsx", index=False, sheet_name="orders")
merged.groupby("month")[["total"]].sum().to_json(
    "demo_ready/monthly_totals.json", indent=2)
print("Shippable artifacts written to demo_ready/")
# Shippable artifacts written to demo_ready/

Key takeaways

• Inspect before you clean: dtypes, missing values, value counts, duplicate keys.
• Read with intent — dtype={"id": str} and parse_dates prevent the classic silent corruptions.
• Clean with vectorized ops: str.strip/title/lower, regex currency stripping, to_datetime(errors="coerce").
• Before any merge, normalize both join keys identically and use indicator=True to audit matches.
• The goal is shippable, not perfect — export clean artifacts the demo and the client can both use.

Next in this series: LLM Integration in Python: Adding AI Features to Client Solutions.