🔢 Week 2 – NumPy & Pandas · Day 12
Day 12: Selecting, Filtering & Sorting
⏱ 2 hours📶 Intermediate🎯 100% self-study
Day 12 / 60
✅ Today you will learn
- loc (labels) vs iloc (positions)
- Boolean filters with &, |, ~
- isin, between, str.contains
- query() for readable filters
- sort_values, nlargest and safe updates with loc
🏢 In the real world
A marketing team asks: “Give me all female customers aged 25–35 in metro cities who paid by UPI and gave a rating below 3.” This is a one-line pandas filter – and it decides who gets a ₹100 coupon.
loc vs iloc
df.loc[rows, cols]
- Uses index labels and column names
- Slices include the end label
- df.loc[5, "city"]
- df.loc[df.price > 100, ["city"]]
df.iloc[rows, cols]
- Uses integer positions
- Slices exclude the end
- df.iloc[0, 2]
- df.iloc[:10, :3]
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
cols = ["order_id", "city", "category", "unit_price"]
print(sales.loc[0:2, cols]) # labels 0, 1, 2 (end included)
print(sales.iloc[0:2, 0:3]) # positions 0, 1 (end excluded)
print(sales.loc[10, "product"], "|", sales.iloc[10, 5]) order_id city category unit_price
0 BK100359 Delhi Fashion 1220
1 BK100422 Mumbai Fashion 2327
2 BK100744 Mumbai Grocery 525
order_id order_date city
0 BK100359 2024-01-01 Delhi
1 BK100422 2024-01-01 Mumbai
Basmati Rice 5kg | Basmati Rice 5kg
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv").set_index("order_id")
print(sales.loc["BK100500", ["city", "product", "unit_price"]])
print(sales.loc[["BK100001", "BK100002"], "category"])city Bengaluru
product Saree
unit_price 1865
Name: BK100500, dtype: object
order_id
BK100001 Home & Kitchen
BK100002 Grocery
Name: category, dtype: object
Boolean filtering
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
big = sales[sales["unit_price"] > 10000]
print("Orders above ₹10,000:", len(big))
metro_upi = sales[(sales["city"].isin(["Delhi", "Mumbai", "Bengaluru"])) & (sales["payment_mode"] == "UPI")]
print("Metro + UPI orders:", len(metro_upi))
not_cod = sales[~(sales["payment_mode"] == "Cash on Delivery")]
print("Prepaid orders:", len(not_cod))Orders above ₹10,000: 126
Metro + UPI orders: 385
Prepaid orders: 1609
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
young = sales[sales["customer_age"].between(25, 35)]
phones = sales[sales["product"].str.contains("Phone|Watch", case=False)]
low = sales[sales["rating"] < 3]
print(len(young), "aged 25–35 |", len(phones), "phone/watch orders |", len(low), "low ratings")
print(phones["product"].value_counts())900 aged 25–35 | 181 phone/watch orders | 54 low ratings
product
Mobile Phone 104
Smart Watch 77
Name: count, dtype: int64
query() – filters you can read aloud
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
target = sales.query("customer_gender == 'Female' and 25 <= customer_age <= 35 "
"and region in ['North', 'West'] and payment_mode == 'UPI' and rating < 3")
print(len(target), "customers get the ₹100 coupon")
print(target[["order_id", "city", "customer_age", "rating"]].head())
limit = 2000
print(len(sales.query("unit_price > @limit")), "orders above", limit)2 customers get the ₹100 coupon
order_id city customer_age rating
392 BK100224 Delhi 30.0 2.5
956 BK100412 Delhi 26.0 2.5
396 orders above 2000
Sorting and top-N
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
print(sales.sort_values("revenue", ascending=False)[["order_id", "product", "revenue"]].head(3))
print(sales.sort_values(["city", "revenue"], ascending=[True, False])[["city", "revenue"]].head(4))
print(sales.nlargest(3, "revenue")[["product", "revenue"]])
print(sales.nsmallest(2, "unit_price")[["product", "unit_price"]]) order_id product revenue
789 BK100824 Laptop 114432.0
280 BK101257 Laptop 112962.0
1262 BK101612 Laptop 105231.5
city revenue
88 Ahmedabad 58065.0
1860 Ahmedabad 48208.5
1966 Ahmedabad 32676.2
830 Ahmedabad 15187.5
product revenue
789 Laptop 114432.0
280 Laptop 112962.0
1262 Laptop 105231.5
product unit_price
1580 Pen Set 138
1407 Pen Set 139
Changing values the right way
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales.loc[sales["payment_mode"] == "Cash on Delivery", "payment_mode"] = "COD" # rename a category
sales.loc[sales["unit_price"] > 50000, "segment"] = "Premium"
sales["segment"] = sales["segment"].fillna("Regular")
print(sales["payment_mode"].value_counts().to_dict())
print(sales["segment"].value_counts().to_dict()){'UPI': 961, 'Card': 485, 'COD': 391, 'Net Banking': 163}
{'Regular': 1978, 'Premium': 22}
⚠️ SettingWithCopyWarning
Writing df[df.x > 5]["y"] = 0 may change a temporary copy and do nothing. Always use one .loc: df.loc[df.x > 5, "y"] = 0. If you need a separate table, add .copy(): sub = df[df.x > 5].copy().
Real example: find unhappy high-value customers
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
vip_unhappy = (sales[(sales["revenue"] >= 5000) & (sales["rating"] <= 3)]
.sort_values("revenue", ascending=False)
[["order_id", "city", "product", "revenue", "rating"]])
print(len(vip_unhappy), "high-value orders with rating ≤ 3")
print(vip_unhappy.head(5).to_string(index=False))
vip_unhappy.to_csv("call_these_customers.csv", index=False)33 high-value orders with rating ≤ 3
order_id city product revenue rating
BK100862 Mumbai Laptop 50116.3 3.0
BK101663 Bengaluru Laptop 42331.2 3.0
BK101355 Hyderabad Mobile Phone 30249.0 3.0
BK100295 Delhi Mobile Phone 16580.0 3.0
BK101136 Kolkata Mobile Phone 16521.0 3.0
💼 Business action
The support team calls these customers first – fixing a ₹15,000 phone complaint protects far more future revenue than a ₹200 grocery complaint. Filtering + sorting = prioritising.
🏅 Job skills unlocked today
✔ Use loc and iloc correctly✔ Combine filters with &, |, ~✔ Write readable query() filters✔ Rank data with sort_values and nlargest
Practice tasks
🏋️ Practice – do it yourself
Task 1: From
students_performance.csv, list students with attendance below 70 but average above 70.Show solution
Python
import pandas as pd
st = pd.read_csv("students_performance.csv")
r = st[(st["attendance"] < 70) & (st["average"] > 70)]
print(len(r)); print(r[["student_id", "attendance", "average"]].head())3
student_id attendance average
34 S1035 68.0 72.3
55 S1056 65.0 72.7
192 S1193 66.0 72.0
Task 2: Show the 5 cheapest 3-bedroom houses in Pune from
house_prices.csv.Show solution
Python
import pandas as pd
h = pd.read_csv("house_prices.csv")
print(h.query("city == 'Pune' and bedrooms == 3").nsmallest(5, "price_lakh")[["area_sqft", "age_years", "price_lakh"]]) area_sqft age_years price_lakh
379 630 8 39.36
754 1010 22 41.45
142 1360 10 45.13
127 1040 12 45.96
219 1130 8 46.51
Task 3: In
telecom_churn.csv, how many senior citizens on a monthly contract churned?Show solution
Python
import pandas as pd
t = pd.read_csv("telecom_churn.csv")
print(len(t.query("senior_citizen == 1 and contract == 'Monthly' and churn == 'Yes'")))128
Quick quiz
🧠 Quick Quiz – test yourself
1. df.iloc[0:3] returns how many rows?
Positions 0, 1, 2 – the end is excluded.
2. df.loc[0:3] (default index) returns how many rows?
Labels 0, 1, 2, 3 – loc includes the end.
3. Which means NOT in a pandas filter?
~ inverts a boolean Series.
4. How do you use a Python variable inside query()?
Prefix it with @.
5. Safest way to change values for some rows?
A single .loc call writes into the original DataFrame.
Interview corner
💼 Interview Corner
Explain the difference between loc and iloc.
loc selects by labels (index values and column names) and its slices include the end; iloc selects by integer position like Python lists and excludes the end. Use loc for filters with conditions.
How would you get the top 3 products by revenue in each city?
Sort by city and revenue descending, then groupby("city").head(3) – or use nlargest inside groupby().apply. Day 13 covers groupby in detail.
Summary
- loc = labels (end included); iloc = positions (end excluded).
- Filters: df[(cond1) & (cond2)], isin, between, str.contains, ~.
- query("col > @var") reads like English.
- Update with df.loc[cond, "col"] = value; use .copy() for subsets.
➡️ Next
Day 13: GroupBy and pivot tables – the heart of every business report.
Frequently Asked Questions
Why do I need brackets around each condition?
Because & and | bind more tightly than > and ==. (a > 1) & (b < 5) is correct; a > 1 & b < 5 is not.
Is query() slower?
For normal data sizes the difference is tiny; choose whichever reads better.
