Lesson 1 — Advanced DataFrame Selection and Filtering

Advanced DataFrame Selection and Filtering in Pandas

Table of Contents

In the previous chapter, you learned the fundamentals of selecting rows and columns from a Pandas DataFrame. You used column selection, loc, iloc, Boolean conditions, sorting, and basic filtering. In this lesson, we will take those skills further and learn how professional Data Analysts select precisely the records required for an analytical task.

Advanced DataFrame selection is important because real-world datasets are rarely small. A business dataset may contain thousands or millions of rows and dozens or hundreds of columns. An analyst normally does not want to work with the entire dataset for every question. Instead, the analyst needs to select a meaningful subset of the data.

For example, imagine a sales dataset containing one million transactions. A manager may ask:

Show sales from Dehradun.
Show sales above ₹50,000.
Show only transactions from 2026.
Show North-region customers whose sales exceed ₹100,000.
Show only Customer, Product, and Sales columns.
Show the first 100 qualifying transactions.

These are all selection and filtering problems.

The goal of this lesson is not simply to memorize Pandas syntax. The goal is to understand how an analytical question can be translated into a precise DataFrame operation.

Understanding Advanced Selection

Consider this DataFrame:

import pandas as pd

sales = pd.DataFrame({
    "Order_ID": [1001, 1002, 1003, 1004, 1005, 1006],
    "Customer": [
        "Aman", "Priya", "Rahul",
        "Neha", "Karan", "Sonia"
    ],
    "City": [
        "Dehradun", "Delhi", "Dehradun",
        "Haridwar", "Delhi", "Dehradun"
    ],
    "Product": [
        "Laptop", "Mobile", "Tablet",
        "Laptop", "Mobile", "Laptop"
    ],
    "Sales": [
        85000, 45000, 62000,
        92000, 55000, 115000
    ]
})

print(sales)

The DataFrame contains six transactions.

A simple column selection would be:

sales["Sales"]

But advanced analysis usually requires more than one operation.

For example:

sales.loc[
    sales["Sales"] > 60000,
    ["Customer", "Product", "Sales"]
]

This expression performs two tasks simultaneously.

First, it selects rows where Sales is greater than ₹60,000.

Second, it returns only the Customer, Product, and Sales columns.

This pattern is extremely important:

df.loc[row_condition, columns]

The first part controls the rows.

The second part controls the columns.

Once you understand this structure, many advanced Pandas queries become much easier to write.

Label-Based Selection with loc

The loc accessor is one of the most important tools for DataFrame selection.

Suppose the DataFrame has the default index:

You can select a particular row:

sales.loc[2]

This selects the row with index label 2.

You can select multiple rows:

sales.loc[[0, 2, 5]]

This returns rows with labels 0, 2, and 5.

You can also select a range:

sales.loc[1:4]

Because this is label-based slicing, the ending label is generally included when appropriate.

This differs from positional slicing with iloc.

Position-Based Selection with iloc

iloc works using integer positions.

For example:

sales.iloc[0]

selects the first row.

To select the first three rows:

sales.iloc[0:3]

The result contains positions 0, 1, and 2.

The ending position 3 is excluded, following standard Python slicing behavior.

You can select specific positions:

sales.iloc[[0, 2, 5]]

This selects the first, third, and sixth rows by position.

The distinction remains:

loc → labels

iloc → positions

Selecting Rows and Columns Together

One of the most useful features of loc is the ability to select both rows and columns in a single expression.

sales.loc[
    [0, 2, 5],
    ["Customer", "Sales"]
]

This selects rows 0, 2, and 5 and returns only the Customer and Sales columns.

With iloc, the same idea can be expressed positionally:

sales.iloc[
    [0, 2, 5],
    [1, 4]
]

Here, column positions 1 and 4 correspond to Customer and Sales in this particular DataFrame.

For analytical code, named column selection is often easier to understand because the code clearly communicates the intended business fields.

Filtering with a Single Condition

Filtering is based on a Boolean expression.

Suppose you want transactions where Sales is greater than ₹60,000:

filtered = sales[
    sales["Sales"] > 60000
]

print(filtered)

The condition:

sales["Sales"] > 60000

creates a Boolean Series.

Conceptually, it looks like:

0     True
1    False
2     True
3     True
4    False
5     True

Pandas then keeps the rows corresponding to True.

You can write the same operation using loc:

filtered = sales.loc[
    sales["Sales"] > 60000
]

Using loc becomes particularly useful when you also want to specify the columns to return.

Filtering and Selecting Specific Columns

Suppose the requirement is:

Show customers whose sales exceed ₹60,000, but display only Customer, City, and Sales.

You can write:

result = sales.loc[
    sales["Sales"] > 60000,
    ["Customer", "City", "Sales"]
]

print(result)

This is a professional and readable way to express the analytical requirement.

The first argument answers:

Which records?

The second argument answers:

Which fields?

Filtering with Equality

Text and categorical columns can be filtered using equality.

For example:

dehradun_sales = sales.loc[
    sales["City"] == "Dehradun"
]

print(dehradun_sales)

This returns all records from Dehradun.

You can also filter products:

laptops = sales.loc[
    sales["Product"] == "Laptop"
]

print(laptops)

Equality filtering is particularly common with categorical fields such as region, city, department, product category, customer type, or status.

Filtering with Not Equal

The != operator selects records that do not match a value.

non_delhi = sales.loc[
    sales["City"] != "Delhi"
]

print(non_delhi)

This can be useful when excluding a particular category.

However, when the objective is to define a precise business population, it is often better to explicitly state which categories should be included rather than simply excluding one category.

Using Multiple Conditions

Real business questions frequently involve more than one condition.

Suppose you want:

Dehradun transactions with sales above ₹60,000.

result = sales.loc[
    (sales["City"] == "Dehradun") &
    (sales["Sales"] > 60000)
]

print(result)

The & operator represents element-wise AND.

Both conditions must be True.

For example, a transaction from Dehradun with sales of ₹40,000 does not qualify because the sales condition is False.

A transaction with ₹80,000 sales from Delhi also does not qualify because the city condition is False.

Only records satisfying both conditions are returned.

Using OR Conditions

Suppose you want transactions from either Dehradun or Delhi.

result = sales.loc[
    (sales["City"] == "Dehradun") |
    (sales["City"] == "Delhi")
]

print(result)

The | operator means element-wise OR.

A record qualifies if at least one condition is True.

Multiple OR conditions can also be written:

result = sales.loc[
    (sales["City"] == "Dehradun") |
    (sales["City"] == "Delhi") |
    (sales["City"] == "Haridwar")
]

For many categories, there is often a cleaner alternative using isin().

Filtering with isin()

Suppose you want records from three cities:

cities = [
    "Dehradun",
    "Delhi",
    "Haridwar"
]

result = sales.loc[
    sales["City"].isin(cities)
]

print(result)

This is often more readable than writing many OR conditions.

You can also write:

result = sales.loc[
    sales["Product"].isin([
        "Laptop",
        "Tablet"
    ])
]

This selects records where Product belongs to the specified list.

Excluding Categories with isin()

You can combine isin() with the ~ operator to select values that are not in the specified list.

