Filtering is one of the most important operations in Pandas because Data Analysts rarely need every row of a dataset for every analysis. A business dataset may contain thousands or millions of records, while a particular question may require only a specific group of rows.
For example, a sales manager may ask you to identify North-region orders above ₹100,000. A marketing team may want customers from selected cities who have spent more than ₹50,000. An HR department may need employees with more than five years of experience and a performance score above 80. These requirements can all be translated into Pandas filtering conditions.
A basic Pandas filter follows this structure:
df[condition]When you also want to control which columns are returned, loc is particularly useful:
df.loc[condition, columns]The key skill is learning how to convert a business requirement into one or more Boolean conditions.
We will use the following sales dataset throughout this lesson:
import pandas as pd
sales = pd.DataFrame({
"Order_ID": [
1001, 1002, 1003, 1004, 1005,
1006, 1007, 1008, 1009, 1010
],
"Customer": [
"Aman", "Priya", "Rahul", "Neha", "Karan",
"Sonia", "Arjun", "Meena", "Ravi", "Pooja"
],
"Region": [
"North", "South", "North", "East", "West",
"North", "South", "East", "North", "West"
],
"Product": [
"Laptop", "Mobile", "Tablet", "Laptop", "Monitor",
"Laptop", "Mobile", "Tablet", "Laptop", "Mobile"
],
"Quantity": [
2, 5, 3, 1, 4,
2, 6, 3, 4, 5
],
"Sales": [
130000, 75000, 96000, 72000, 54000,
145000, 90000, 64000, 155000, 80000
]
})
print(sales)Before filtering, it is good practice to inspect the DataFrame:
print(sales.head())
print(sales.shape)
print(sales.dtypes)This confirms the available columns, number of rows, and data types before you begin analysis.
Suppose the first business requirement is:
Find all transactions where Sales is greater than ₹100,000.
result = sales.loc[
sales["Sales"] > 100000
]
print(result)The expression sales["Sales"] > 100000 creates a Boolean Series. Pandas evaluates every row and determines whether the condition is True or False.
Only rows where the condition is True are returned.
You can use the same principle with equality:
result = sales.loc[
sales["Region"] == "North"
]
print(result)This selects only North-region transactions.
To exclude North-region transactions:
result = sales.loc[
sales["Region"] != "North"
]
print(result)Other common comparison operators include:
sales["Sales"] > 100000
sales["Sales"] < 100000
sales["Sales"] >= 100000
sales["Sales"] <= 100000
sales["Sales"] == 100000
sales["Sales"] != 100000These operators allow numerical business rules to be expressed directly in Python.
The next step is combining multiple conditions.
Suppose the requirement becomes:
Find North-region transactions with Sales greater than ₹100,000.
result = sales.loc[
(sales["Region"] == "North") &
(sales["Sales"] > 100000)
]
print(result)The & operator represents element-wise AND. Both conditions must be True for a row to be included.
A North transaction with ₹50,000 sales does not qualify because the sales condition is False. A South transaction with ₹150,000 sales does not qualify because the region condition is False.
When combining Pandas conditions, use parentheses around each comparison:
result = sales.loc[
(sales["Sales"] > 50000) &
(sales["Quantity"] > 2)
]Do not use the normal Python and operator for combining Pandas Series conditions.
# Incorrect
df["Sales"] > 50000 and df["Quantity"] > 2Use:
# Correct
df.loc[
(df["Sales"] > 50000) &
(df["Quantity"] > 2)
]For OR conditions, use the element-wise | operator.
Suppose the business wants transactions from either North or South:
result = sales.loc[
(sales["Region"] == "North") |
(sales["Region"] == "South")
]
print(result)At least one condition must be True.
AND and OR can also be combined:
result = sales.loc[
(
(sales["Region"] == "North") |
(sales["Region"] == "South")
) &
(sales["Sales"] > 90000)
]
print(result)This means the transaction must be from North or South and must also have sales above ₹90,000.
For a simple NOT condition, you can use !=:
result = sales.loc[
sales["Region"] != "West"
]You can also use the tilde operator ~ to negate a Boolean expression:
result = sales.loc[
~(sales["Region"] == "West")
]The tilde becomes particularly useful when filtering multiple categories with isin().
Suppose the company wants transactions from North, South, and East:
target_regions = [
"North",
"South",
"East"
]
result = sales.loc[
sales["Region"].isin(target_regions)
]
print(result)isin() checks whether each value belongs to the supplied list.
This is much cleaner than writing several OR conditions:
result = sales.loc[
(sales["Region"] == "North") |
(sales["Region"] == "South") |
(sales["Region"] == "East")
]When the number of categories grows, isin() makes the code easier to maintain.
You can also exclude several categories:
excluded_regions = [
"South",
"West"
]
result = sales.loc[
~sales["Region"].isin(excluded_regions)
]
print(result)This means that the selected rows must not belong to either South or West.
Another common requirement is filtering values within a numerical range.
For example, suppose the requirement is:
Find transactions with sales between ₹70,000 and ₹120,000.
result = sales.loc[
(sales["Sales"] >= 70000) &
(sales["Sales"] <= 120000)
]
print(result)Pandas also provides the between() method:
result = sales.loc[
sales["Sales"].between(
70000,
120000
)
]
print(result)This makes range-based conditions concise and readable.
The same technique can be applied to many types of numerical analysis, including salary ranges, age ranges, product prices, examination marks, customer spending, and transaction values.
Now consider a more complex requirement:
Find North or South transactions involving Laptop or Mobile products with sales between ₹70,000 and ₹150,000.
Instead of writing one long expression immediately, break the requirement into three conditions.
region_condition = sales["Region"].isin([
"North",
"South"
])
product_condition = sales["Product"].isin([
"Laptop",
"Mobile"
])
sales_condition = sales["Sales"].between(
70000,
150000
)Now combine them:
result = sales.loc[
region_condition &
product_condition &
sales_condition
]
print(result)This approach makes complicated filtering easier to understand and debug.
You can inspect each condition separately:
print(region_condition)
print(product_condition)
print(sales_condition)If the final result is unexpectedly empty, examining these intermediate conditions can help identify which part of the requirement is causing the problem.
You can also select only the columns needed for the final report:
result = sales.loc[
region_condition &
product_condition &
sales_condition,
[
"Order_ID",
"Customer",
"Product",
"Sales"
]
]
print(result)This produces a focused DataFrame that contains only the information required for the analysis or report.
One important principle to remember is that a filter defines the analytical population. If you calculate a KPI after filtering, that KPI applies only to the records that passed the filter.
For example:
average_sales = sales["Sales"].mean()
north_average = sales.loc[
sales["Region"] == "North",
"Sales"
].mean()
print("Overall Average:", average_sales)
print("North Average:", north_average)The first value represents the average across all transactions. The second represents only North-region transactions.
Both calculations may be correct, but they answer different analytical questions.
This is why professional Data Analytics requires more than knowing Pandas syntax. The analyst must understand what population should be included before calculating a metric.
Advanced filtering becomes more useful when several conditions must be evaluated together. In practical Data Analytics, a requirement may involve region, product, sales value, quantity, customer type, or status at the same time. The safest approach is to translate the requirement into small logical conditions and then combine them.
For example, suppose a manager asks:
“Show active customers from North or South whose purchase value is at least ₹75,000.”
Instead of writing the entire expression immediately, identify the individual rules:
active_condition = customers["Status"] == "Active"
region_condition = customers["Region"].isin([
"North",
"South"
])
purchase_condition = customers["Purchase"] >= 75000Then combine them:
result = customers.loc[
active_condition &
region_condition &
purchase_condition
]
print(result)This structure is easier to read and much easier to troubleshoot when the result is not what you expected.
You can also use conditions involving several numerical columns. Consider a student dataset:
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 comparison creates Boolean values for each subject. The all(axis=1) operation checks whether every selected condition is true for each row.
If the requirement is instead that a student must score at least 80 in at least one subject, use any(axis=1):
result = students.loc[
(students[["Python", "SQL", "Excel"]] >= 80).any(axis=1)
]
print(result)This approach can be applied far beyond education datasets. For example, a business might use it to identify products that meet a target in every quarter or customers who satisfy a condition in at least one category.
Another useful technique is filtering against a calculated benchmark.
Suppose you want to identify sales transactions above the average transaction value:
average_sales = sales["Sales"].mean()
result = sales.loc[
sales["Sales"] > average_sales
]
print(result)The important advantage is that the threshold is dynamic. If the dataset changes, the average automatically changes as well.
You can perform the same operation using the median:
median_sales = sales["Sales"].median()
result = sales.loc[
sales["Sales"] > median_sales
]
print(result)The mean and median should not be treated as interchangeable. The mean can be strongly affected by unusually large values, while the median is generally more resistant to extreme observations. The appropriate benchmark depends on the analytical objective and the distribution of the data.
Filtering can also be based on a calculated value that does not originally exist as a column.
Suppose a sales dataset contains quantity and sales:
sales["Revenue_Per_Unit"] = (
sales["Sales"] / sales["Quantity"]
)Now identify transactions where revenue per unit is greater than ₹30,000:
result = sales.loc[
sales["Revenue_Per_Unit"] > 30000
]
print(result)This is a common analytical pattern:
Calculate Metric
↓
Create Condition
↓
Filter Data
↓
Analyze ResultFor example, you could calculate profit margin, average order value, marks percentage, revenue per employee, or conversion rate and then filter records based on that calculated metric.
Text conditions are another important part of advanced filtering.
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"
]
})To find product names containing “Laptop”:
result = products.loc[
products["Product"].str.contains(
"Laptop",
case=False,
na=False
)
]
print(result)The case=False argument makes the search case-insensitive, while na=False ensures missing values do not cause unexpected Boolean results.
You can combine text filtering with categorical filtering:
result = products.loc[
products["Product"].str.contains(
"Laptop",
case=False,
na=False
) &
(products["Category"] == "Laptop")
]
print(result)You can also search for values beginning with specific text:
result = products.loc[
products["Product"].str.startswith(
"Apple",
na=False
)
]Or values ending with specific text:
result = products.loc[
products["Product"].str.endswith(
"Pro",
na=False
)
]These techniques are useful for customer names, product descriptions, addresses, job titles, categories, and other text-based fields.
Text normalization is important when source data is inconsistent.
cities = pd.DataFrame({
"City": [
"Dehradun",
"dehradun",
"DEHRADUN",
" Delhi ",
"Delhi"
]
})A direct comparison may not identify all variations:
cities.loc[
cities["City"] == "Dehradun"
]A more robust approach is to normalize the text first:
cities["City_Clean"] = (
cities["City"]
.str.strip()
.str.lower()
)
result = cities.loc[
cities["City_Clean"] == "dehradun"
]
print(result)The strip() operation removes unnecessary spaces and lower() standardizes capitalization.
This demonstrates an important Data Analytics principle: sometimes a filtering problem is actually a data-quality problem. If the source values are inconsistent, even technically correct filtering code may produce incomplete results.
Dates are another major area of advanced filtering.
orders = pd.DataFrame({
"Order_Date": [
"2026-01-10",
"2026-02-15",
"2026-03-20",
"2026-05-10",
"2026-07-01"
],
"Sales": [
45000,
65000,
72000,
85000,
95000
]
})
orders["Order_Date"] = pd.to_datetime(
orders["Order_Date"]
)Once the column has been converted to a proper datetime type, you can filter by date:
result = orders.loc[
orders["Order_Date"] > "2026-03-01"
]
print(result)You can also select a date range:
result = orders.loc[
orders["Order_Date"].between(
"2026-02-01",
"2026-05-31"
)
]
print(result)Date filtering is essential for monthly reports, quarterly analysis, financial-year reporting, marketing campaigns, website traffic analysis, and operational monitoring.
You can also extract the year and month:
orders["Year"] = orders["Order_Date"].dt.year
orders["Month"] = orders["Order_Date"].dt.monthThen filter by year:
result = orders.loc[
orders["Year"] == 2026
]Or by month:
result = orders.loc[
orders["Month"] == 5
]When filtering dates, always verify that the column is actually stored as a datetime type. Comparing improperly formatted strings can produce misleading results.
Missing values also require special attention.
customers = pd.DataFrame({
"Name": [
"Aman", "Priya", "Rahul",
"Neha", "Karan"
],
"City": [
"Dehradun",
None,
"Delhi",
"Dehradun",
"Haridwar"
],
"Purchase": [
50000,
65000,
None,
85000,
72000
]
})To find records where City is missing:
result = customers.loc[
customers["City"].isna()
]
print(result)To find records where City is available:
result = customers.loc[
customers["City"].notna()
]
print(result)The same approach works for numerical columns:
missing_purchase = customers.loc[
customers["Purchase"].isna()
]
print(missing_purchase)You can combine missing-value checks with other conditions:
result = customers.loc[
customers["City"].notna() &
customers["Purchase"].notna()
]
print(result)This can be useful when preparing a reliable dataset before calculating KPIs.
Advanced filtering becomes even more useful when the condition itself depends on another calculation, a group-level value, or several business rules. In practical Data Analytics, filtering is often used as part of a larger workflow rather than as an isolated operation.
A typical workflow can look like this:
Load Data
↓
Inspect Data
↓
Clean Data
↓
Create Conditions
↓
Filter Records
↓
Select Required Columns
↓
Validate Result
↓
Calculate KPI
↓
Create ReportThe quality of the final analysis depends heavily on whether the filtering step correctly defines the population being analyzed.
Consider a sales DataFrame containing completed, pending, cancelled, and returned orders:
orders = pd.DataFrame({
"Order_ID": [1001, 1002, 1003, 1004, 1005, 1006],
"Status": [
"Completed",
"Pending",
"Cancelled",
"Completed",
"Returned",
"Completed"
],
"Region": [
"North",
"North",
"South",
"South",
"East",
"North"
],
"Sales": [
85000,
65000,
45000,
120000,
55000,
150000
]
})Suppose management wants the average revenue from completed orders only.
completed_average = orders.loc[
orders["Status"] == "Completed",
"Sales"
].mean()
print(completed_average)This is different from:
overall_average = orders["Sales"].mean()
print(overall_average)The first calculation includes only completed transactions. The second includes every transaction, regardless of status.
This distinction is extremely important in professional analytics. A KPI is meaningful only when the analyst understands exactly which records contributed to it.
For example, a company may define “net sales” as completed transactions only. Another organization may include pending transactions in a forecast. A third organization may subtract returns. The filtering logic must follow the documented business definition.
Filtering can also be combined with query(), which provides a convenient expression-based syntax.
result = orders.query(
"Status == 'Completed' and Sales > 90000"
)
print(result)You can use multiple conditions:
result = orders.query(
"Region in ['North', 'South'] and Sales >= 80000"
)
print(result)Variables can also be supplied to a query using the @ symbol:
minimum_sales = 80000
result = orders.query(
"Sales >= @minimum_sales"
)
print(result)This allows the filtering threshold to be changed without modifying the query itself.
For example:
minimum_sales = 100000
result = orders.query(
"Sales >= @minimum_sales"
)The same analytical logic can now be reused with different thresholds.
However, query() is not automatically better than loc. Both are useful. The choice depends on readability, complexity, and the surrounding workflow.
For example:
orders.loc[
(orders["Status"] == "Completed") &
(orders["Sales"] > 90000)
]and:
orders.query(
"Status == 'Completed' and Sales > 90000"
)express similar logic.
Another advanced technique is filtering records based on a group-level benchmark.
Consider regional sales:
regional_sales = pd.DataFrame({
"Region": [
"North", "North", "North",
"South", "South", "South",
"East", "East"
],
"Sales": [
80000, 120000, 100000,
60000, 90000, 75000,
50000, 70000
]
})Suppose you want to identify transactions that are above the average sales for their own region.
First calculate the regional average using transform():
regional_sales["Region_Average"] = (
regional_sales
.groupby("Region")["Sales"]
.transform("mean")
)
print(regional_sales)Each row now contains the average sales for the region to which that row belongs.
You can filter transactions above their regional average:
result = regional_sales.loc[
regional_sales["Sales"] >
regional_sales["Region_Average"]
]
print(result)This is more advanced than comparing every transaction with one global average because each row is evaluated against the appropriate regional benchmark.
This type of filtering can be useful in sales analysis, employee performance analysis, school performance analysis, healthcare comparisons, and many other domains where observations belong to groups.
You can also filter using calculated percentages.
Suppose a product DataFrame contains revenue and total regional revenue:
products = pd.DataFrame({
"Product": [
"Laptop",
"Mobile",
"Tablet",
"Monitor"
],
"Sales": [
150000,
90000,
60000,
40000
]
})
total_sales = products["Sales"].sum()
products["Sales_Percentage"] = (
products["Sales"] / total_sales * 100
)Now identify products contributing more than 20 percent of total sales:
result = products.loc[
products["Sales_Percentage"] > 20
]
print(result)This is an example of creating a derived metric and then filtering based on that metric.
Filtering can also be used to investigate potentially unusual observations.
For example, the interquartile range can be used to identify potential numerical outliers:
q1 = products["Sales"].quantile(0.25)
q3 = products["Sales"].quantile(0.75)
iqr = q3 - q1
lower_limit = q1 - 1.5 * iqr
upper_limit = q3 + 1.5 * iqr
outliers = products.loc[
(products["Sales"] < lower_limit) |
(products["Sales"] > upper_limit)
]
print(outliers)This technique identifies observations outside the conventional IQR boundaries.
However, an analyst should never automatically assume that every outlier is an error. A very large transaction may be completely legitimate. Filtering is used here for investigation, not necessarily deletion.
Another important filtering problem involves duplicate records.
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 duplicated rows using:
duplicates = customers.loc[
customers.duplicated()
]
print(duplicates)If duplication should be evaluated using Customer_ID:
duplicates = customers.loc[
customers.duplicated(
subset="Customer_ID",
keep=False
)
]
print(duplicates)The keep=False option marks every record belonging to the duplicate group.
This is useful during data-quality investigations because it allows you to inspect all potentially duplicated records before deciding what should happen to them.
Filtering can also identify invalid numerical values.
For example, if quantity should always be positive:
invalid_quantity = sales.loc[
sales["Quantity"] <= 0
]
print(invalid_quantity)If sales should never be negative:
invalid_sales = sales.loc[
sales["Sales"] < 0
]
print(invalid_sales)These filters are useful during data validation before analysis begins.
A strong analytical workflow should distinguish between business filtering and data-quality filtering.
Business filtering answers questions such as:
Data-quality filtering answers questions such as:
Both are important, but they serve different purposes.
When a filter returns no records, do not immediately assume that the code is wrong.
result = sales.loc[
(sales["Region"] == "North") &
(sales["Product"] == "Tablet") &
(sales["Sales"] > 200000)
]
print(result)The result may simply be empty because no transaction satisfies all three conditions.
To investigate, inspect the individual categories:
print(sales["Region"].value_counts())
print(sales["Product"].value_counts())
print(sales["Sales"].describe())You can also test each condition separately:
north = sales.loc[
sales["Region"] == "North"
]
print(north)Then:
north_tablet = north.loc[
north["Product"] == "Tablet"
]
print(north_tablet)Finally apply the sales threshold.
This step-by-step approach is useful for debugging complex analytical filters.
Another good practice is to validate the final filtered result.
result = sales.loc[
sales["Sales"] > 100000
]
print("Number of records:", len(result))
print(result.head())
print(result["Sales"].min())If the filtering rule requires Sales greater than ₹100,000, the minimum selected value should satisfy that requirement, assuming the column does not contain unexpected missing values.
You can even use an assertion:
assert (
result["Sales"] > 100000
).all()This provides a simple automated check that every selected row satisfies the intended condition.
Validation becomes increasingly important when filtering is part of an automated reporting or dashboard pipeline.
A useful professional pattern is:
filtered = sales.loc[
sales["Region"].isin([
"North",
"South"
])
]
filtered = filtered.loc[
filtered["Sales"] >= 75000
]
filtered = filtered[
[
"Order_ID",
"Customer",
"Product",
"Sales"
]
]
print(filtered)This approach separates the filtering stages and makes the transformation easy for another analyst to understand.
In smaller expressions, the same logic can be combined:
filtered = sales.loc[
sales["Region"].isin([
"North",
"South"
]) &
(sales["Sales"] >= 75000),
[
"Order_ID",
"Customer",
"Product",
"Sales"
]
]Both approaches can be valid. Choose the version that provides the clearest and most maintainable analytical code.
Advanced filtering can also be used to create more meaningful analytical subsets by combining conditions with calculated metrics, business rules, and validation checks. At this stage, the objective is not only to make Pandas return rows, but to make sure the returned rows represent the exact population required by the analysis.
Consider the following customer dataset:
customers = pd.DataFrame({
"Customer_ID": [
101, 102, 103, 104, 105,
106, 107, 108, 109, 110
],
"Name": [
"Aman", "Priya", "Rahul", "Neha", "Karan",
"Sonia", "Arjun", "Meena", "Ravi", "Pooja"
],
"City": [
"Dehradun", "Delhi", "Dehradun", "Haridwar",
"Delhi", "Dehradun", "Haridwar", "Delhi",
"Dehradun", "Delhi"
],
"Age": [
24, 31, 28, 35, 42,
26, 30, 39, 27, 45
],
"Purchase": [
45000, 85000, 125000, 65000, 150000,
72000, 95000, 110000, 58000, 135000
],
"Status": [
"Active", "Active", "Active", "Pending",
"Active", "Inactive", "Active", "Active",
"Pending", "Active"
]
})Suppose the business defines a target customer as someone who is active, at least 25 years old, and has purchased at least ₹70,000.
result = customers.loc[
(customers["Status"] == "Active") &
(customers["Age"] >= 25) &
(customers["Purchase"] >= 70000)
]
print(result)This is a good example of translating a business definition into Boolean logic.
The business definition is:
Active
AND
Age >= 25
AND
Purchase >= 70000The Pandas expression follows exactly the same logical structure.
Now suppose the business changes the requirement and wants customers from either Dehradun or Delhi.
result = customers.loc[
(customers["Status"] == "Active") &
customers["City"].isin([
"Dehradun",
"Delhi"
]) &
(customers["Age"] >= 25) &
(customers["Purchase"] >= 70000)
]
print(result)Notice how isin() allows multiple category values to be treated as one condition.
You can also store the conditions separately:
active = customers["Status"] == "Active"
selected_cities = customers["City"].isin([
"Dehradun",
"Delhi"
])
age_condition = customers["Age"] >= 25
purchase_condition = customers["Purchase"] >= 70000
result = customers.loc[
active &
selected_cities &
age_condition &
purchase_condition
]
print(result)This version is particularly useful when building longer analytical scripts.
Each condition can be tested independently:
print(active.sum())
print(selected_cities.sum())
print(age_condition.sum())
print(purchase_condition.sum())The sum() of a Boolean Series counts the number of True values. This provides a quick way to understand how many records satisfy each individual condition.
For example, if the first condition has 8 True values and the final result contains only 4 rows, you know that additional conditions reduced the population.
This type of intermediate validation can be extremely useful when working with large datasets.
Filtering can also be performed relative to a benchmark calculated from the data.
For example, find customers whose purchase value is above the average purchase:
average_purchase = customers["Purchase"].mean()
result = customers.loc[
customers["Purchase"] > average_purchase
]
print("Average Purchase:", average_purchase)
print(result)Because the average is calculated dynamically, the same code can work with a different dataset without changing the threshold manually.
You can use the median as another benchmark:
median_purchase = customers["Purchase"].median()
result = customers.loc[
customers["Purchase"] > median_purchase
]
print("Median Purchase:", median_purchase)
print(result)The mean and median answer different analytical questions and can produce different populations when the data contains extreme values.
Another useful technique is filtering based on a calculated column.
Suppose the DataFrame contains purchase value and number of orders:
customer_orders = pd.DataFrame({
"Customer": [
"Aman", "Priya", "Rahul",
"Neha", "Karan"
],
"Orders": [2, 8, 5, 10, 3],
"Purchase": [
50000,
120000,
90000,
180000,
45000
]
})You can calculate average purchase per order:
customer_orders["Average_Order_Value"] = (
customer_orders["Purchase"] /
customer_orders["Orders"]
)Now filter customers whose average order value exceeds ₹20,000:
result = customer_orders.loc[
customer_orders["Average_Order_Value"] > 20000
]
print(result)This demonstrates an important pattern used throughout Data Analytics:
Raw Data
↓
Calculated Metric
↓
Condition
↓
Filtered PopulationThe calculated metric might be revenue per unit, profit margin, average order value, conversion rate, marks percentage, or any other analytical measure.
Filtering can also be used after grouping data.
Consider regional sales:
regional_sales = pd.DataFrame({
"Region": [
"North", "North", "North",
"South", "South", "South",
"East", "East", "East"
],
"Sales": [
80000, 120000, 100000,
60000, 90000, 75000,
50000, 70000, 85000
]
})Suppose you want to find transactions that are above the average transaction value of their own region.
regional_sales["Region_Average"] = (
regional_sales
.groupby("Region")["Sales"]
.transform("mean")
)
print(regional_sales)The transform() operation returns a value aligned with every original row.
You can then filter:
result = regional_sales.loc[
regional_sales["Sales"] >
regional_sales["Region_Average"]
]
print(result)This is more sophisticated than comparing every transaction against one overall average. Each transaction is compared against the benchmark appropriate to its own region.
Filtering can also identify records requiring data-quality investigation.
Suppose a quantity column should never contain zero or negative values:
invalid_quantity = sales.loc[
sales["Quantity"] <= 0
]
print(invalid_quantity)Similarly, negative sales may need investigation:
invalid_sales = sales.loc[
sales["Sales"] < 0
]
print(invalid_sales)However, never assume that a negative value is automatically wrong. In some financial datasets, negative values may represent refunds, returns, adjustments, or credit notes. The meaning of the value depends on the business context.
Filtering can also help investigate duplicate records.
duplicate_orders = sales.loc[
sales.duplicated(
subset="Order_ID",
keep=False
)
]
print(duplicate_orders)The purpose of this operation is investigation. You should determine whether the repeated Order_ID represents an actual duplicate or whether multiple rows legitimately belong to the same order.
Another common task is finding potential outliers.
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
potential_outliers = sales.loc[
(sales["Sales"] < lower_limit) |
(sales["Sales"] > upper_limit)
]
print(potential_outliers)This is an investigation filter based on the interquartile range method.
A potential outlier should be examined rather than automatically deleted.
For example, a sales transaction of ₹500,000 might initially look unusual, but it could be a legitimate bulk order from a large customer.
Filtering can therefore be used both to select data for analysis and to identify data that requires investigation.
When a complex filter unexpectedly returns no records, inspect the conditions individually.
condition_1 = sales["Region"] == "North"
condition_2 = sales["Product"] == "Tablet"
condition_3 = sales["Sales"] > 200000
print(condition_1.sum())
print(condition_2.sum())
print(condition_3.sum())This tells you how many records satisfy each individual rule.
You can then combine them gradually:
step_1 = sales.loc[
condition_1
]
step_2 = step_1.loc[
condition_2
]
step_3 = step_2.loc[
condition_3
]
print(step_1)
print(step_2)
print(step_3)This approach makes debugging much easier.
It also helps explain your analytical logic to another person. Instead of simply presenting a complex expression, you can demonstrate how the dataset was progressively reduced.
After applying a filter, always inspect the result:
print(result.shape)
print(result.head())
print(result.dtypes)If the result should contain only sales above ₹100,000, you can verify the condition:
assert (
result["Sales"] > 100000
).all()This assertion will pass only when every selected row satisfies the threshold.
Such validation is valuable when filtering becomes part of an automated data pipeline.
A final professional workflow might look like this:
filtered = sales.loc[
sales["Region"].isin([
"North",
"South"
]) &
(sales["Sales"] >= 75000)
]
filtered = filtered[
[
"Order_ID",
"Customer",
"Product",
"Sales"
]
]
filtered = filtered.sort_values(
by="Sales",
ascending=False
)
print(filtered)The workflow is clear:
Define Population
↓
Apply Conditions
↓
Select Required Columns
↓
Order Results
↓
Validate OutputThis is the type of structured thinking that separates simple Pandas syntax from practical Data Analytics.
Before moving to the next lesson, practice creating filters without immediately looking at the solution. Use a small DataFrame and write the business question in plain English first. Then identify the columns involved, define each condition, combine the conditions, select the required columns, and finally inspect the output.
The more consistently you follow this process, the easier complex Pandas filtering becomes.
In the next lesson, we will move from conditional filtering to another important DataFrame skill: sorting and ranking data for analytical decision-making.