I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Selecting and Filtering Data

Selecting data means choosing rows and columns from a DataFrame. df["col"] picks a column; .loc selects by labels, such as df.loc[rows, columns]; .iloc selects by integer position; and boolean filtering keeps the rows where a condition is True, combining conditions with &, | and ~. Methods such as isin, between, str.contains and query make common filters shorter.

12 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

Definition

Out of 50,000 orders, you need those from Pune, above ₹2,000, placed in December, showing only the customer and amount columns. One line of pandas can do it, if you know how selection works.

Selecting data means choosing rows and columns from a DataFrame. df["col"] picks a column; .loc selects by labels, such as df.loc[rows, columns]; .iloc selects by integer position; and boolean filtering keeps the rows where a condition is True, combining conditions with &, | and ~. Methods such as isin, between, str.contains and query make common filters shorter.

In simple words

It is like using the filters on a shopping website: tick "Pune", set the price above ₹2,000, choose December, and hide the columns you do not need. pandas lets you write those ticks as code.

Overview

  • Selecting means choosing columns or rows; filtering means keeping rows that meet conditions.
  • df["col"] gives a Series, df[["a", "b"]] a smaller DataFrame.
  • loc selects by labels and iloc by integer positions.
  • Conditions combine with & (and), | (or) and ~ (not), each inside brackets.
  • Assign with df.loc[condition, "col"] = value to avoid the SettingWithCopyWarning.
  • Filtering is how you study one group, remove bad rows or build training subsets.

Why it matters

Almost every analysis is a question about a subset: one region, one period, one type of customer. Selecting correctly, and knowing the difference between labels and positions, avoids subtle bugs and the famous SettingWithCopyWarning.

Key terms

.loc
Selection by row and column labels; slices include the end label.
.iloc
Selection by integer position; slices exclude the end, as in Python.
Boolean filter
A True/False Series used to keep matching rows.
isin
Tests membership in a list: df["city"].isin(["Pune", "Nashik"]).
query
Filtering with a readable string expression, such as df.query("amount > 2000").
Chained assignment
Assigning through two selections, df[a][b] = x, which may modify a temporary copy.

Types

pandas offers several selection methods. Each type suits a different way of describing what you want.

1. Column selection

Choosing one or more columns by name.

  • How it works: df["marks"] returns a Series; df[["name", "marks"]] returns a DataFrame; select_dtypes("number") picks columns by type.
  • Example: X = df[["area", "rooms", "age"]]; y = df["price"].
  • When to use it: Use it to separate features from the target and to drop irrelevant columns. Selecting only the needed columns early also saves memory on large files.

2. Label-based selection (loc)

Selecting rows and columns by their labels.

  • How it works: df.loc[row_labels, column_labels]; slices with labels include the end, and conditions can go in the row position.
  • Example: df.loc["Asha", "maths"]; df.loc[df["city"] == "Pune", ["name", "marks"]].
  • When to use it: Use it for readable code and for setting values safely. It is the standard way to change values in a filtered part of a table without warnings.

3. Position-based selection (iloc)

Selecting rows and columns by integer position, like NumPy.

  • How it works: df.iloc[0:5, 1:3] takes rows 0–4 and columns 1–2; the end is excluded, as in Python slicing.
  • Example: df.iloc[-1] gives the last row; df.iloc[:, 0] the first column.
  • When to use it: Use it when position matters, such as the first rows or the last column, regardless of labels.

4. Boolean filtering

Keeping rows where a condition is True.

  • How it works: df[df["marks"] >= 33] keeps passing rows; combine conditions as df[(df["city"] == "Pune") & (df["age"] < 18)].
  • Example: Orders above ₹1,000 placed after 10 p.m.
  • When to use it: Use it for any subset defined by rules. Remember to put each condition in brackets and to use & and | rather than the words and and or, which fail on columns.

5. Helper filters: isin, between, str methods and query

Shortcuts for common conditions.

  • How it works: isin([...]) tests membership; between(a, b) tests ranges; .str.contains("text") searches text; df.query("marks > 80 and city == 'Pune'") writes conditions as a string.
  • Example: df[df["state"].isin(["Kerala", "Goa"])]; df[df["name"].str.startswith("A")].
  • When to use it: Use them to keep long conditions short and readable. They also reduce mistakes, because the intention is visible at a glance to anyone reviewing the code.

Explained in detail

Columns, labels and positions

CodeSelects
df["amount"]One column as a Series
df[["customer", "amount"]]Several columns as a DataFrame
df.loc[5]The row whose index label is 5
df.loc[5:10, "amount"]Labels 5 to 10 inclusive, one column
df.iloc[0]The first row, whatever its label
df.iloc[:5, :2]First five rows, first two columns