result = sales.loc[
    ~sales["City"].isin([
        "Delhi",
        "Haridwar"
    ])
]

print(result)

The tilde ~ negates the Boolean condition.

This is useful when you need to exclude several known categories.

Filtering Numerical Ranges

Suppose you want sales between ₹50,000 and ₹100,000.

result = sales.loc[
    (sales["Sales"] >= 50000) &
    (sales["Sales"] <= 100000)
]

print(result)

This works correctly because each comparison creates a Boolean Series and the conditions are combined element by element.

Pandas also provides the convenient between() method:

result = sales.loc[
    sales["Sales"].between(50000, 100000)
]

print(result)

For straightforward ranges, between() can make the intention easier to read.

Filtering Text with str Methods

Text filtering becomes important when exact equality is not sufficient.

Suppose a customer dataset contains names and you want customers whose names start with the letter A.

result = sales.loc[
    sales["Customer"].str.startswith("A")
]

print(result)

You can search for text appearing anywhere within a value using contains():

result = sales.loc[
    sales["Customer"].str.contains("an", case=False, na=False)
]

print(result)

Here:

case=False makes the search case-insensitive.
na=False prevents missing values from producing problematic Boolean results.

Text operations are especially useful when working with customer names, addresses, product descriptions, job titles, categories, and other text fields.

Filtering with Missing Values

Missing values require special attention.

Suppose:

customers = pd.DataFrame({
    "Name": ["Aman", "Priya", "Rahul", "Neha"],
    "City": [
        "Dehradun",
        None,
        "Delhi",
        "Dehradun"
    ],
    "Sales": [
        50000,
        65000,
        None,
        85000
    ]
})

To select rows where City is missing:

missing_city = customers.loc[
    customers["City"].isna()
]

print(missing_city)

To select rows where City is available:

valid_city = customers.loc[
    customers["City"].notna()
]

print(valid_city)

The same pattern works for numerical columns:

missing_sales = customers.loc[
    customers["Sales"].isna()
]

Missing-value filtering is a core part of data-quality analysis.

Combining Missing-Value Conditions

You can combine missing-value checks with other conditions.

For example, find records where City is Dehradun and Sales are available:

result = customers.loc[
    (customers["City"] == "Dehradun") &
    (customers["Sales"].notna())
]

print(result)

This is useful when preparing a reliable subset for analysis.

Filtering with query()

Pandas also provides the query() method for filtering rows using an expression-like syntax.

result = sales.query(
    "Sales > 60000"
)

print(result)

This can be easier to read when conditions become complex.

For multiple conditions:

result = sales.query(
    "Sales > 60000 and City == 'Dehradun'"
)

print(result)

The syntax looks closer to a business rule and can be convenient for exploratory analysis.

However, loc remains extremely useful because it provides explicit control over row conditions and selected columns and works naturally with many Python expressions.

Using Variables Inside query()

Suppose the minimum sales threshold is stored in a variable:

minimum_sales = 60000

result = sales.query(
    "Sales > @minimum_sales"
)

print(result)

The @ symbol tells Pandas to use the Python variable from the surrounding environment.

This can make analytical code more flexible because you can change the threshold without rewriting the filtering expression.

Advanced Column Selection

Sometimes the filtering condition is simple, but the required output contains many columns.

You can store the required columns in a variable:

report_columns = [
    "Order_ID",
    "Customer",
    "Product",
    "Sales"
]

result = sales.loc[
    sales["Sales"] > 60000,
    report_columns
]

print(result)

This approach is useful when building reusable analysis scripts or reporting pipelines.

Instead of repeatedly writing a long list of column names, you can define the list once and reuse it.

Selecting Columns Based on Data Type

Sometimes you do not know the exact column names you want but know the type of data required.

For example, numerical columns can be selected using select_dtypes():

numeric_data = sales.select_dtypes(
    include="number"
)

print(numeric_data)

This is useful when analyzing a dataset containing many mixed data types.

You can select object or string-related columns separately depending on the dataset’s dtypes and Pandas configuration.

This can be useful during automated exploratory data analysis where you want to identify numerical and categorical fields without manually listing every column.

Using Index Conditions

Sometimes the index itself contains meaningful information.

For example:

sales.index = [
    "ORD-A", "ORD-B", "ORD-C",
    "ORD-D", "ORD-E", "ORD-F"
]

You can select by index label:

sales.loc["ORD-C"]

Or multiple labels:

sales.loc[
    ["ORD-A", "ORD-C", "ORD-F"]
]

In many practical datasets, however, important business identifiers are better retained as explicit columns unless there is a clear reason to make them the index.

Using a Business Question to Build a Filter

Consider the question:

“Find all Dehradun laptop transactions above ₹70,000 and show the customer, order ID, and sales.”

Break the question into components.

Required city:

sales["City"] == "Dehradun"

Required product:

sales["Product"] == "Laptop"

Required sales threshold:

sales["Sales"] > 70000

Required output columns:

["Customer", "Order_ID", "Sales"]

Combine them:

result = sales.loc[
    (sales["City"] == "Dehradun") &
    (sales["Product"] == "Laptop") &
    (sales["Sales"] > 70000),
    ["Customer", "Order_ID", "Sales"]
]

print(result)

This is the analytical thinking you should develop while learning Pandas.

Instead of trying to remember one complicated expression, break the business question into smaller conditions and then combine them.

Practical Exercise

Create this DataFrame:

employees = pd.DataFrame({
    "Employee_ID": [101, 102, 103, 104, 105, 106, 107, 108],
    "Name": [
        "Amit", "Priya", "Rahul", "Neha",
        "Karan", "Sonia", "Arjun", "Meena"
    ],
    "Department": [
        "Analytics", "HR", "Analytics", "Finance",
        "IT", "Analytics", "HR", "Finance"
    ],
    "City": [
        "Dehradun", "Delhi", "Dehradun", "Delhi",
        "Dehradun", "Haridwar", "Delhi", "Dehradun"
    ],
    "Salary": [
        55000, 48000, 72000, 68000,
        65000, 58000, 52000, 75000
    ]
})

Now solve these problems:

Select only Name and Salary.
Select the first four rows using iloc.
Select employees from Dehradun.
Select employees earning more than ₹60,000.
Select Analytics employees earning more than ₹55,000.
Select employees from either Delhi or Dehradun.
Select employees whose salary is between ₹50,000 and ₹70,000.
Select employees who are not in HR.
Return only Name, Department, and Salary for Analytics employees.
Find employees whose names start with A.

Try to solve the questions independently before checking documentation. The objective is to build analytical reasoning as well as Python syntax.

Important Rules to Remember

When using multiple Boolean conditions, use parentheses:

(df["Age"] > 25) & (df["Salary"] > 50000)

Use | for element-wise OR.

Use ~ when you need to negate a Boolean condition.

Use isin() when checking membership in multiple categories.

Use isna() and notna() when working with missing values.

Use loc when you want readable label-based row and column selection.

Use iloc when you specifically need integer-position-based selection.

Always validate the filtered result with methods such as:

print(result.shape)
print(result.head())

A filter that runs without an error is not necessarily a filter that answers the correct business question.

