# Lesson 9: pandas - cleaning messy data
import pandas as pd
df = pd.read_csv("messy_sales.csv")
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")
df["region"] = df["region"].str.strip().str.title()
df["customer"] = df["customer"].str.strip()
df["amount"] = pd.to_numeric(df["amount"].astype(str).str.replace("Rs", "").str.replace(",", "").str.strip(), errors="coerce")
df["date"] = pd.to_datetime(df["date"], format="mixed", dayfirst=True, errors="coerce")
before = len(df)
df = df.drop_duplicates()
df = df.dropna(subset=["amount", "date"])
print("Rows before:", before, "after:", len(df))
print(df["region"].value_counts().sort_index().to_string())
print("Clean total:", f"{df['amount'].sum():,.0f}")
df.to_csv("clean_sales.csv", index=False)