ডেটা হ্যান্ডলিংয়ের মূল কথা: ফরম্যাট, মান আর উৎস

import pandas as pd

from sqlhelp import con

orders = pd.read_sql("""
    SELECT o.order_id, o.order_date, o.status, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
    GROUP BY o.order_id, o.order_date, o.status
    ORDER BY o.order_id""", con, parse_dates=["order_date"])

orders.to_csv("orders.csv", index=False)
orders.to_json("orders.json", orient="records", date_format="iso")
orders.to_excel("orders.xlsx", index=False)
orders.to_xml("orders.xml", index=False, parser="etree")
orders.to_parquet("orders.parquet", index=False)

readers = {
    "orders.csv": pd.read_csv,
    "orders.json": pd.read_json,
    "orders.xlsx": pd.read_excel,
    "orders.xml": lambda path: pd.read_xml(path, parser="etree"),
    "orders.parquet": pd.read_parquet,
}
for path, read in readers.items():
    back = read(path)
    print(f"{path:15} order_date: {str(back['order_date'].dtype):15} total: {back['total'].dtype}")

orders.csv      order_date: str             total: int64
orders.json     order_date: str             total: int64
orders.xlsx     order_date: datetime64[us]  total: int64
orders.xml      order_date: str             total: int64
orders.parquet  order_date: datetime64[us]  total: int64

raw = pd.Series(["1200", "1,500", "৳ 900", "২৫০০", "free", None])

BANGLA_DIGITS = str.maketrans("০১২৩৪৫৬৭৮৯", "0123456789")
cleaned = (raw.str.translate(BANGLA_DIGITS)
              .str.replace(r"[৳,\s]", "", regex=True))
price = pd.to_numeric(cleaned, errors="coerce")       # যা পার্স করা যায় না তা NaN হয়

print(pd.DataFrame({"raw": raw, "price": price}))

     raw   price
0   1200  1200.0
1  1,500  1500.0
2  ৳ 900   900.0
3   ২৫০০  2500.0
4   free     NaN
5    NaN     NaN

signups = pd.DataFrame({
    "email":     ["nadia@example.com", "TANVIR@example.com ", "farhana@example", None,
                  "nadia@example.com", "rafiq@example.com", "mitu@example.com",
                  "imran@example.com", "sadia@example.com"],
    "city":      ["Dhaka", "chattogram", "Dhaka ", "Sylhet", "Dhaka", "Dacca", "DHAKA",
                  "Khulna", "dhaka"],
    "age":       [29, 34, 27, 41, 29, -3, 25, 38, 22],
    "signed_up": ["2026-05-01", "2026-05-03", "2026-05-03", "2026-05-04",
                  "2026-05-01", "2026-05-06", "2026-13-01", "2026-05-08", "2026-05-09"],
})
print(signups)

                 email        city  age   signed_up
0    nadia@example.com       Dhaka   29  2026-05-01
1  TANVIR@example.com   chattogram   34  2026-05-03
2      farhana@example      Dhaka    27  2026-05-03
3                  NaN      Sylhet   41  2026-05-04
4    nadia@example.com       Dhaka   29  2026-05-01
5    rafiq@example.com       Dacca   -3  2026-05-06
6     mitu@example.com       DHAKA   25  2026-13-01
7    imran@example.com      Khulna   38  2026-05-08
8    sadia@example.com       dhaka   22  2026-05-09

EMAIL_RULE = r"^[^@\s]+@[^@\s]+\.[a-z]{2,}$"
KNOWN_CITIES = {"Dhaka", "Chattogram", "Sylhet", "Khulna", "Rajshahi"}
AS_OF = pd.Timestamp("2026-05-10")                   # রিপোর্টের তারিখ, স্থির রাখা যাতে ফলাফল বারবার একই আসে

dates = pd.to_datetime(signups["signed_up"], format="%Y-%m-%d", errors="coerce")
email_key = signups["email"].str.strip().str.lower()

report = {
    "completeness: email present": signups["email"].notna().mean(),
    "validity: email well-formed": email_key.str.match(EMAIL_RULE).fillna(False).astype(bool).mean(),
    "validity: age 0-120": signups["age"].between(0, 120).mean(),
    "validity: real date": dates.notna().mean(),
    "consistency: city in reference list": signups["city"].isin(KNOWN_CITIES).mean(),
    "uniqueness: distinct emails": email_key.dropna().nunique() / email_key.notna().sum(),
    "timeliness: signed up in last 7 days": (dates >= AS_OF - pd.Timedelta(days=7)).mean(),
}
for check, share in report.items():
    print(f"{check:38} {share:6.1%}")

completeness: email present             88.9%
validity: email well-formed             77.8%
validity: age 0-120                     88.9%
validity: real date                     88.9%
consistency: city in reference list     44.4%
uniqueness: distinct emails             87.5%
timeliness: signed up in last 7 days    66.7%

CITY_MAP = {"dhaka": "Dhaka", "dacca": "Dhaka", "chattogram": "Chattogram",
            "chittagong": "Chattogram", "sylhet": "Sylhet", "khulna": "Khulna"}


def clean(df):
    out = df.copy()
    out["email"] = out["email"].str.strip().str.lower()
    out["city"] = out["city"].str.strip().str.lower().map(CITY_MAP)
    out["signed_up"] = pd.to_datetime(out["signed_up"], format="%Y-%m-%d", errors="coerce")

    problems = pd.Series("", index=out.index)
    problems[out["email"].isna()] += "no email; "
    problems[out["email"].notna() & ~out["email"].str.match(EMAIL_RULE).fillna(False).astype(bool)] += "bad email; "
    problems[~out["age"].between(0, 120)] += "bad age; "
    problems[out["signed_up"].isna()] += "bad date; "

    quarantine = out[problems != ""].assign(reason=problems[problems != ""].str.rstrip("; "))
    good = out[problems == ""].drop_duplicates(subset="email", keep="first")
    return good, quarantine


