You’re given three messy datasets (customers.csv, orders.csv, aliases.csv) along with cryptic notes about canonicalization and duplicates. Your task is to clean, deduplicate, and integrate them properly. If you follow the correct data warehousing rules, a hidden flag is revealed: one character is embedded every 137th row of the final dataset.
aliases.csv (e.g., "e-mail", "email_address" → "email")._, as they are meaningful identifiers).created_at timestamp.order_id.version (lower versions contain decoy bytes).import pandas as pd, unicodedata, re
def load_aliases():
aliases = pd.read_csv("aliases.csv")
return dict(zip(aliases["alias"], aliases["canonical"]))
def normalize_headers(df, aliases):
norm_cols = []
for col in df.columns:
col = col.lower().strip()
col = re.sub(r"\s+", " ", col)
norm_cols.append(aliases.get(col, col))
df.columns = norm_cols
return df
def canonicalize_email(email):
if pd.isna(email): return email
email = email.lower()
email = unicodedata.normalize("NFC", email)
return email.replace(" ", "")
created_at and keep the first row per canonical key.order_id + version (descending), then keep the first per order_id.orders ↔ customers on the canonical key.region (alphabetical: East, North, South, West)order_date (chronological)order_id (alphanumeric)def process_customers(df):
df["canonical_key"] = df["email"].apply(canonicalize_email)
df["created_at"] = pd.to_datetime(df["created_at"])
df = df.sort_values(["canonical_key", "created_at"])
return df.groupby("canonical_key").first().reset_index()
def process_orders(df):
df["canonical_key"] = df["customer_key"].apply(canonicalize_email)
df["order_date"] = pd.to_datetime(df["order_date"])
df = df.sort_values(["order_id", "version"], ascending=[True, False])
return df.groupby("order_id").first().reset_index()
# Join and sort
result = pd.merge(orders_processed, customers_processed,
on="canonical_key", how="inner")
result = result.sort_values(["region", "order_date", "order_id"]).reset_index(drop=True)