Advanced Filtering Techniques for Real-World Data

In the previous section, you learned how to use loc, iloc, Boolean conditions, isin(), between(), string methods, missing-value checks, and query() to select specific records from a Pandas DataFrame.

Now we will apply these techniques to more realistic analytical situations. The important idea is that filtering should be based on a clearly defined requirement. In professional Data Analytics, a filter is not simply a Python expression; it represents a decision about which records belong to the population being analyzed.

For example, if a company wants to analyze high-value customers, the analyst must first define what “high-value” means. It could mean customers with total purchases above ₹100,000, customers with more than ten transactions, or customers belonging to a particular business segment. Pandas can implement the rule, but the rule itself must come from a meaningful analytical requirement.

Filtering Using Several Business Rules

Consider a customer dataset:

customers = pd.DataFrame({
    "Customer_ID": [101, 102, 103, 104, 105, 106, 107, 108],
    "Name": [
        "Aman", "Priya", "Rahul", "Neha",
        "Karan", "Sonia", "Arjun", "Meena"
    ],
    "City": [
        "Dehradun", "Delhi", "Dehradun", "Haridwar",
        "Delhi", "Dehradun", "Haridwar", "Delhi"
    ],
    "Age": [24, 31, 28, 35, 42, 26, 30, 39],
    "Purchase": [
        45000, 85000, 125000, 65000,
        150000, 72000, 95000, 110000
    ]
})

Suppose the business defines a high-value customer as someone who has made a purchase greater than ₹100,000.

high_value = customers.loc[
    customers["Purchase"] > 100000
]

print(high_value)

Now suppose management changes the requirement:

Find customers from Dehradun whose purchase value is above ₹70,000.

result = customers.loc[
    (customers["City"] == "Dehradun") &
    (customers["Purchase"] > 70000)
]

print(result)

Now add another condition:

The customer must also be at least 25 years old.

result = customers.loc[
    (customers["City"] == "Dehradun") &
    (customers["Purchase"] > 70000) &
    (customers["Age"] >= 25)
]

print(result)

Each condition represents part of the business requirement.

This is much easier to understand when you first write the question in natural language and then translate each condition into Pandas.

Using Conditions Stored in Variables

For more complex analysis, conditions can be stored in variables.

dehradun = customers["City"] == "Dehradun"

high_purchase = customers["Purchase"] > 70000

adult_customer = customers["Age"] >= 25

result = customers.loc[
    dehradun & high_purchase & adult_customer
]

print(result)

This approach can make complex analytical logic easier to debug because each condition can be inspected independently.

For example:

print(dehradun)
print(high_purchase)
print(adult_customer)

If the final result looks unexpected, you can identify which condition is responsible.

Filtering with NOT Conditions

Sometimes the requirement is to exclude a category.

For example:

Show customers who are not from Delhi.

result = customers.loc[
    customers["City"] != "Delhi"
]

When excluding multiple cities, use isin() with ~:

result = customers.loc[
    ~customers["City"].isin([
        "Delhi",
        "Haridwar"
    ])
]

print(result)

This means that the City must not belong to the specified list.

Filtering Categories with isin()

Suppose a marketing team wants customers from Dehradun, Delhi, and Haridwar:

target_cities = [
    "Dehradun",
    "Delhi",
    "Haridwar"
]

result = customers.loc[
    customers["City"].isin(target_cities)
]

The advantage of using a list is that the values can easily be changed without rewriting the filtering logic.

For example:

target_cities = [
    "Dehradun",
    "Delhi"
]

Now the same analysis automatically focuses on only those two cities.

Filtering Text Data

Real-world datasets often contain text that cannot be filtered effectively using exact equality.

Suppose a product dataset contains:

products = pd.DataFrame({
    "Product": [
        "HP Laptop",
        "Dell Laptop",
        "Samsung Mobile",
        "Apple Laptop",
        "Lenovo Laptop",
        "OnePlus Mobile"
    ],
    "Sales": [
        85000,
        92000,
        45000,
        125000,
        78000,
        52000
    ]
})

To find products containing the word “Laptop”:

result = products.loc[
    products["Product"].str.contains(
        "Laptop",
        case=False,
        na=False
    )
]

print(result)

This is more flexible than checking whether Product is exactly equal to one particular string.

You can also find products beginning with a particular word:

result = products.loc[
    products["Product"].str.startswith(
        "Apple",
        na=False
    )
]

Or products ending with a particular word:

result = products.loc[
    products["Product"].str.endswith(
        "Mobile",
        na=False
    )
]

These operations are useful for searching product descriptions, customer names, locations, job titles, categories, and other text fields.

Case Sensitivity

Text data may contain inconsistent capitalization.

For example:

cities = pd.DataFrame({
    "City": [
        "Dehradun",
        "dehradun",
        "DEHRADUN",
        "Delhi"
    ]
})

A simple equality condition:

cities.loc[
    cities["City"] == "Dehradun"
]

will not necessarily match all capitalization variations.

A case-insensitive approach can use string normalization:

result = cities.loc[
    cities["City"].str.lower() == "dehradun"
]

print(result)

This technique is useful when source data has inconsistent capitalization.

Whitespace Problems

Another common problem is leading or trailing whitespace.

For example:

cities = pd.DataFrame({
    "City": [
        "Dehradun",
        " Delhi",
        "Dehradun ",
        "Delhi"
    ]
})

The values containing spaces may not match the expected string.

You can normalize the text:

cities["City"] = cities["City"].str.strip()

After this transformation, comparisons become more reliable.

This demonstrates an important principle: sometimes a filtering problem is actually a data-quality problem.

Filtering Dates

Date filtering is extremely common in business analytics.

Consider:

orders = pd.DataFrame({
    "Order_Date": [
        "2026-01-10",
        "2026-02-15",
        "2026-04-20",
        "2026-06-05"
    ],
    "Sales": [
        45000,
        65000,
        85000,
        92000
    ]
})

orders["Order_Date"] = pd.to_datetime(
    orders["Order_Date"]
)

Now you can filter orders after a particular date:

result = orders.loc[
    orders["Order_Date"] > "2026-03-01"
]

print(result)

You can also define a date range:

result = orders.loc[
    (orders["Order_Date"] >= "2026-02-01") &
    (orders["Order_Date"] <= "2026-05-31")
]

Date filtering becomes especially powerful when analyzing monthly sales, quarterly performance, customer activity, website traffic, or other time-dependent datasets.

Filtering by Year or Month

Once a column has been converted to datetime, you can extract components.

orders["Year"] = orders["Order_Date"].dt.year
orders["Month"] = orders["Order_Date"].dt.month

You can then filter:

result = orders.loc[
    orders["Year"] == 2026
]

Or a particular month:

result = orders.loc[
    orders["Month"] == 4
]

This is useful for time-based analysis and will become even more important when you study Pandas date and time operations later in the course.

Filtering Numerical Data with Multiple Ranges

Suppose you want customers whose purchases are either below ₹50,000 or above ₹100,000.

result = customers.loc[
    (customers["Purchase"] < 50000) |
    (customers["Purchase"] > 100000)
]

print(result)

