🧹 Week 3 – Data Cleaning & EDA · Day 17
Day 17: apply, map, Binning & New Features
⏱ 2 hours📶 Intermediate🎯 100% self-study
Day 17 / 60
✅ Today you will learn
- Vectorised maths for new columns
- map and replace for lookups
- np.where and np.select for rules
- cut and qcut for bands and quantiles
- apply (when needed) and get_dummies
🏢 In the real world
A raw order says: quantity 2, price ₹1,299, discount 10%, age 23, date 12 Oct. A useful order says: revenue ₹2,338, “Gen-Z”, “Mid-price”, “Festive”, “Big discount”. Turning the first into the second is feature creation – and good features matter more than fancy models.
Vectorised maths
Python
import pandas as pd
s = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
s["gross"] = s["quantity"] * s["unit_price"]
s["discount_amt"] = (s["gross"] * s["discount"]).round(2)
s["revenue"] = s["gross"] - s["discount_amt"]
s["gst_18"] = (s["revenue"] * 0.18).round(2)
s["days_to_diwali"] = (pd.Timestamp("2024-10-31") - s["order_date"]).dt.days
print(s[["quantity", "unit_price", "discount", "revenue", "gst_18", "days_to_diwali"]].head(4)) quantity unit_price discount revenue gst_18 days_to_diwali
0 2 1220 0.0 2440.0 439.20 304
1 1 2327 0.1 2094.3 376.97 304
2 5 525 0.0 2625.0 472.50 304
3 1 154 0.1 138.6 24.95 304
map: look-up values
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
tier = {"Delhi": "Tier-1", "Mumbai": "Tier-1", "Bengaluru": "Tier-1", "Chennai": "Tier-1",
"Hyderabad": "Tier-1", "Kolkata": "Tier-1", "Pune": "Tier-1", "Ahmedabad": "Tier-1",
"Jaipur": "Tier-2", "Lucknow": "Tier-2", "Patna": "Tier-2"}
s["city_tier"] = s["city"].map(tier)
s["prepaid"] = s["payment_mode"].map(lambda p: 0 if p == "Cash on Delivery" else 1)
print(s["city_tier"].value_counts().to_dict(), "| unmapped:", s["city_tier"].isna().sum())
print(s.groupby("city_tier")["prepaid"].mean().round(3)){'Tier-1': 1637, 'Tier-2': 363} | unmapped: 0
city_tier
Tier-1 0.805
Tier-2 0.804
Name: prepaid, dtype: float64
np.where and np.select: business rules
Python
import pandas as pd
import numpy as np
s = pd.read_csv("sales_data.csv")
s["revenue"] = s["quantity"] * s["unit_price"] * (1 - s["discount"])
s["big_discount"] = np.where(s["discount"] >= 0.2, "Yes", "No")
conds = [s["revenue"] >= 20000, s["revenue"] >= 3000, s["revenue"] >= 1000]
labels = ["Platinum", "Gold", "Silver"]
s["order_class"] = np.select(conds, labels, default="Bronze")
print(s["big_discount"].value_counts().to_dict())
print(s["order_class"].value_counts().reindex(labels + ["Bronze"])){'No': 1490, 'Yes': 510}
order_class
Platinum 42
Gold 406
Silver 893
Bronze 659
Name: count, dtype: int64
🧠 Order matters in np.select
np.select takes the first condition that is true. Put the strictest rule (≥ 20,000) first – otherwise every Platinum order would be labelled Silver.
cut and qcut: bands
pd.cut – fixed edges
- You choose the edges
- Age 18–24, 25–34, 35–44…
- Groups can have very different sizes
pd.qcut – equal-sized groups
- pandas finds the edges
- Bottom 25%, next 25%…
- Each group has about the same count
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
s["age_group"] = pd.cut(s["customer_age"], bins=[17, 24, 34, 44, 60],
labels=["Gen-Z 18-24", "25-34", "35-44", "45+"])
s["price_band"] = pd.qcut(s["unit_price"], q=4, labels=["Budget", "Value", "Mid", "Premium"])
print(s["age_group"].value_counts().sort_index())
print(s.groupby("price_band", observed=True)["unit_price"].agg(["min", "max", "count"]))age_group
Gen-Z 18-24 429
25-34 816
35-44 570
45+ 159
Name: count, dtype: int64
min max count
price_band
Budget 138 527 500
Value 528 935 500
Mid 936 1606 500
Premium 1607 59044 500
Python
import pandas as pd
import matplotlib.pyplot as plt
s = pd.read_csv("sales_data.csv")
s["age_group"] = pd.cut(s["customer_age"], bins=[17, 24, 34, 44, 60], labels=["18-24", "25-34", "35-44", "45+"])
mix = pd.crosstab(s["age_group"], s["category"], normalize="index") * 100
mix.plot(kind="barh", stacked=True, figsize=(9, 3.8), colormap="tab20c")
plt.title("What each age group buys (% of its orders)")
plt.xlabel("% of orders"); plt.legend(bbox_to_anchor=(1, 1), fontsize=8)
plt.tight_layout(); plt.show()
apply – for logic that needs several columns
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
def delivery_fee(row):
if row["payment_mode"] == "Cash on Delivery":
return 49
if row["quantity"] * row["unit_price"] >= 499:
return 0
return 29
s["delivery_fee"] = s.apply(delivery_fee, axis=1)
print(s["delivery_fee"].value_counts().to_dict(), "| fee income ₹", s["delivery_fee"].sum()){0: 1400, 49: 391, 29: 209} | fee income ₹ 25220
⚠️ apply is a loop
apply(axis=1) calls Python once per row – fine for thousands of rows, slow for millions. Prefer vectorised maths, map, np.where or np.select whenever possible.
get_dummies: categories to numbers
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
small = s[["order_id", "payment_mode", "region"]].head(4)
print(pd.get_dummies(small, columns=["payment_mode", "region"], dtype=int)) order_id payment_mode_UPI region_North region_West
0 BK100359 1 1 0
1 BK100422 1 0 1
2 BK100744 1 0 1
3 BK100830 1 0 1
Real example: customer value score per city
Python
import pandas as pd
s = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
s["revenue"] = s["quantity"] * s["unit_price"] * (1 - s["discount"])
c = s.groupby("city").agg(last_order=("order_date", "max"), orders=("order_id", "count"),
revenue=("revenue", "sum"))
c["recency_days"] = (pd.Timestamp("2025-01-01") - c["last_order"]).dt.days
c["F"] = pd.qcut(c["orders"], 3, labels=[1, 2, 3]).astype(int)
c["M"] = pd.qcut(c["revenue"], 3, labels=[1, 2, 3]).astype(int)
c["score"] = c["F"] + c["M"]
print(c.sort_values("score", ascending=False)[["orders", "revenue", "F", "M", "score"]].round(0)) orders revenue F M score
city
Bengaluru 299 964020.0 3 3 6
Mumbai 252 1043230.0 3 3 6
Delhi 271 945592.0 3 3 6
Kolkata 193 839885.0 2 3 5
Hyderabad 200 572190.0 3 2 5
Chennai 155 545479.0 2 2 4
Pune 143 403766.0 2 1 3
Patna 126 459941.0 1 2 3
Ahmedabad 124 450612.0 1 1 2
Jaipur 106 260155.0 1 1 2
Lucknow 131 394912.0 1 1 2
🏅 Job skills unlocked today
✔ Build features with vectorised maths✔ Encode rules with np.where / np.select✔ Create bands with cut and qcut✔ Know when apply is acceptable
Practice tasks
🏋️ Practice – do it yourself
Task 1: In the house data, create
price_per_sqft (₹) and a size_band using cut: Compact (<800), Standard (800–1400), Large (>1400). Average price per sqft per band?Show solution
Python
import pandas as pd
h = pd.read_csv("house_prices.csv")
h["price_per_sqft"] = h["price_lakh"] * 100000 / h["area_sqft"]
h["size_band"] = pd.cut(h["area_sqft"], [0, 800, 1400, 10000], labels=["Compact", "Standard", "Large"])
print(h.groupby("size_band", observed=True)["price_per_sqft"].mean().round())size_band
Compact 4820.0
Standard 5035.0
Large 4809.0
Name: price_per_sqft, dtype: float64
Task 2: In telecom data, create
tenure_group (0–12, 13–24, 25–48, 49–72 months) and show the churn rate per group.Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
t["tenure_group"] = pd.cut(t["tenure_months"], [0, 12, 24, 48, 72], labels=["0-12", "13-24", "25-48", "49-72"])
print((t.groupby("tenure_group", observed=True)["churn"].apply(lambda c: (c == "Yes").mean()) * 100).round(1))tenure_group
0-12 45.1
13-24 37.1
25-48 29.1
49-72 16.5
Name: churn, dtype: float64
Task 3: For students, use np.select to give grades: A (≥85), B (≥70), C (≥55), D otherwise. Count each grade.
Show solution
Python
import pandas as pd, numpy as np
st = pd.read_csv("students_performance.csv")
st["grade"] = np.select([st["average"] >= 85, st["average"] >= 70, st["average"] >= 55], ["A", "B", "C"], default="D")
print(st["grade"].value_counts().sort_index())grade
A 22
B 152
C 252
D 74
Name: count, dtype: int64
Quick quiz
🧠 Quick Quiz – test yourself
1. Fastest way to compute revenue = qty × price?
Vectorised maths works on whole columns at C speed.
2. A value missing from the map dictionary becomes…
Check unmapped values with isna().sum().
3. qcut(x, 4) creates groups with…
Quartiles – each holds ~25% of rows.
4. np.select picks…
Order rules from strictest to loosest.
5. get_dummies turns a column with 4 categories into…
One column per category (drop_first=True gives 3).
Interview corner
💼 Interview Corner
What is feature engineering and why does it matter?
Creating new input columns from raw data using domain knowledge – ratios, bands, flags, date parts, aggregates. Models only see what you give them; a good feature like “days since last order” often improves accuracy more than switching algorithms.
map vs apply vs replace?
map transforms each value of a Series using a dict/function (unmapped → NaN). replace swaps only listed values and keeps others. apply runs a function on each value or each row/column – most flexible but slowest.
Summary
- Vectorised maths first; it is fast and readable.
- map for look-ups, np.where for yes/no, np.select for many rules.
- cut = your edges; qcut = equal-sized groups.
- apply only when the logic truly needs a Python function.
- get_dummies turns categories into 0/1 columns for ML.
➡️ Next
Day 18: Exploratory Data Analysis – a repeatable process to understand any new dataset.
Frequently Asked Questions
How many features should I create?
Start with features you can explain to a business person. More is not always better – useless features add noise. Day 44 covers feature selection.
Why did cut give NaN for some rows?
The value was outside the bins (e.g. age 17 with bins starting at 17 – the left edge is excluded). Widen the edges or use include_lowest=True.