The difference matters as soon as the index is not simply 0, 1, 2. After sorting or filtering, the labels stay with their rows, so df.loc[0] may be in the middle of the table while df.iloc[0] is the top row. A good habit is to use .loc for everything label-based, including filtering, and .iloc only when you truly mean position.

Filtering rows

A condition on a column produces a boolean Series, and using it inside [] or .loc keeps the matching rows: df[df["amount"] > 2000]. Combine conditions with & and |, putting each in brackets: df.loc[(df["city"] == "Pune") & (df["amount"] > 2000), ["customer", "amount"]]. Use ~ to negate. Handy shortcuts include df["city"].isin(["Pune", "Nashik"]), df["amount"].between(1000, 5000), df["name"].str.contains("Kumar", case=False) and date comparisons such as df["date"] >= "2024-12-01" once the column is a datetime. df.query("city == 'Pune' and amount > 2000") does the same with a readable string.

Changing values safely

  • Assign with a single .loc: df.loc[df["amount"] < 0, "amount"] = np.nan.
  • Avoid chained assignment such as df[df["city"] == "Pune"]["amount"] = 0, which may change a temporary copy and leave df unchanged.
  • When you need a separate subset to modify, take an explicit copy: pune = df[df["city"] == "Pune"].copy().
  • pandas 3 uses copy-on-write by default, which makes this behaviour more predictable, but the single-.loc habit is still the clearest.

Selection is also where many analyses quietly go wrong. A filter written as city == "Pune" misses rows stored as "pune" or "Pune " with a trailing space, so clean text first or filter with str.strip().str.lower(). Date filters fail if the column is still text. And a filter that removes rows with missing values may also remove a whole group of people. Before trusting a result, count the rows that survived each step and ask whether the ones you dropped were dropped for the right reason.

How it works

  1. State the question in words: which rows, which columns?
  2. Write each row condition separately and check it with .sum() to see how many rows match.
  3. Combine conditions with & and | in brackets.
  4. Select with df.loc[mask, columns].
  5. Use .copy() before modifying a subset you will keep.

In code

filtering.py
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Asha", "Ravi", "Neha", "Imran", "Kiran", "Dev"],
    "city": ["Pune", "Delhi", "Pune", "Nashik", "Pune", "Delhi"],
    "amount": [2500, 1800, 900, 3200, 4100, 2600],
    "date": pd.to_datetime(["2024-12-02", "2024-12-05", "2024-11-28", "2024-12-10", "2024-12-15", "2024-12-20"]),
})
mask = (orders["city"] == "Pune") & (orders["amount"] > 2000) & (orders["date"] >= "2024-12-01")
print("Matches per condition:", (orders["city"] == "Pune").sum(), (orders["amount"] > 2000).sum())
print(orders.loc[mask, ["customer", "amount"]])

print(orders[orders["city"].isin(["Pune", "Nashik"])]["customer"].tolist())
print(orders.query("amount.between(2000, 3000)")[["customer", "amount"]])

sorted_orders = orders.sort_values("amount")
print("loc[0] ->", sorted_orders.loc[0, "customer"], " iloc[0] ->", sorted_orders.iloc[0]["customer"])

Output

Matches per condition: 3 4
  customer  amount
0     Asha    2500
4    Kiran    4100
['Asha', 'Neha', 'Imran', 'Kiran']
  customer  amount
0     Asha    2500
5      Dev    2600
loc[0] -> Asha  iloc[0] -> Neha

Try it yourself

The December report

A shop manager asks: "Which Pune customers spent over ₹2,000 in December?"

Match the code to its meaning

What does each select?

  • df.iloc[0]
  • df.loc[0]
  • df["city"].isin(["Pune", "Delhi"])
  • df[["a", "b"]]

Common mistakes

  • Confusing .loc labels with .iloc positions after sorting.
  • Chained assignment that silently changes nothing.
  • Comparing a text date column to a date without converting it.

Check yourself

Question 1

1. What is the difference between .loc and .iloc?

Question 2

2. Which combines two conditions correctly?

Question 3

3. How do you safely set negative amounts to missing?

Summary

  • Use [] for columns, .loc for labels and .iloc for positions.
  • Filter rows with boolean masks combined by &, | and ~ in brackets.
  • Assign with one .loc call, and copy subsets you will modify.

Finished reading?

Sources: pandas: Indexing and selecting data · Python for Data Analysis, 3rd edition (Wes McKinney)