This can be useful for identifying customers at the lower and upper ends of a distribution.

However, analysts should be careful when using thresholds. A threshold should ideally come from business rules, statistical analysis, domain knowledge, or a clearly documented assumption.

Filtering Using query()

The query() method can make some complex conditions easier to read.

result = customers.query(
    "Purchase > 70000 and Age >= 25"
)

print(result)

Multiple categories can be handled with membership expressions:

result = customers.query(
    "City in ['Dehradun', 'Delhi']"
)

print(result)

For some exploratory tasks, this syntax can be easier to read than a long Boolean expression.

Selecting Rows After Filtering

Filtering often needs to be followed by selection.

For example:

result = customers.loc[
    customers["Purchase"] > 70000,
    ["Customer_ID", "Name", "Purchase"]
]

print(result)

This is preferable when preparing a focused report because unnecessary fields are removed from the result.

For a larger analytical pipeline, you might create a reusable list:

report_columns = [
    "Customer_ID",
    "Name",
    "City",
    "Purchase"
]

result = customers.loc[
    customers["Purchase"] > 70000,
    report_columns
]

Filtering and Sorting Together

Filtering and sorting can be combined.

Suppose you want the highest-value Dehradun customers:

result = customers.loc[
    customers["City"] == "Dehradun"
].sort_values(
    by="Purchase",
    ascending=False
)

print(result)

You can limit the result to the top three:

result = customers.loc[
    customers["City"] == "Dehradun"
].sort_values(
    by="Purchase",
    ascending=False
).head(3)

This creates a very common analytical pattern:

Filter → Sort → Limit.

Filtering Before Aggregation

Filtering can also be used before calculating summary statistics.

Suppose you want the average purchase value of Dehradun customers:

average_purchase = customers.loc[
    customers["City"] == "Dehradun",
    "Purchase"
].mean()

print(average_purchase)

Notice that the result is calculated only from the selected population.

This distinction is extremely important.

The average purchase of all customers and the average purchase of Dehradun customers are different metrics. The filter defines the population being analyzed.

Understanding the Analytical Population

Suppose a company reports that its average customer purchase is ₹85,000.

Before interpreting that number, ask:

Which customers were included?
Were cancelled orders excluded?
Were returns included?
Was the analysis limited to a particular period?
Were missing transactions removed?

These questions demonstrate why filtering is not merely a programming operation.

The filter determines what data contributes to the result.

A technically correct calculation based on an incorrectly defined population can still produce a misleading business conclusion.

Performance Considerations

For large DataFrames, efficient selection becomes important.

Selecting only the required columns can reduce unnecessary processing:

result = sales.loc[
    sales["Sales"] > 100000,
    ["Order_ID", "Product", "Sales"]
]

If the dataset contains 100 columns but the report requires only three, there is little reason to carry all 100 columns through every subsequent step.

Similarly, avoid unnecessary repeated filtering when the same subset can be stored and reused:

high_value = sales.loc[
    sales["Sales"] > 100000
]

print(high_value.shape)
print(high_value.head())

You can then perform several analyses on the filtered subset.

Validating a Filter

Always validate important filters.

For example:

result = sales.loc[
    sales["Sales"] > 100000
]

print("Rows:", result.shape[0])
print(result.head())

You can verify that every returned row satisfies the condition:

print(
    (result["Sales"] > 100000).all()
)

If the output is True, every selected record satisfies the stated threshold.

This type of validation is particularly useful in automated reporting and production analytics workflows.

Practice Challenge

Create the following dataset:

sales = pd.DataFrame({
    "Order_ID": range(1001, 1011),
    "Customer": [
        "Aman", "Priya", "Rahul", "Neha", "Karan",
        "Sonia", "Arjun", "Meena", "Ravi", "Pooja"
    ],
    "City": [
        "Dehradun", "Delhi", "Haridwar", "Dehradun",
        "Delhi", "Dehradun", "Haridwar", "Delhi",
        "Dehradun", "Delhi"
    ],
    "Product": [
        "Laptop", "Mobile", "Tablet", "Laptop",
        "Monitor", "Mobile", "Laptop", "Tablet",
        "Laptop", "Mobile"
    ],
    "Sales": [
        85000, 45000, 62000, 125000,
        55000, 72000, 145000, 64000,
        98000, 51000
    ]
})

Complete these tasks:

Find all Dehradun transactions.
Find all transactions above ₹70,000.
Find Dehradun transactions above ₹70,000.
Find transactions involving Laptop or Tablet.
Exclude transactions from Delhi.
Find transactions between ₹50,000 and ₹100,000.
Find customers whose names contain the letter “a”.
Return only Customer, Product, and Sales for transactions above ₹80,000.
Sort the filtered result from highest to lowest sales.
Return only the top three records.

After completing the exercise, inspect the result using:

result.shape
result.head()
result.dtypes

This final validation step should become a habit.

Building Reliable DataFrame Filters for Data Analytics

Advanced filtering becomes especially valuable when a DataFrame represents real business data rather than a small learning dataset. In professional projects, the analyst must often combine several conditions, handle inconsistent values, protect against missing information, and verify that the resulting records actually represent the intended population.

The most important habit is to translate the analytical requirement into smaller logical conditions before writing the final Pandas expression.

For example, consider the requirement:

“Find active customers from Dehradun or Haridwar who spent at least ₹50,000 during the selected period, excluding records with missing customer IDs.”

This requirement contains several separate rules:

Customer must be from Dehradun or Haridwar.
Purchase must be at least ₹50,000.
Customer ID must exist.
The dataset must already represent the selected period.

The corresponding Pandas logic can then be built step by step.

city_condition = customers["City"].isin([
    "Dehradun",
    "Haridwar"
])

purchase_condition = customers["Purchase"] >= 50000

id_condition = customers["Customer_ID"].notna()

result = customers.loc[
    city_condition &
    purchase_condition &
    id_condition
]

print(result)

Breaking complicated filters into named conditions improves readability and makes debugging easier.

Filtering Based on a Calculated Value

Sometimes the value required for filtering does not already exist as a column.

Suppose a sales DataFrame contains revenue and quantity:

sales = pd.DataFrame({
    "Product": [
        "Laptop", "Mobile", "Tablet",
        "Laptop", "Monitor"
    ],
    "Quantity": [2, 5, 4, 1, 3],
    "Sales": [130000, 125000, 96000, 72000, 54000]
})

You may want to find transactions where revenue per unit is greater than ₹30,000.

First calculate the metric:

sales["Revenue_Per_Unit"] = (
    sales["Sales"] / sales["Quantity"]
)

Then filter:

result = sales.loc[
    sales["Revenue_Per_Unit"] > 30000
]

print(result)

You can also construct the condition directly:

result = sales.loc[
    (sales["Sales"] / sales["Quantity"]) > 30000
]

print(result)

The first approach is often easier to read when the calculated metric will be reused several times.

Filtering Relative to the Average

Fixed thresholds are not always appropriate.

Suppose you want to find transactions above the average sales value.

average_sales = sales["Sales"].mean()

result = sales.loc[
    sales["Sales"] > average_sales
]

print(result)

This creates a dynamic filter because the threshold is calculated from the data itself.

