Project: Sales Data EDA

import pandas as pd

raw = pd.read_csv("sales.csv")
print(raw.shape)
print("Duplicate rows:", raw.duplicated().sum())
print("Missing values:", raw.isna().sum()[lambda s: s > 0].to_dict())
print("City spellings:", raw["city"].nunique(), "| payment spellings:", raw["payment_mode"].nunique())
print("Quantity range:", raw["quantity"].min(), "to", raw["quantity"].max())
print(raw.nlargest(2, "quantity")[["order_id", "product", "quantity", "payment_mode"]])

import pandas as pd

PAY = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery", "cash on delivery": "Cash on Delivery",
       "netbanking": "Net Banking", "net banking": "Net Banking", "wallet": "Wallet"}

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()    # exported twice
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(PAY).fillna("Unknown")
    df.loc[df["quantity"] < 1, "quantity"] = None                           # impossible value
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1                    # confirmed typo: 120 -> 1
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    df["month"] = df["order_date"].dt.month
    df["weekend"] = df["order_date"].dt.dayofweek >= 5
    return df

sales = load_sales()
assert sales["order_id"].is_unique and sales["city"].nunique() == 10
print(sales.shape)
print(sales["payment_mode"].value_counts())

kpi = {
    "orders": len(sales),
    "items sold": int(sales["quantity"].sum()),
    "revenue (₹ lakh)": round(sales["revenue"].sum() / 1e5, 2),
    "mean order (₹)": round(sales["revenue"].mean()),
    "median order (₹)": round(sales["revenue"].median()),
}
for name, value in kpi.items():
    print(f"{name:<18}{value:>10}")

import pandas as pd
import matplotlib.pyplot as plt

PAY = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery", "cash on delivery": "Cash on Delivery",
       "netbanking": "Net Banking", "net banking": "Net Banking", "wallet": "Wallet"}

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(PAY).fillna("Unknown")
    df.loc[df["quantity"] < 1, "quantity"] = None
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    df["month"] = df["order_date"].dt.month
    df["weekend"] = df["order_date"].dt.dayofweek >= 5
    return df

sales = load_sales()
monthly = sales.groupby("month").agg(orders=("order_id", "count"), revenue=("revenue", "sum"))
monthly["revenue"] = (monthly["revenue"] / 1e5).round(2)
print(monthly.T)

festive = monthly.loc[[10, 11], "revenue"].sum() / monthly["revenue"].sum()
print(f"October + November share of revenue: {festive:.1%}")

days = sales.groupby(sales["order_date"].dt.dayofweek)["order_id"].count()
days.index = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
print((days / 52).round(2).to_dict())

names = ["J", "F", "M", "A", "M", "J", "J", "A", "S", "O", "N", "D"]
colours = ["#c2410c" if m in (10, 11) else "#cbd5e1" for m in monthly.index]
fig, ax = plt.subplots(figsize=(8, 3.5))
ax.bar(names, monthly["revenue"], color=colours)
ax.set_title("Festive months bring in over a quarter of the year's revenue", loc="left")
ax.set_ylabel("Revenue (₹ lakh)")
plt.show()

import pandas as pd
import matplotlib.pyplot as plt

PAY = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery", "cash on delivery": "Cash on Delivery",
       "netbanking": "Net Banking", "net banking": "Net Banking", "wallet": "Wallet"}

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(PAY).fillna("Unknown")
    df.loc[df["quantity"] < 1, "quantity"] = None
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    df["month"] = df["order_date"].dt.month
    return df

sales = load_sales()
cat = sales.groupby("category").agg(orders=("order_id", "count"), revenue=("revenue", "sum"),
                                    median_order=("revenue", "median"))
cat["share_pct"] = (cat["revenue"] / cat["revenue"].sum() * 100).round(1)
print(cat.sort_values("revenue", ascending=False).round(0))

top = sales.groupby("product")["revenue"].sum().nlargest(5) / 1000
print(top.round(1))

season = sales.pivot_table(index="month", columns="category", values="order_id", aggfunc="count")
print(season.idxmax())

fig, ax = plt.subplots(figsize=(7, 3.5))
top.sort_values().plot.barh(ax=ax, color="#2563eb")
ax.set_title("Top 5 products by revenue (₹ thousand)", loc="left")
ax.set_ylabel("")
plt.show()

import pandas as pd

PAY = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery", "cash on delivery": "Cash on Delivery",
       "netbanking": "Net Banking", "net banking": "Net Banking", "wallet": "Wallet"}

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(PAY).fillna("Unknown")
    df.loc[df["quantity"] < 1, "quantity"] = None
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    return df

