🧹 Week 3 – Data Cleaning & EDA · Day 20
Day 20: Revision Test: Python + Pandas
⏱ 2–3 hours📶 Test🎯 100% self-study
Day 20 / 60
✅ Today you will learn
- Solve 15 tasks without looking at solutions
- Score yourself honestly (1 point each)
- Revise weak topics from Days 10–19
- Practise the style of data-analyst take-home tests
🏢 In the real world
Many companies send a 2-hour “take-home” test before the interview: a CSV and ten questions. This page is that test. Set a timer, open a notebook and try each task first – then open the solution.
How to use this test
- Download the datasets (Download button in the sidebar).
- Spend at most 10 minutes per task.
- Write your answer, then open “Show solution”.
- Give yourself 1 point for a correct result, ½ if close.
- 12+ = job-ready, 8–11 = revise weak days, below 8 = repeat Days 10–19.
| Tasks | Topic | Revise |
|---|---|---|
| 1–3 | Reading, selecting, filtering | Days 11–12 |
| 4–6 | GroupBy, pivot, crosstab | Day 13 |
| 7–8 | Merge | Day 14 |
| 9–10 | Cleaning | Day 15 |
| 11–12 | Dates and text | Day 16 |
| 13–15 | Features and EDA | Days 17–19 |
💡 Before you start
Import once at the top: import pandas as pd, numpy as np. Read each file once into a variable and use .copy() before changing it.
🏅 Job skills unlocked today
✔ Solve take-home style pandas tasks✔ Self-assess and find weak topics✔ Work under time pressure
Practice tasks
🏋️ Practice – do it yourself
Task 1: Task 1. How many orders in
sales_data.csv came from the South region with quantity ≥ 3?Show solution
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
print(len(s[(s["region"] == "South") & (s["quantity"] >= 3)]))143
Task 2: Task 2. Show order_id, product and unit_price of the 5 most expensive Fashion items.
Show solution
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
print(s[s["category"] == "Fashion"].nlargest(5, "unit_price")[["order_id", "product", "unit_price"]].to_string(index=False))order_id product unit_price
BK101817 Sneakers 2690
BK101974 Sneakers 2690
BK101532 Sneakers 2687
BK101679 Sneakers 2682
BK100119 Sneakers 2672
Task 3: Task 3. What percentage of orders used UPI in each city? Sort high to low.
Show solution
Python
import pandas as pd
s = pd.read_csv("sales_data.csv")
print((s.groupby("city")["payment_mode"].apply(lambda p: (p == "UPI").mean()) * 100).round(1).sort_values(ascending=False))city
Kolkata 55.4
Jaipur 51.9
Lucknow 51.1
Pune 49.0
Bengaluru 48.5
Chennai 48.4
Ahmedabad 47.6
Mumbai 47.2
Delhi 44.6
Hyderabad 44.0
Patna 43.7
Name: payment_mode, dtype: float64
Task 4: Task 4. Revenue (qty × price × (1 − discount)) per category and quarter as a pivot table in ₹ lakh.
Show solution
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"])
print((s.pivot_table(index="category", columns=s["order_date"].dt.quarter, values="revenue", aggfunc="sum") / 1e5).round(2))order_date 1 2 3 4
category
Books & Stationery 0.74 0.45 0.83 1.08
Electronics 10.48 7.46 7.07 13.38
Fashion 4.19 2.80 3.03 5.14
Grocery 1.73 1.85 1.95 2.37
Home & Kitchen 1.04 0.86 0.92 1.43
Task 5: Task 5. In the telecom data, find the churn rate for each payment method and the number of customers.
Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
t["churned"] = t["churn"].eq("Yes")
r = t.groupby("payment_method").agg(customers=("customer_id", "count"), churn_rate=("churned", "mean"))
r["churn_rate"] = (r["churn_rate"] * 100).round(1)
print(r.sort_values("churn_rate", ascending=False)) customers churn_rate
payment_method
Cash 724 32.6
UPI AutoPay 911 29.0
Bank Transfer 608 27.8
Credit Card 757 27.1
Task 6: Task 6. Crosstab of tech_support vs churn as row percentages. What do you conclude?
Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
print((pd.crosstab(t["tech_support"], t["churn"], normalize="index") * 100).round(1))
print("Customers without tech support churn far more – offer it free for 3 months.")churn No Yes
tech_support
No 62.7 37.3
No internet 86.2 13.8
Yes 74.6 25.4
Customers without tech support churn far more – offer it free for 3 months.
Task 7: Task 7. Merge employees with departments (keep all employees). How many employees have no department?
Show solution
Python
import pandas as pd
e = pd.read_csv("employees.csv"); d = pd.read_csv("departments.csv")
m = e.merge(d, on="dept_id", how="left")
print(m["dept_name"].isna().sum())2
Task 8: Task 8. Total salary cost per department location, highest first.
Show solution
Python
import pandas as pd
e = pd.read_csv("employees.csv"); d = pd.read_csv("departments.csv")
print(e.merge(d, on="dept_id").groupby("location")["salary"].sum().sort_values(ascending=False))location
Bengaluru 1164000
Mumbai 636000
Delhi 264000
Pune 79000
Name: salary, dtype: int64
Task 9: Task 9. In
sales_dirty.csv, how many rows remain after removing exact duplicates and rows with unit_price ≤ 0?Show solution
Python
import pandas as pd
d = pd.read_csv("sales_dirty.csv").drop_duplicates()
print(len(d[d["unit_price"] > 0]))292
Task 10: Task 10. In
sales_dirty.csv, standardise city names and print the number of unique cities before and after.Show solution
Python
import pandas as pd
d = pd.read_csv("sales_dirty.csv")
print(d["city"].nunique(), "->", d["city"].str.strip().str.title().nunique())26 -> 11
Task 11: Task 11. Delhi weather: in which year was the average AQI of November highest?
Show solution
Python
import pandas as pd
w = pd.read_csv("delhi_weather.csv", parse_dates=["date"])
nov = w[w["date"].dt.month == 11].groupby(w["date"].dt.year)["aqi"].mean().round(1)
print(nov); print("Highest:", nov.idxmax())date
2022 247.7
2023 250.2
2024 250.8
Name: aqi, dtype: float64
Highest: 2024
Task 12: Task 12. Product reviews: what share of reviews mentioning “money” are negative?
Show solution
Python
import pandas as pd
r = pd.read_csv("product_reviews.csv")
m = r[r["review"].str.contains("money", case=False)]
print(len(m), "reviews |", round((m["sentiment"] == "negative").mean() * 100, 1), "% negative")62 reviews | 50.0 % negative
Task 13: Task 13. Students: create study_band with qcut (4 equal groups) and show the average score per band.
Show solution
Python
import pandas as pd
st = pd.read_csv("students_performance.csv")
st["study_band"] = pd.qcut(st["study_hours"], 4, labels=["Low", "Mid-low", "Mid-high", "High"])
print(st.groupby("study_band", observed=True)["average"].mean().round(1))study_band
Low 56.4
Mid-low 63.7
Mid-high 69.4
High 77.3
Name: average, dtype: float64
Task 14: Task 14. Loan applications: approval rate (%) by employment type. (Look at the columns first.)
Show solution
Python
import pandas as pd
l = pd.read_csv("loan_applications.csv")
print(l.columns.tolist())
print((l.groupby("employment")["approved"].mean() * 100).round(1).sort_values(ascending=False))['applicant_id', 'monthly_income', 'credit_score', 'loan_amount', 'employment', 'years_employed', 'approved']
employment
Salaried 48.7
Business 46.4
Self-Employed 40.2
Name: approved, dtype: float64
Task 15: Task 15. Houses: which city gives the most area per ₹ lakh on average? Write one sentence for a buyer.
Show solution
Python
import pandas as pd
h = pd.read_csv("house_prices.csv")
v = (h["area_sqft"] / h["price_lakh"]).groupby(h["city"]).mean().round(1).sort_values(ascending=False)
print(v)
print(f"A buyer gets about {v.iloc[0]:.0f} sqft per lakh in {v.index[0]} vs {v.iloc[-1]:.0f} in {v.index[-1]}.")city
Lucknow 30.0
Jaipur 27.2
Hyderabad 20.2
Pune 19.5
Bengaluru 17.1
dtype: float64
A buyer gets about 30 sqft per lakh in Lucknow vs 17 in Bengaluru.
Quick quiz
🧠 Quick Quiz – test yourself
1. df.groupby("a")["b"].agg(["mean", "count"]) returns…
A list of functions gives one column per function.
2. Which removes leading/trailing spaces?
pandas uses Python’s name: strip.
3. s.value_counts(normalize=True) returns…
Multiply by 100 for percentages.
4. Left merge of 30 employees with a unique-key lookup gives…
Many-to-one left join preserves row count.
5. pd.cut(x, [0, 10, 20]) for x = 0 gives…
Left edge is open; use include_lowest=True.
6. Fastest way to keep rows where city is Delhi or Pune?
isin checks membership in a list.
Interview corner
💼 Interview Corner
You get a 5 GB CSV that does not fit in memory. What do you do?
Read only needed columns (usecols), set smaller dtypes (category, int32), read in chunks (chunksize) and aggregate each chunk, or use a database / tools like DuckDB or Polars.
How do you make your pandas analysis reproducible?
Never edit raw files, put cleaning in functions, fix random seeds, record package versions (requirements.txt), and keep the notebook running top-to-bottom without errors.
Summary
- You have finished the pandas block – the core skill of every data job.
- Score 12+? Move on to visualisation. Lower? Revise the listed days.
- Keep your solutions – they become portfolio snippets.
➡️ Next
Week 4 starts! Day 21: Matplotlib – line, bar, scatter, histograms and subplots that look professional.
Frequently Asked Questions
My answer differs slightly from the solution. Is it wrong?
Not necessarily – rounding, sorting or a different but valid method can change the look. Check that the numbers match.
What next if I scored below 8?
Repeat the practice sections of the weak days, then retry this test after two days without looking at the solutions.