You can inspect the threshold:

print("Average Sales:", average_sales)

This approach is useful for identifying observations that perform above or below a central benchmark.

For example, you could find transactions below average:

result = sales.loc[
    sales["Sales"] < average_sales
]

The same idea can be applied to employee salaries, customer spending, product prices, website sessions, exam marks, or other numerical variables.

Filtering Relative to the Median

The median can sometimes provide a more robust benchmark than the mean, especially when extreme values are present.

median_sales = sales["Sales"].median()

result = sales.loc[
    sales["Sales"] > median_sales
]

print(result)

The choice between mean and median should depend on the analytical question and distribution of the data. Pandas gives you the technical ability to calculate both, but the analyst must decide which metric makes sense.

Filtering with Multiple Numerical Columns

Suppose a product dataset contains Sales and Quantity:

products = pd.DataFrame({
    "Product": [
        "Laptop", "Mobile", "Tablet",
        "Monitor", "Keyboard"
    ],
    "Quantity": [3, 15, 8, 12, 25],
    "Sales": [195000, 225000, 192000, 144000, 75000]
})

You might want products with quantity above 10 and sales above ₹100,000:

result = products.loc[
    (products["Quantity"] > 10) &
    (products["Sales"] > 100000)
]

print(result)

Or products where either condition is satisfied:

result = products.loc[
    (products["Quantity"] > 10) |
    (products["Sales"] > 100000)
]

The difference between AND and OR can significantly change the size and meaning of the resulting dataset.

Understanding Operator Precedence

When writing multiple conditions, always use parentheses around individual comparisons.

Prefer:

result = df.loc[
    (df["Sales"] > 50000) &
    (df["Quantity"] > 5)
]

rather than relying on operator precedence.

Parentheses make the intended logic explicit and reduce the chance of errors.

This is especially important when combining three or more conditions.

Filtering Using Membership in a Python List

Suppose a marketing campaign targets selected product categories:

target_products = [
    "Laptop",
    "Tablet",
    "Monitor"
]

result = products.loc[
    products["Product"].isin(target_products)
]

print(result)

The list can come from another part of your Python program, configuration file, or analytical input.

This makes the filtering logic reusable.

Filtering Based on a Dictionary of Rules

In more structured analytical programs, filtering criteria can be stored separately.

criteria = {
    "cities": ["Dehradun", "Delhi"],
    "minimum_sales": 50000
}

result = sales.loc[
    sales["City"].isin(criteria["cities"]) &
    (sales["Sales"] >= criteria["minimum_sales"])
]

This separates the business parameters from the filtering operation.

It can be useful when the same analysis needs to be run repeatedly with different thresholds.

Filtering Duplicate Records

Duplicate records can affect analysis.

Suppose:

customers = pd.DataFrame({
    "Customer_ID": [101, 102, 103, 103, 104],
    "Name": [
        "Aman", "Priya", "Rahul",
        "Rahul", "Neha"
    ],
    "Purchase": [
        50000, 60000, 75000,
        75000, 82000
    ]
})

You can identify duplicate rows:

duplicates = customers.loc[
    customers.duplicated()
]

print(duplicates)

If duplication should be based specifically on Customer_ID:

duplicates = customers.loc[
    customers.duplicated(
        subset="Customer_ID",
        keep=False
    )
]

print(duplicates)

The keep=False option marks all records involved in the duplicate group.

This is useful when investigating data-quality problems.

Filtering the First or Last Duplicate

You can identify repeated records while controlling which occurrence is considered the original.

customers.duplicated(
    subset="Customer_ID",
    keep="first"
)

Or:

customers.duplicated(
    subset="Customer_ID",
    keep="last"
)

This is useful when the business rule says that one particular occurrence should be retained.

However, do not automatically delete duplicates. First determine why the duplicates exist and whether each record represents a separate legitimate event.

Filtering Invalid Numerical Values

Real-world numerical data can contain invalid values.

For example, a quantity field might contain zero or negative values even though the business process expects positive quantities.

valid_sales = sales.loc[
    sales["Quantity"] > 0
]

print(valid_sales)

You could also check sales:

valid_sales = sales.loc[
    sales["Sales"] >= 0
]

These checks are simple but important. Before analyzing numerical data, understand the valid range according to the business context.

Finding Potential Outliers with a Simple Rule

Filtering can also be used to investigate unusually high or low values.

For example, using the interquartile range approach:

q1 = sales["Sales"].quantile(0.25)
q3 = sales["Sales"].quantile(0.75)

iqr = q3 - q1

lower_limit = q1 - 1.5 * iqr
upper_limit = q3 + 1.5 * iqr

outliers = sales.loc[
    (sales["Sales"] < lower_limit) |
    (sales["Sales"] > upper_limit)
]

print(outliers)

This is a common statistical approach for identifying potential outliers.

However, an outlier is not automatically an error. A very large sale could represent a legitimate bulk order. Statistical detection should therefore be followed by business investigation.

Filtering with Category Values

Suppose a customer dataset contains a Status column:

customers = pd.DataFrame({
    "Customer": [
        "Aman", "Priya", "Rahul",
        "Neha", "Karan"
    ],
    "Status": [
        "Active",
        "Inactive",
        "Active",
        "Pending",
        "Active"
    ]
})

To select active customers:

active = customers.loc[
    customers["Status"] == "Active"
]

To select active or pending customers:

result = customers.loc[
    customers["Status"].isin([
        "Active",
        "Pending"
    ])
]

Categorical filtering is common in customer management, HR, healthcare, education, marketing, finance, and operations datasets.

Filtering Boolean Columns

Some DataFrames contain Boolean columns such as Is_Active.

customers = pd.DataFrame({
    "Customer": ["Aman", "Priya", "Rahul", "Neha"],
    "Is_Active": [True, False, True, True]
})

You can select active customers directly:

active = customers.loc[
    customers["Is_Active"]
]

Or inactive customers:

inactive = customers.loc[
    ~customers["Is_Active"]
]

This is a concise and readable way to work with Boolean flags.

Building a Reusable Filtering Function

When the same type of filtering operation is required repeatedly, you can place the logic inside a function.

def filter_high_value_sales(df, threshold):
    return df.loc[
        df["Sales"] > threshold
    ]

Now you can call:

high_sales = filter_high_value_sales(
    sales,
    100000
)

print(high_sales)

And later:

high_sales = filter_high_value_sales(
    sales,
    150000
)

This approach reduces repeated code and makes analytical workflows easier to maintain.

Avoiding Ambiguous Filters

Consider a requirement such as:

“Find high-performing customers.”

This is not yet a sufficiently precise technical requirement.

The analyst should determine what high-performing means.

For example:

Purchase above ₹100,000
More than 10 orders
Profit above ₹25,000
Customer lifetime value above a threshold

Each definition produces a different filtered population.

This is where domain knowledge and communication become important. Pandas can execute a rule, but it cannot determine whether the rule itself is appropriate for the business question.

Checking Filter Results Before Reporting

After applying an important filter, inspect the result:

print(result.shape)
print(result.head())
print(result.tail())
print(result.describe())

You can also check the minimum and maximum of the filtered metric:

print(result["Sales"].min())
print(result["Sales"].max())

If your condition was:

result = sales.loc[
    sales["Sales"] > 100000
]

then:

print(result["Sales"].min())

should not be less than or equal to ₹100,000, assuming there are valid non-missing values.

This simple validation can catch logic errors before they reach a report or dashboard.

Performance and Readability

There are often several ways to write the same filter.

For example:

sales[sales["Sales"] > 100000]

and:

sales.loc[
    sales["Sales"] > 100000
]

both select rows satisfying the condition.

The loc form becomes particularly valuable when you need explicit control over columns:

sales.loc[
    sales["Sales"] > 100000,
    ["Order_ID", "Product", "Sales"]
]

For maintainable analytical code, readability matters. A future analyst should be able to understand why the records were selected.

Mini Project: Customer Segmentation Filter

Let’s combine the concepts into a small project.

customers = pd.DataFrame({
    "Customer_ID": [101, 102, 103, 104, 105, 106, 107, 108],
    "Name": [
        "Aman", "Priya", "Rahul", "Neha",
        "Karan", "Sonia", "Arjun", "Meena"
    ],
    "City": [
        "Dehradun", "Delhi", "Dehradun", "Haridwar",
        "Delhi", "Dehradun", "Haridwar", "Delhi"
    ],
    "Age": [24, 31, 28, 35, 42, 26, 30, 39],
    "Purchase": [
        45000, 85000, 125000, 65000,
        150000, 72000, 95000, 110000
    ],
    "Status": [
        "Active", "Active", "Active", "Pending",
        "Active", "Inactive", "Active", "Active"
    ]
})

Suppose the company wants customers who meet all of these conditions:

Customer is Active.
Customer is from Dehradun or Delhi.
Purchase is at least ₹70,000.
Customer age is at least 25.

Build the filter:

result = customers.loc[
    (customers["Status"] == "Active") &
    (customers["City"].isin([
        "Dehradun",
        "Delhi"
    ])) &
    (customers["Purchase"] >= 70000) &
    (customers["Age"] >= 25)
]

print(result)

Now select only the fields needed by management:

result = customers.loc[
    (customers["Status"] == "Active") &
    (customers["City"].isin([
        "Dehradun",
        "Delhi"
    ])) &
    (customers["Purchase"] >= 70000) &
    (customers["Age"] >= 25),
    ["Customer_ID", "Name", "City", "Purchase"]
]

print(result)

Finally, sort the result:

result = result.sort_values(
    by="Purchase",
    ascending=False
)

print(result)

This is a realistic mini analytical workflow:

Define Requirement
       ↓
Create Conditions
       ↓
Filter Rows
       ↓
Select Columns
       ↓
Sort Results
       ↓
Validate Output

Lesson Practice Questions

Use the customer dataset above to answer the following:

Find all active customers.
Find all inactive customers.
Find customers from Dehradun or Haridwar.
Find customers with purchases above ₹90,000.
Find customers aged between 25 and 40.
Find active customers with purchases above ₹80,000.
Find customers who are not from Delhi.
Find customers whose names contain the letter “a”, ignoring capitalization.
Find the customers whose purchase value is above the average purchase.
Return only Customer_ID, Name, City, and Purchase.
Sort the filtered records from highest to lowest purchase.
Return the top three qualifying customers.

Key Takeaways

Advanced filtering begins with a clearly defined analytical requirement.
Complex filters should be broken into smaller logical conditions.
loc can simultaneously filter rows and select columns.
isin() is useful for membership-based filtering.
~ can negate a Boolean condition.
str.contains(), startswith(), and endswith() are useful for text filtering.
isna() and notna() are important when missing values affect the analysis.
Date filtering requires appropriate datetime representation.
Dynamic filters can use metrics such as mean or median.
Duplicate and invalid records should be investigated rather than automatically deleted.
Filtering defines the population included in an analytical calculation.
Important filtered results should always be validated before reporting.
Readable filtering logic is easier to maintain and audit.

Advanced DataFrame selection and filtering is one of the most transferable Pandas skills for a Data Analyst. Once you can translate business requirements into reliable row conditions and precise column selections, you can work with much larger and more complex datasets with confidence.

In the next lesson, we will build on these techniques by working with advanced sorting, ranking, and ordering of DataFrame data.

Advanced DataFrame Selection: Multi-Level Conditions and Practical Analysis

Advanced DataFrame selection becomes more powerful when you combine several types of conditions in a single analytical workflow. In real-world Data Analytics, you may need to work with numerical values, text, dates, categories, missing values, and calculated metrics at the same time.

The key is to build the logic carefully and validate the result after every important operation.

Combining Numerical and Categorical Conditions

Consider a sales dataset:

import pandas as pd