good, quarantine = clean(signups)
print(good)
print(quarantine[["email", "reason"]])
print(f"rows in: {len(signups)}, clean: {len(good)}, quarantined: {len(quarantine)}, "
      f"duplicates removed: {len(signups) - len(good) - len(quarantine)}")

                email        city  age  signed_up
0   nadia@example.com       Dhaka   29 2026-05-01
1  tanvir@example.com  Chattogram   34 2026-05-03
7   imran@example.com      Khulna   38 2026-05-08
8   sadia@example.com       Dhaka   22 2026-05-09
               email     reason
2    farhana@example  bad email
3                NaN   no email
5  rafiq@example.com    bad age
6   mitu@example.com   bad date
rows in: 9, clean: 4, quarantined: 4, duplicates removed: 1

divisions = pd.DataFrame({"city": ["Dhaka", "Chattogram", "Sylhet", "Khulna"],
                          "division": ["Dhaka", "Chattogram", "Sylhet", "Khulna"],
                          "metro": [True, True, False, False]})

enriched = good.merge(divisions, on="city", how="left", validate="many_to_one")
assert len(enriched) == len(good), "the merge must not add or lose rows"

enriched["age_band"] = pd.cut(enriched["age"], bins=[0, 24, 34, 120], labels=["<25", "25-34", "35+"])
print(enriched[["email", "division", "metro", "age_band"]])

summary = (enriched[enriched["metro"]]
           .groupby("city", as_index=False)
           .agg(signups=("email", "count"), mean_age=("age", "mean")))
print(summary)

                email    division  metro age_band
0   nadia@example.com       Dhaka   True    25-34
1  tanvir@example.com  Chattogram   True    25-34
2   imran@example.com      Khulna  False      35+
3   sadia@example.com       Dhaka   True      <25
         city  signups  mean_age
0  Chattogram        1      34.0
1       Dhaka        2      25.5

long = pd.read_sql("""
    SELECT c.name AS category, substr(o.order_date, 1, 7) AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN products p ON p.product_id = oi.product_id
    JOIN categories c ON c.category_id = p.category_id
    WHERE o.status <> 'cancelled' AND o.order_date < '2026-04-01'
    GROUP BY category, month
    ORDER BY category, month""", con)
print(long.head(4))

wide = long.pivot(index="category", columns="month", values="revenue").fillna(0).astype(int)
print(wide)

back_to_long = wide.reset_index().melt(id_vars="category", var_name="month", value_name="revenue")
print(len(long), "long rows,", wide.shape, "wide,", len(back_to_long), "rows after melt")

      category    month  revenue
0  Accessories  2026-02     1500
1  Accessories  2026-03     2200
2        Books  2026-01     1200
3        Books  2026-02     2500
month        2026-01  2026-02  2026-03
category                              
Accessories        0     1500     2200
Books           1200     2500     2400
Courses            0     3000    15000
Electronics     5400        0     1800
9 long rows, (4, 3) wide, 12 rows after melt

import hashlib
import json


def sha256_of(path):
    return hashlib.sha256(open(path, "rb").read()).hexdigest()[:12]


signups.to_csv("signups_raw.csv", index=False)

def run_pipeline():
    raw = pd.read_csv("signups_raw.csv")
    good, quarantine = clean(raw)
    good.sort_values("email").to_csv("signups_clean.csv", index=False)
    return {
        "source": "newsletter export (signups_raw.csv)",
        "input_sha256": sha256_of("signups_raw.csv"),
        "steps": ["strip/lower email", "map city", "parse date", "validate", "dedupe on email"],
        "rows": {"in": len(raw), "clean": len(good), "quarantined": len(quarantine)},
        "output_sha256": sha256_of("signups_clean.csv"),
        "as_of": str(AS_OF.date()),
        "pandas": pd.__version__.split(".")[0] + ".x",
    }


first, second = run_pipeline(), run_pipeline()
print(json.dumps(first, indent=2))
print("same output on a second run:", first["output_sha256"] == second["output_sha256"])

{
  "source": "newsletter export (signups_raw.csv)",
  "input_sha256": "066c33963c6c",
  "steps": [
    "strip/lower email",
    "map city",
    "parse date",
    "validate",
    "dedupe on email"
  ],
  "rows": {
    "in": 9,
    "clean": 4,
    "quarantined": 4
  },
  "output_sha256": "caa2d8ed5ba9",
  "as_of": "2026-05-10",
  "pandas": "3.x"
}
same output on a second run: True

# ১. সহজ
values = pd.Series(["12", "৪৫", "3.5", "N/A", "1,200"])
numbers = pd.to_numeric(values.str.translate(BANGLA_DIGITS).str.replace(",", ""), errors="coerce")
print(numbers.tolist(), "unparsed:", numbers.isna().sum())

# ২. মাঝারি
customers = pd.read_sql("SELECT customer_id, email, city FROM customers", con)
print(f"email present:     {customers['email'].notna().mean():.0%}")
print(f"email well-formed: {customers['email'].str.match(EMAIL_RULE).mean():.0%}")
print(f"city present:      {customers['city'].notna().mean():.0%}")

# ৩. কঠিন
melted = wide.reset_index().melt(id_vars="category", var_name="month", value_name="revenue")
melted = melted[melted["revenue"] != 0].sort_values(["category", "month"]).reset_index(drop=True)
print(melted.equals(long.astype({"revenue": int})))