sales = load_sales()
city = sales.groupby("city").agg(orders=("order_id", "count"), revenue=("revenue", "sum"),
                                 median_order=("revenue", "median"))
city["cod_pct"] = (sales.assign(cod=sales["payment_mode"] == "Cash on Delivery")
                   .groupby("city")["cod"].mean() * 100).round(1)
print(city.sort_values("revenue", ascending=False).round(0))

pay = pd.crosstab(sales["payment_mode"], sales["revenue"] > 3000, normalize="columns").round(2)
pay.columns = ["up to ₹3,000", "above ₹3,000"]
print(pay)

import pandas as pd
from scipy import stats

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()
    df.loc[df["quantity"] < 1, "quantity"] = None
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1
    return df.dropna(subset=["quantity"])

sales = load_sales()
sales = sales[sales["quantity"] < 50]                 # leave out the one bulk order
print(sales.groupby("discount")["quantity"].agg(["count", "mean"]).round(2))

with_disc = sales.loc[sales["discount"] > 0, "quantity"]
no_disc = sales.loc[sales["discount"] == 0, "quantity"]
res = stats.mannwhitneyu(with_disc, no_disc, alternative="two-sided")
print(f"Mean items: discounted {with_disc.mean():.2f}, full price {no_disc.mean():.2f}, p = {res.pvalue:.2f}")

rho = stats.spearmanr(sales["discount"], sales["quantity"])
print(f"Spearman rho = {rho.statistic:.3f}, p = {rho.pvalue:.2f}")

import pandas as pd
import matplotlib.pyplot as plt

PAY = {"upi": "UPI", "card": "Card", "cod": "Cash on Delivery", "cash on delivery": "Cash on Delivery",
       "netbanking": "Net Banking", "net banking": "Net Banking", "wallet": "Wallet"}

def load_sales(path="sales.csv"):
    df = pd.read_csv(path, parse_dates=["order_date"]).drop_duplicates()
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = df["payment_mode"].str.strip().str.lower().map(PAY).fillna("Unknown")
    df.loc[df["quantity"] < 1, "quantity"] = None
    df.loc[df["order_id"] == "ORD11014", "quantity"] = 1
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    return df

sales = load_sales()
weekly = sales.set_index("order_date")["revenue"].resample("W").sum() / 1000
cat = (sales.groupby("category")["revenue"].sum() / 1e5).sort_values()
city = (sales.groupby("city")["revenue"].sum() / 1e5).sort_values()
pay = sales["payment_mode"].value_counts(normalize=True).sort_values() * 100

fig, axes = plt.subplots(2, 2, figsize=(11, 7), layout="constrained")
axes[0, 0].plot(weekly.index, weekly.values, color="#c2410c")
axes[0, 0].set_title("Weekly revenue peaks in Oct-Nov (₹ thousand)", loc="left")
axes[0, 1].barh(cat.index, cat.values, color="#2563eb")
axes[0, 1].set_title("Three categories earn most (₹ lakh)", loc="left")
axes[1, 0].barh(city.index, city.values, color="#64748b")
axes[1, 0].set_title("Mumbai and Delhi lead (₹ lakh)", loc="left")
axes[1, 1].barh(pay.index, pay.values, color="#0891b2")
axes[1, 1].set_title("Half of all orders are paid by UPI (%)", loc="left")
for ax in axes.flat:
    for side in ["top", "right"]:
        ax.spines[side].set_visible(False)
fig.suptitle("Online store, 2025 at a glance", fontsize=15)
fig.autofmt_xdate()
plt.show()

print(f"UPI share: {pay['UPI']:.1f}%")

Online store: 2025 sales review

1. Revenue was about ₹18.6 lakh from 1,209 orders; the typical (median) order is about ₹1,100.
2. October and November brought in 27% of the year's revenue. Order festive stock by September
   and add packing staff for October, November and weekends.
3. Fashion, Electronics and Home & Kitchen earn almost 80% of revenue. Each category
   peaks at a different time of year: plan promotions per category.
4. Mumbai and Delhi lead because of order volume, not order size. Cash on delivery is used more
   in Lucknow, Kochi and Jaipur: test a small UPI incentive there.
5. Bigger discounts were not linked to bigger baskets. Test coupons with a controlled experiment
   before increasing discount spend.

Data notes: 8 duplicate rows removed; city and payment spellings standardised; one negative
quantity set to missing; order ORD11014 corrected from 120 to 1 after checking; 7 orders
without a quantity excluded from revenue. Data covers one year, so seasonality is not confirmed
across years.