pandas দিয়ে ডেটা পরিষ্কার

messy = """ Order ID,Customer ,Order Date,City,Product,Qty,Amount (BDT),Status
1001,1,2026-01-12,Dhaka,Python Crash Course,1,"৳1,200",Delivered
1002,2,20/01/2026,chattogram,Mechanical Keyboard,1,4500,delivered
1003,1,2026-02-03, Dhaka ,SQL Masterclass,1,3000,DELIVERED
1004,3,2026-02-14,DHAKA,Laptop Stand,1,1500,delivered
1005,4,25/02/2026,Sylhet,27-inch Monitor,1,18500,cancelled
1006,2,2026-03-08,Ctg,AI Engineering Bootcamp,1,15000,delivered
1007,5,2026-03-15,,Wireless Mouse,2,1800,delivered
1008,3,2026-03-28,Dhaka,Python Crash Course,2,2400,Delivered
1008,3,2026-03-28,Dhaka,Python Crash Course,2,2400,Delivered
1009,6,2026-04-02,Khulna,Mechanical Keyboard,1,4050,delivered
1010,1,2026-04-19,Dhaka,27-inch Monitor,1,185000,delivered
1011,7,2026-05-06,Dhaka,SQL Masterclass,1,N/A,delivered
1012,2,2026-05-21,Chittagong,Laptop Stand,2,3000,shipped
1013,6,2026-06-02,khulna,AI Engineering Bootcamp,1,15000,Pending
1014,3,2026-06-09,Dhaka,USB-C Hub,-1,-2200,delivered
1015,,2026-06-11,Dhaka,Wireless Mouse,1,900,delivered
1016,9,2026-06-12,Rajshahi,Gift Card,1,1000,canceled
"""
with open("orders_messy.csv", "w", encoding="utf-8") as f:
    f.write(messy)
print(len(messy.splitlines()) - 1, "data rows written")

17 data rows written

import pandas as pd

raw = pd.read_csv("orders_messy.csv")
print(raw.shape)
raw.info()

