Data Wrangling with Pandas
The store manager pings you: "Which category makes us the most money? And which three cities should we prioritise for the Diwali campaign?" The raw orders table can't answer that as-is — you need to reshape it. This is data wrangling: selecting, filtering, grouping, and joining until the data answers the question.
By the end of this lesson you'll produce a single tidy table joining orders, customers, and products, and you'll answer two real ShopKart questions with a couple of lines each.
Selecting the columns you need
Pass a list of column names to pick a subset. This keeps your working table focused.
> Need the data? This lesson loads the ShopKart CSVs. Download orders.csv, customers.csv and products.csv into the same folder as your notebook first — Lesson 1 covers the setup.
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
cols = orders[["order_id", "category", "price", "quantity", "city"]]
cols.head(3) order_id category price quantity city
0 ORD10001 Electronics 1299.0 1 Mumbai
1 ORD10002 Fashion 2199.0 1 Mumbai
2 ORD10003 Home & Kitchen 649.0 2 MumbaiCreating a derived column
Revenue isn't stored directly — it's price quantity. Assigning to a new column name creates it. Vectorised arithmetic like this runs across the whole column at once (far faster than a Python loop).
orders["revenue"] = orders["price"] * orders["quantity"]
orders[["order_id", "price", "quantity", "revenue"]].head(3) order_id price quantity revenue
0 ORD10001 1299.0 1 1299.0
1 ORD10002 2199.0 1 2199.0
2 ORD10003 649.0 2 1298.0Filtering rows with conditions
A comparison on a column produces a boolean Series; indexing with it keeps matching rows. Combine conditions with & (and) / | (or), wrapping each in parentheses.
# High-value Electronics orders in Mumbai
mask = (orders["category"] == "Electronics") & \
(orders["city"] == "Mumbai") & \
(orders["revenue"] > 2000)
orders[mask][["order_id", "brand", "revenue"]].head() order_id brand revenue
3 ORD10004 Samsung 18999.0
12 ORD10009 Samsung 2013.0
44 ORD10029 Realme 5403.0
152 ORD10095 OnePlus 2160.0
192 ORD10120 OnePlus 4071.0.isin() is handy for matching multiple values at once:
metro = orders[orders["city"].isin(["Mumbai", "Delhi", "Bengaluru"])]
print(len(metro), "line items in the three biggest metros")7577 line items in the three biggest metrosQuestion 1: Revenue by category
This is a group-by aggregation — split the rows into groups by category, then sum revenue within each group. groupby(col)[measure].agg(...) is the core pattern of analytics.
by_category = (
orders.groupby("category")["revenue"]
.sum()
.sort_values(ascending=False)
)
by_categorycategory
Electronics 9663403.0
Home & Kitchen 3496498.0
Fashion 3133305.0
Sports 3034279.0
Beauty 1129002.0
Books 771678.0
Name: revenue, dtype: float64Electronics dominates at ₹96.6 lakh — nearly three times Home & Kitchen, the runner-up. That's the answer to the manager's first question in three lines.
You can aggregate several measures at once with .agg():
summary = orders.groupby("category").agg(
total_revenue=("revenue", "sum"),
line_items=("order_id", "count"), # rows, not distinct orders
distinct_orders=("order_id", "nunique"),
avg_price=("price", "mean"),
)
summary.sort_values("total_revenue", ascending=False) total_revenue line_items distinct_orders avg_price
category
Electronics 9663403.0 4068 3418 1861.284415
Home & Kitchen 3496498.0 2137 1942 1249.909686
Fashion 3133305.0 2429 2202 905.526554
Sports 3034279.0 1591 1504 1558.891892
Beauty 1129002.0 1480 1385 368.279054
Books 771678.0 775 750 565.285161Now you can see why Electronics wins: the highest volume and the highest average price. Sports is the mirror image — pricey items (₹1,559 average) but only 1,591 line items, so it lands fourth.
Note the two count columns. One order can contain several line items, so count on order_id counts rows while nunique counts distinct orders. Electronics shows 4,068 rows across 3,418 real orders. Picking the wrong one is a classic way to overstate order volume in a report.
Question 2: Top cities
Same pattern, grouped by city. .head(3) after sorting gives the top three for the Diwali campaign.
top_cities = (
orders.groupby("city")["revenue"]
.sum()
.sort_values(ascending=False)
.head(3)
)
top_citiescity
Mumbai 5223311.0
Delhi 4037024.0
Bengaluru 3715412.0
Name: revenue, dtype: float64Mumbai, Delhi, Bengaluru — prioritise those three. Answered.
Merging tables
Real questions span multiple tables. To ask "Do Premium customers spend more?" you need customer segment (in customers) alongside revenue (in orders). pd.merge joins on a shared key.
customers = pd.read_csv("customers.csv", parse_dates=["signup_date"])
products = pd.read_csv("products.csv")
# Join orders to customer info, then to product info
df = orders.merge(customers, on="customer_id", how="left", suffixes=("", "_cust"))
df = df.merge(products, on="product_id", how="left", suffixes=("", "_prod"))
df[["order_id", "customer_id", "segment", "category", "rating", "revenue"]].head(3) order_id customer_id segment category rating revenue
0 ORD10001 CUST042 Premium Electronics 4.2 1299.0
1 ORD10002 CUST119 Regular Fashion 4.2 2199.0
2 ORD10003 CUST042 Premium Home & Kitchen 4.4 1298.0how="left" keeps every order even if a customer or product row is missing — the safe default when orders are your source of truth. The diagram shows what each join type keeps.
Now the Premium-vs-Regular question is one group-by:
df.groupby("segment")["revenue"].agg(["sum", "mean", "count"]) sum mean count
segment
Premium 10630927.0 2223.113133 4782
Regular 10295572.0 1378.255957 7470Premium customers place fewer line items but spend about 60% more on each one (₹2,223 vs ₹1,378) — a strong signal we'll use when building features later. Their smaller count still adds up to slightly more total revenue.
Handling missing values
Recall from Lesson 1 that brand had 120 missing values. Left joins can also introduce NaNs. Always check, then decide.
df.isna().sum()[lambda s: s > 0]brand 120
segment 228
dtype: int64Watch the numbers carefully. Only 35 customers are missing a segment in customers.csv, but they show up as 228 missing rows here — because each of those customers has several orders, and the left join copies the blank segment onto every one of their rows. Missing values multiply through a join. Always check counts after merging, not just before.
Three common strategies, each appropriate in different cases:
# 1. Fill categorical gaps with a sensible placeholder
df["brand"] = df["brand"].fillna("Unknown")
# 2. Fill a segment gap with the most common value (mode)
df["segment"] = df["segment"].fillna(df["segment"].mode()[0])
# 3. Drop rows only when the missing field is essential (rare here)
# df = df.dropna(subset=["price"])# After filling:
df.isna().sum().sum() -> 0Never blindly dropna() everything — for ShopKart, dropping 120 orders would quietly understate Electronics revenue. Filling brand with "Unknown" keeps the money in the totals while flagging the gap.
Fixing dtypes
If a column arrives with the wrong type (e.g. a category read as generic text), convert it. Using pandas' category dtype for repeated string values saves memory and speeds up group-bys.
df["category"] = df["category"].astype("category")
df["segment"] = df["segment"].astype("category")
df["order_date"] = pd.to_datetime(df["order_date"])On the job
Analysts spend far more time wrangling than modelling — the industry saying "80% of data science is cleaning data" is only half a joke. The groupby → agg → sort_values pattern you just used answers a huge share of real business questions ("revenue by X", "top N Y", "average Z per segment"). And getting merges right matters: an accidental inner join that silently drops unmatched rows is one of the most common ways a dashboard ends up quietly wrong. When a total looks off, check your join type first.
Key takeaways
- Select columns with
df[[...]]; create derived columns likerevenue = price quantitywith vectorised math. - Filter with boolean masks; combine conditions using
&/|and parentheses, or.isin()for lists. groupby(col)[measure].agg(...)answers "revenue by category" and "top cities" in a few lines.pd.merge(..., how="left")joins orders + customers + products while keeping every order.- Always
isna().sum()after loading and merging; fill or drop deliberately, and set correct dtypes.