sales = pd.DataFrame({
    "Order_ID": [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
    "Customer": [
        "Aman", "Priya", "Rahul", "Neha",
        "Karan", "Sonia", "Arjun", "Meena"
    ],
    "Region": [
        "North", "South", "North", "East",
        "West", "North", "South", "East"
    ],
    "Product": [
        "Laptop", "Mobile", "Laptop", "Tablet",
        "Monitor", "Laptop", "Mobile", "Tablet"
    ],
    "Quantity": [2, 5, 1, 4, 3, 2, 7, 5],
    "Sales": [
        130000, 75000, 72000, 96000,
        54000, 145000, 105000, 80000
    ]
})

Suppose management asks for North-region laptop transactions above ₹70,000.

result = sales.loc[
    (sales["Region"] == "North") &
    (sales["Product"] == "Laptop") &
    (sales["Sales"] > 70000)
]

print(result)

This combines three different types of conditions:

A categorical condition for Region.
A categorical condition for Product.
A numerical condition for Sales.

This pattern appears frequently in practical business analysis.

Creating Reusable Conditions

When a filter becomes complicated, separate the conditions first:

north_region = sales["Region"] == "North"

laptop = sales["Product"] == "Laptop"

high_sales = sales["Sales"] > 70000

result = sales.loc[
    north_region &
    laptop &
    high_sales
]

print(result)

This style makes the code easier to read and debug.

You can also inspect each condition separately:

print(north_region)
print(laptop)
print(high_sales)

If the final result is unexpectedly empty, checking these intermediate conditions can quickly reveal the problem.

Using Any and All Conditions

Sometimes you want to evaluate several columns within each row.

For example, suppose a student DataFrame contains scores from three subjects:

students = pd.DataFrame({
    "Name": [
        "Aman", "Priya", "Rahul",
        "Neha", "Karan"
    ],
    "Python": [82, 91, 65, 88, 72],
    "SQL": [78, 94, 70, 85, 68],
    "Excel": [85, 89, 60, 92, 75]
})

Suppose you want students who scored at least 80 in every subject.

result = students.loc[
    (students[["Python", "SQL", "Excel"]] >= 80).all(axis=1)
]

print(result)

The expression creates Boolean values for each subject and then checks whether all three conditions are true for each row.

You can instead find students who scored at least 80 in at least one subject:

result = students.loc[
    (students[["Python", "SQL", "Excel"]] >= 80).any(axis=1)
]

print(result)

This demonstrates a more advanced form of DataFrame filtering.

Filtering Based on Several Columns

Suppose you want students who scored below 60 in at least one subject:

result = students.loc[
    (students[["Python", "SQL", "Excel"]] < 60).any(axis=1)
]

print(result)

This can be useful for identifying records that require attention.

For example, in an education dataset, this technique could identify students who need support in at least one subject.

In a business dataset, similar logic could identify customers who have failed one or more quality criteria.

Filtering Rows Based on Multiple Numeric Columns

Suppose you have an employee dataset:

employees = pd.DataFrame({
    "Name": [
        "Amit", "Priya", "Rahul",
        "Neha", "Karan"
    ],
    "Experience": [2, 5, 3, 8, 6],
    "Salary": [45000, 70000, 52000, 95000, 82000],
    "Performance": [72, 91, 68, 94, 87]
})

Find employees with at least five years of experience and performance above 80:

result = employees.loc[
    (employees["Experience"] >= 5) &
    (employees["Performance"] > 80)
]

print(result)

Now suppose management wants employees satisfying either high performance or high salary:

result = employees.loc[
    (employees["Performance"] > 90) |
    (employees["Salary"] > 90000)
]

print(result)

Understanding the distinction between AND and OR is essential because it changes the analytical population.

Using Conditions Based on Aggregated Values

Advanced filtering can also involve group-level statistics.

Suppose we have:

sales = pd.DataFrame({
    "Region": [
        "North", "North", "South",
        "South", "East", "East"
    ],
    "Sales": [
        80000, 120000, 60000,
        90000, 50000, 70000
    ]
})

You might want to compare each transaction against the average sales of its own region.

A grouped average can be calculated:

region_average = sales.groupby(
    "Region"
)["Sales"].transform("mean")

sales["Region_Average"] = region_average

Now filter transactions above their regional average:

result = sales.loc[
    sales["Sales"] > sales["Region_Average"]
]

print(result)

This is an important advanced pattern because the comparison value changes according to the group to which each row belongs.

Instead of comparing every transaction against one global average, each transaction is compared with its own region’s average.

Filtering with Ranking Logic

You can also create a rank and filter according to it.

sales["Sales_Rank"] = sales["Sales"].rank(
    ascending=False,
    method="dense"
)

top_sales = sales.loc[
    sales["Sales_Rank"] <= 3
]

print(top_sales)

This approach becomes especially useful when ranking within groups, which you will explore more deeply in later lessons.

Filtering with a Custom Function

Sometimes a business rule is too complicated for a simple comparison.

You can define a custom function:

def qualifies(row):
    return (
        row["Sales"] > 100000 and
        row["Quantity"] >= 2
    )

result = sales.loc[
    sales.apply(qualifies, axis=1)
]

print(result)

This approach gives you flexibility, but it should not automatically be preferred over vectorized Pandas operations.

For simple numerical conditions, normal vectorized expressions are usually clearer and more efficient.

Use row-wise functions when the business logic genuinely requires it and cannot be expressed cleanly with standard vectorized operations.

Filtering Based on Multiple Text Conditions

Suppose a product dataset contains descriptions:

products = pd.DataFrame({
    "Product": [
        "HP Laptop Pro",
        "Dell Laptop Basic",
        "Samsung Mobile",
        "Apple Laptop Air",
        "Lenovo Tablet",
        "OnePlus Mobile Pro"
    ],
    "Category": [
        "Laptop",
        "Laptop",
        "Mobile",
        "Laptop",
        "Tablet",
        "Mobile"
    ]
})

Find products containing the word Laptop:

result = products.loc[
    products["Product"].str.contains(
        "Laptop",
        case=False,
        na=False
    )
]

Now combine text conditions:

result = products.loc[
    products["Product"].str.contains(
        "Laptop",
        case=False,
        na=False
    ) &
    (products["Category"] == "Laptop")
]

print(result)

This ensures that both the product description and category satisfy the requirement.

Handling Missing Text Safely

Suppose a text column contains missing values:

products = pd.DataFrame({
    "Product": [
        "Laptop Pro",
        None,
        "Mobile",
        "Tablet"
    ]
})

A safe text search is:

result = products.loc[
    products["Product"].str.contains(
        "Laptop",
        case=False,
        na=False
    )
]

The na=False argument ensures that missing values are treated as False for this filtering operation.

This small detail can prevent errors and make text filtering more robust.

Filtering Using a Date Range

Suppose a sales dataset contains dates:

sales = pd.DataFrame({
    "Order_Date": [
        "2026-01-05",
        "2026-02-15",
        "2026-03-20",
        "2026-05-10",
        "2026-07-01"
    ],
    "Sales": [
        50000,
        65000,
        72000,
        85000,
        95000
    ]
})

sales["Order_Date"] = pd.to_datetime(
    sales["Order_Date"]
)

To select orders from February through May:

result = sales.loc[
    sales["Order_Date"].between(
        "2026-02-01",
        "2026-05-31"
    )
]

print(result)

Date ranges are frequently used for monthly reports, quarterly analysis, campaign measurement, financial reporting, and operational monitoring.

Filtering by Business Status

Suppose a dataset contains order status:

orders = pd.DataFrame({
    "Order_ID": [1, 2, 3, 4, 5, 6],
    "Status": [
        "Completed",
        "Pending",
        "Cancelled",
        "Completed",
        "Returned",
        "Completed"
    ],
    "Sales": [
        50000,
        60000,
        45000,
        80000,
        55000,
        95000
    ]
})

Suppose management wants only completed orders:

completed = orders.loc[
    orders["Status"] == "Completed"
]

Suppose cancelled and returned transactions should both be excluded:

valid = orders.loc[
    ~orders["Status"].isin([
        "Cancelled",
        "Returned"
    ])
]

Notice that “valid” is a business definition. Whether a Pending transaction should be included depends on the purpose of the analysis.

For a revenue report, the business may want only Completed transactions. For an operational report, Pending orders may be important.

This is why filtering rules should always be connected to the reporting objective.

Filtering Before Calculating KPIs

Suppose a company wants the average revenue from completed orders only.

completed_average = orders.loc[
    orders["Status"] == "Completed",
    "Sales"
].mean()

print(completed_average)

This is different from:

orders["Sales"].mean()

The first calculation uses only completed orders, while the second includes every status.

This difference can materially affect a business KPI.

Therefore, before calculating any KPI, define the population included in the calculation.

Debugging an Unexpected Empty Result

Sometimes a filter returns no rows:

result = sales.loc[
    (sales["Region"] == "North") &
    (sales["Product"] == "Tablet") &
    (sales["Sales"] > 200000)
]

print(result)

An empty DataFrame does not necessarily mean Pandas is broken.

It may simply mean that no record satisfies all three conditions.

Debug each condition:

print(
    sales["Region"].value_counts()
)

print(
    sales["Product"].value_counts()
)

print(
    sales["Sales"].describe()
)

This can reveal whether the threshold is unrealistic or whether the category does not exist.

You can also inspect the intermediate subsets:

north = sales.loc[
    sales["Region"] == "North"
]

print(north)

Then:

north_tablet = north.loc[
    north["Product"] == "Tablet"
]

print(north_tablet)

Finally, apply the numerical threshold.

This step-by-step debugging method is extremely useful when developing complex analytical code.

Creating an Analytical Filter Pipeline

A readable workflow might look like this:

filtered = sales.loc[
    sales["Status"] == "Completed"
]

filtered = filtered.loc[
    filtered["Region"].isin([
        "North",
        "South"
    ])
]

filtered = filtered.loc[
    filtered["Sales"] > 50000
]

filtered = filtered[
    ["Order_ID", "Product", "Region", "Sales"]
]

filtered = filtered.sort_values(
    by="Sales",
    ascending=False
)

print(filtered)

This approach is longer than writing everything in one expression, but it can be easier to understand and debug.

For reusable production code, clarity is often more valuable than making every expression as short as possible.

Validating the Final Population

After filtering, ask whether the resulting population is logically correct.

print("Number of records:", len(filtered))
print(filtered["Sales"].min())
print(filtered["Sales"].max())

If the filter requires Sales above ₹50,000, the minimum valid Sales value should satisfy that rule.

You can explicitly test the condition:

assert (
    filtered["Sales"] > 50000
).all()

If the assertion fails, investigate the filtering logic.

Assertions can be useful in analytical pipelines because they provide a simple way to catch unexpected results before a report is generated.

Practical Mini Project

Create the following employee dataset:

employees = pd.DataFrame({
    "Employee_ID": range(101, 111),
    "Name": [
        "Amit", "Priya", "Rahul", "Neha", "Karan",
        "Sonia", "Arjun", "Meena", "Ravi", "Pooja"
    ],
    "Department": [
        "Analytics", "HR", "Analytics", "Finance", "IT",
        "Analytics", "HR", "Finance", "Analytics", "IT"
    ],
    "City": [
        "Dehradun", "Delhi", "Dehradun", "Delhi",
        "Haridwar", "Dehradun", "Delhi", "Dehradun",
        "Haridwar", "Delhi"
    ],
    "Experience": [2, 5, 4, 8, 6, 3, 7, 5, 4, 9],
    "Salary": [
        45000, 65000, 72000, 95000, 82000,
        58000, 78000, 70000, 68000, 105000
    ],
    "Performance": [
        72, 91, 85, 94, 87,
        76, 89, 82, 90, 96
    ]
})

Complete these analytical tasks:

Find employees from Dehradun.
Find employees from Dehradun or Haridwar.
Find employees earning more than ₹70,000.
Find Analytics employees earning more than ₹60,000.
Find employees with performance above 85 and salary above ₹70,000.
Find employees with either performance above 90 or salary above ₹90,000.
Find employees with at least five years of experience and performance above 80.
Find employees who do not belong to HR.
Find employees whose names start with A.
Return only Name, Department, Salary, and Performance.
Calculate the average salary and filter employees above that average.
Sort the final filtered result by salary in descending order.

For each task, think about the population you are selecting and why the condition is appropriate.

Professional Data Analytics Perspective

Advanced filtering is not just a technical skill. It is part of data reasoning.

Suppose an analyst is asked to calculate “average customer spending.” The analyst should not immediately write:

df["Purchase"].mean()

First, the analyst should determine what records belong in the calculation.

Should cancelled orders be excluded?

Should returned orders be excluded?

Should test accounts be excluded?

Should customers without a valid ID be included?

Should the calculation cover all historical data or only the current financial year?

These questions determine the filter.

Only after defining the population should the metric be calculated.

This is a critical difference between simply knowing Pandas syntax and performing professional Data Analytics.

Lesson 1 Summary

In this lesson, you expanded your understanding of Pandas DataFrame selection and filtering. You learned how to combine numerical, categorical, Boolean, text, date, and missing-value conditions to create precise analytical subsets.

You learned to use loc for explicit row and column selection, isin() for category membership, between() for ranges, string methods for text searches, isna() and notna() for missing data, and query() for expression-based filtering.

You also learned how to compare values against averages and medians, use any() and all() across multiple columns, identify potential duplicates and outliers, build reusable conditions, debug empty results, validate filtered populations, and design filtering workflows around actual business questions.

The most important principle is simple:

Define the analytical population first, then write the filter.

A filter determines which records contribute to an analysis. Therefore, an incorrect filter can produce an incorrect conclusion even when the Python code runs perfectly.

In the next lesson, we will focus specifically on sorting, ranking, and ordering DataFrame data, including multiple-column sorting, ranking methods, top-N analysis, group-based ranking, and practical business examples.

Pandas in Python: Complete Data Analysis Course with Projects

Curriculum

Lesson 1 — Advanced DataFrame Selection and Filtering

Advanced DataFrame Selection and Filtering in Pandas

Understanding Advanced Selection

Label-Based Selection with loc

Position-Based Selection with iloc

Selecting Rows and Columns Together

Filtering with a Single Condition

Filtering and Selecting Specific Columns

Filtering with Equality

Filtering with Not Equal

Using Multiple Conditions

Using OR Conditions

Filtering with isin()

Excluding Categories with isin()

Filtering Numerical Ranges

Filtering Text with str Methods

Filtering with Missing Values

Combining Missing-Value Conditions

Filtering with query()

Using Variables Inside query()

Advanced Column Selection

Selecting Columns Based on Data Type

Using Index Conditions

Using a Business Question to Build a Filter

Practical Exercise

Important Rules to Remember

Advanced Filtering Techniques for Real-World Data

Filtering Using Several Business Rules

Using Conditions Stored in Variables

Filtering with NOT Conditions

Filtering Categories with isin()

Filtering Text Data

Case Sensitivity

Whitespace Problems

Filtering Dates

Filtering by Year or Month

Filtering Numerical Data with Multiple Ranges

Filtering Using query()

Selecting Rows After Filtering

Filtering and Sorting Together

Filtering Before Aggregation

Understanding the Analytical Population

Performance Considerations

Validating a Filter

Practice Challenge

Building Reliable DataFrame Filters for Data Analytics

Filtering Based on a Calculated Value

Filtering Relative to the Average

Filtering Relative to the Median

Filtering with Multiple Numerical Columns

Understanding Operator Precedence

Filtering Using Membership in a Python List

Filtering Based on a Dictionary of Rules

Filtering Duplicate Records

Filtering the First or Last Duplicate

Filtering Invalid Numerical Values

Finding Potential Outliers with a Simple Rule

Filtering with Category Values

Filtering Boolean Columns

Building a Reusable Filtering Function

Avoiding Ambiguous Filters

Checking Filter Results Before Reporting

Performance and Readability

Mini Project: Customer Segmentation Filter

Lesson Practice Questions

Key Takeaways

Advanced DataFrame Selection: Multi-Level Conditions and Practical Analysis

Combining Numerical and Categorical Conditions

Creating Reusable Conditions

Using Any and All Conditions

Filtering Based on Several Columns

Filtering Rows Based on Multiple Numeric Columns

Using Conditions Based on Aggregated Values

Filtering with Ranking Logic

Filtering with a Custom Function

Filtering Based on Multiple Text Conditions

Handling Missing Text Safely

Filtering Using a Date Range