(17, 8)
<class 'pandas.DataFrame'>
RangeIndex: 17 entries, 0 to 16
Data columns (total 8 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0    Order ID     17 non-null     int64  
 1   Customer      16 non-null     float64
 2   Order Date    17 non-null     str    
 3   City          16 non-null     str    
 4   Product       17 non-null     str    
 5   Qty           17 non-null     int64  
 6   Amount (BDT)  16 non-null     str    
 7   Status        17 non-null     str    
dtypes: float64(1), int64(2), str(5)
memory usage: 1.9 KB

print(raw.describe().round(2))
print(raw["Status"].value_counts())

        Order ID  Customer     Qty
count      17.00      16.00  17.00
mean     1008.47       3.62   1.12
std         4.61       2.36   0.70
min      1001.00       1.00  -1.00
25%      1005.00       2.00   1.00
50%      1008.00       3.00   1.00
75%      1012.00       5.25   1.00
max      1016.00       9.00   2.00
Status
delivered    9
Delivered    3
DELIVERED    1
cancelled    1
shipped      1
Pending      1
canceled     1
Name: count, dtype: int64

df = pd.read_csv("orders_messy.csv", dtype=str, keep_default_na=False)   # সব লেখা হিসেবে, কোনো আন্দাজ নয়

df.columns = (
    df.columns.str.strip()
    .str.lower()
    .str.replace(r"[^a-z0-9]+", "_", regex=True)     # বাকি যেকোনো কিছু হয়ে যায় _
    .str.strip("_")
)
df = df.rename(columns={"customer": "customer_id", "amount_bdt": "amount_text"})
print(list(df.columns))

['order_id', 'customer_id', 'order_date', 'city', 'product', 'qty', 'amount_text', 'status']

for col in ["city", "product", "status"]:
    df[col] = df[col].str.strip()                      # " Dhaka " -> "Dhaka"

print(sorted(df["city"].unique()))
df["city"] = df["city"].str.title().replace({
    "Ctg": "Chattogram", "Chittagong": "Chattogram",   # প্রতি শহরের একটাই নাম
    "": None,                                          # খালি লেখা মানে অনুপস্থিত
})
df["status"] = df["status"].str.lower().replace({"canceled": "cancelled"})

print(df["city"].value_counts(dropna=False).sort_index())
print(sorted(df["status"].unique()))

['', 'Chittagong', 'Ctg', 'DHAKA', 'Dhaka', 'Khulna', 'Rajshahi', 'Sylhet', 'chattogram', 'khulna']
city
Chattogram    3
Dhaka         9
Khulna        2
Rajshahi      1
Sylhet        1
None          1
Name: count, dtype: int64
['cancelled', 'delivered', 'pending', 'shipped']

amount = df["amount_text"].str.replace("৳", "").str.replace(",", "")
df["amount"] = pd.to_numeric(amount, errors="coerce")           # "N/A" -> এররের বদলে NaN
df["qty"] = pd.to_numeric(df["qty"], errors="coerce").astype("Int64")
df["customer_id"] = pd.to_numeric(df["customer_id"], errors="coerce").astype("Int64")

print(df.loc[df["amount"].isna(), ["order_id", "amount_text"]])
print(df[["customer_id", "qty", "amount"]].dtypes)

   order_id amount_text
11     1011         N/A
customer_id      Int64
qty              Int64
amount         float64
dtype: object

iso = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
df["order_date"] = iso.fillna(dmy)

print(df["order_date"].isna().sum(), "dates could not be parsed")
print(df.loc[[1, 4], ["order_id", "order_date"]])

0 dates could not be parsed
  order_id order_date
1     1002 2026-01-20
4     1005 2026-02-25

print(df.duplicated().sum(), "exact duplicate row(s)")
print(df["order_id"].duplicated().sum(), "repeated order_id(s)")
df = df.drop_duplicates().reset_index(drop=True)
assert df["order_id"].is_unique
print(len(df), "rows left")

1 exact duplicate row(s)
1 repeated order_id(s)
16 rows left

print(df[["customer_id", "city", "amount"]].isna().sum())

customer_id    1
city           1
amount         1
dtype: int64

import sqlite3

con = sqlite3.connect("shop.db")
customers = pd.read_sql("SELECT customer_id, city AS home_city FROM customers", con)
catalogue = pd.read_sql("SELECT name AS product, price AS list_price FROM products", con)
customers["customer_id"] = customers["customer_id"].astype("Int64")   # দুই দিকে একই dtype

df = df.merge(customers, on="customer_id", how="left", validate="many_to_one", indicator=True)
df = df.merge(catalogue, on="product", how="left", validate="many_to_one")
print(df["_merge"].value_counts())
print(df.loc[df["_merge"] == "left_only", ["order_id", "customer_id"]])

df["city"] = df["city"].fillna(df["home_city"]).fillna("Unknown")
df["amount_imputed"] = df["amount"].isna()
df["amount"] = df["amount"].fillna(df["qty"] * df["list_price"])
print(df.loc[df["order_id"].isin(["1007", "1011"]), ["order_id", "city", "amount", "amount_imputed"]])

_merge
both          14
left_only      2
right_only     0
Name: count, dtype: int64
   order_id  customer_id
14     1015         <NA>
15     1016            9
   order_id     city  amount  amount_imputed
6      1007  Unknown  1800.0           False
10     1011    Dhaka  3000.0            True

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
low, high = q1 - 1.5 * iqr, q3 + 1.5 * iqr
print(f"Q1={q1:.0f} Q3={q3:.0f} IQR={iqr:.0f} -> normal range {low:.0f} to {high:.0f}")
print(df.loc[(df["amount"] < low) | (df["amount"] > high), ["order_id", "product", "amount"]])

Q1=1425 Q3=7125 IQR=5700 -> normal range -7125 to 15675
  order_id          product    amount
4     1005  27-inch Monitor   18500.0
9     1010  27-inch Monitor  185000.0

df["price_ratio"] = (df["amount"] / df["qty"] / df["list_price"]).round(2)
checks = {
    "no customer": df["_merge"] == "left_only",
    "qty must be positive": ~(df["qty"] > 0).fillna(False),
    "price far from list": (df["price_ratio"] < 0.5) | (df["price_ratio"] > 1.5),
}
df["problem"] = None
for reason, bad in checks.items():
    df.loc[bad & df["problem"].isna(), "problem"] = reason     # প্রথম পাওয়া কারণটাই রাখা
print(df.loc[df["problem"].notna(), ["order_id", "qty", "amount", "price_ratio", "problem"]])

   order_id  qty    amount  price_ratio               problem
9      1010    1  185000.0         10.0   price far from list
13     1014   -1   -2200.0          1.0  qty must be positive
14     1015    1     900.0          1.0           no customer
15     1016    1    1000.0          1.0           no customer

keep = ["order_id", "customer_id", "order_date", "city", "product", "qty", "amount", "amount_imputed", "status"]
rejected = df[df["problem"].notna()]
clean = df.loc[df["problem"].isna(), keep].reset_index(drop=True)

assert clean["order_id"].is_unique
assert clean[["customer_id", "order_date", "amount"]].notna().all().all()
assert (clean["qty"] > 0).all() and (clean["amount"] > 0).all()
assert set(clean["status"]) <= {"pending", "shipped", "delivered", "cancelled"}

print(len(clean), "clean rows,", len(rejected), "rejected")
clean.to_csv("orders_clean.csv", index=False)
rejected[["order_id", "problem"]].to_csv("orders_rejected.csv", index=False)
clean.to_excel("orders_clean.xlsx", index=False, sheet_name="orders")
print(pd.read_excel("orders_clean.xlsx", sheet_name="orders").shape)

12 clean rows, 4 rejected
(12, 9)

sold = clean[clean["status"] != "cancelled"]
print(sold.groupby("city")["amount"].agg(["count", "sum"]).sort_values("sum", ascending=False))

sold = sold.assign(month=sold["order_date"].dt.strftime("%Y-%m"))
wide = sold.pivot_table(index="month", columns="city", values="amount", aggfunc="sum", fill_value=0)
print(wide)
long = wide.reset_index().melt(id_vars="month", var_name="city", value_name="revenue")
print(long[long["revenue"] > 0].sort_values(["month", "city"]).head(4))

            count      sum
city                      
Chattogram      3  22500.0
Khulna          2  19050.0
Dhaka           5  11100.0
Unknown         1   1800.0
city     Chattogram   Dhaka   Khulna  Unknown
month                                        
2026-01      4500.0  1200.0      0.0      0.0
2026-02         0.0  4500.0      0.0      0.0
2026-03     15000.0  2400.0      0.0   1800.0
2026-04         0.0     0.0   4050.0      0.0
2026-05      3000.0  3000.0      0.0      0.0
2026-06         0.0     0.0  15000.0      0.0
     month        city  revenue
0  2026-01  Chattogram   4500.0
6  2026-01       Dhaka   1200.0
7  2026-02       Dhaka   4500.0
2  2026-03  Chattogram  15000.0

CITY_MAP = {"Ctg": "Chattogram", "Chittagong": "Chattogram", "": None}
STATUS_MAP = {"canceled": "cancelled"}

def clean_orders(path, customers, catalogue):
    """কাঁচা অর্ডার CSV থেকে (clean, rejected) DataFrame ফেরত দেয়।"""
    df = pd.read_csv(path, dtype=str, keep_default_na=False)
    df.columns = (df.columns.str.strip().str.lower()
                  .str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_"))
    df = df.rename(columns={"customer": "customer_id", "amount_bdt": "amount_text"})
    df = df.apply(lambda s: s.str.strip())
    df["city"] = df["city"].str.title().replace(CITY_MAP)
    df["status"] = df["status"].str.lower().replace(STATUS_MAP)
    df["amount"] = pd.to_numeric(df["amount_text"].str.replace("৳", "").str.replace(",", ""), errors="coerce")
    for col in ["qty", "customer_id"]:
        df[col] = pd.to_numeric(df[col], errors="coerce").astype("Int64")
    df["order_date"] = (pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
                        .fillna(pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")))
    df = df.drop_duplicates().reset_index(drop=True)

    df = df.merge(customers, on="customer_id", how="left", validate="many_to_one", indicator=True)
    df = df.merge(catalogue, on="product", how="left", validate="many_to_one")
    df["city"] = df["city"].fillna(df["home_city"]).fillna("Unknown")
    df["amount_imputed"] = df["amount"].isna()
    df["amount"] = df["amount"].fillna(df["qty"] * df["list_price"])
    ratio = df["amount"] / df["qty"] / df["list_price"]

    df["problem"] = None
    for reason, bad in {
        "duplicate order_id": df["order_id"].duplicated(keep=False),
        "no customer": df["_merge"] == "left_only",
        "bad date": df["order_date"].isna(),
        "unknown product": df["list_price"].isna(),
        "qty must be positive": ~(df["qty"] > 0).fillna(False),
        "price far from list": (ratio < 0.5) | (ratio > 1.5),
    }.items():
        df.loc[bad & df["problem"].isna(), "problem"] = reason
    good = df["problem"].isna()
    return df.loc[good, keep].reset_index(drop=True), df.loc[~good, ["order_id", "problem"]]

clean2, rejected2 = clean_orders("orders_messy.csv", customers, catalogue)
print(len(clean2), "clean,", len(rejected2), "rejected")
print(rejected2.to_string(index=False))
print("same result as the step-by-step version:", clean2.equals(clean))

12 clean, 4 rejected
order_id              problem
    1010  price far from list
    1014 qty must be positive
    1015          no customer
    1016          no customer
same result as the step-by-step version: True

# ১. সহজ
spend = (clean[clean["status"] != "cancelled"]
         .groupby("customer_id")["amount"].sum()
         .sort_values(ascending=False))
print(spend)

# ২. মাঝারি
joined = pd.read_sql("SELECT customer_id, joined_on FROM customers", con, parse_dates=["joined_on"])
joined["customer_id"] = joined["customer_id"].astype("Int64")
checked = clean2.merge(joined, on="customer_id", how="left", validate="many_to_one")
too_early = checked[checked["order_date"] < checked["joined_on"]]
print(too_early["order_id"].tolist() or "no order is dated before its customer joined")

# ৩. কঠিন
def quality_report(df):
    return pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "missing": df.isna().sum(),
        "distinct": df.nunique(),
        "most_common": df.mode().iloc[0],
    })

print(quality_report(raw))
print(quality_report(clean[["city", "status", "amount"]]))