Filtering Rows in pandas

Filtering is where business language becomes executable logic. “Completed orders above $100 during July” sounds straightforward, but the implementation must define missing quantities, date boundaries, returned orders, and whether the original DataFrame should change.

Problem

An operations team wants completed orders with an extended value of at least $100, placed in July 2025. You need a filter that is readable, testable, and explicit about missing numeric values.

Dataset used

Use the Orders Dataset and download orders.csv. It contains 250 rows, several statuses, missing quantity and price values, two duplicate records, and unmatched customer and product keys.

Code: load and prepare the columns

from pathlib import Path

import pandas as pd

orders = pd.read_csv(
    Path("data") / "orders.csv",
    dtype={"order_id": "Int64", "customer_id": "Int64", "product_id": "Int64"},
    parse_dates=["order_date"],
)

orders["quantity"] = pd.to_numeric(orders["quantity"], errors="coerce")
orders["unit_price"] = pd.to_numeric(orders["unit_price"], errors="coerce")
orders["order_value"] = orders["quantity"] * orders["unit_price"]

errors="coerce" converts invalid numeric text to missing values. That is useful only when the pipeline also measures conversion failures; otherwise coercion can hide a source defect. Here the blanks are deliberate and remain NaN. Multiplication propagates them, which prevents an invented order value.

Code: build named boolean conditions

is_completed = orders["status"].eq("Completed")
is_large = orders["order_value"].ge(100)
is_in_july = orders["order_date"].between(
    "2025-07-01",
    "2025-07-31",
    inclusive="both",
)

large_july_orders = orders.loc[
    is_completed & is_large & is_in_july,
    ["order_id", "customer_id", "order_date", "order_value", "status"],
].copy()

print(large_july_orders.head())

Named masks make the rule reviewable. .loc[rows, columns] applies the row condition and selects the output contract in one place. .copy() creates an independent result before later enrichment and avoids ambiguous chained assignment.

Output

The precise rows are deterministic, but the more important validation is the condition itself:

Validation	Expected result
Status values	Only Completed
Date range	July 1 through July 31, inclusive
Minimum order value	At least 100
Missing order values	Excluded by the comparison

assert large_july_orders["status"].eq("Completed").all()
assert large_july_orders["order_date"].between("2025-07-01", "2025-07-31").all()
assert large_july_orders["order_value"].ge(100).all()

Assertions make example intent concrete. In a production pipeline, replace bare assertions with validation that records counts and raises domain-specific errors, because Python can disable assertions.

Explanation: combining conditions

Use & for AND, | for OR, and ~ for NOT. Each comparison must be parenthesized when written inline because Python operator precedence is not the same as conversational logic.

review_queue = orders.loc[
    (orders["status"].isin(["Pending", "Returned"]))
    & (orders["order_value"].fillna(0).ge(75))
]

not_cancelled = orders.loc[~orders["status"].eq("Cancelled")]
missing_amount = orders.loc[orders["quantity"].isna() | orders["unit_price"].isna()]

fillna(0) is a business decision, not a generic missing-value fix. In the review-queue example it says missing amounts do not meet the threshold. For revenue reporting, silently treating unknown value as zero could understate totals. Keep an explicit missing-data queue when completeness matters.

String filters need similar care. Use .str methods with na=False when null strings should not match. Normalize casing only when the comparison is meant to be case-insensitive.

completed_case_insensitive = orders.loc[
    orders["status"].str.casefold().eq("completed")
]

Common mistakes

• Writing and or or between Series instead of & and |.
• Omitting parentheses around inline comparisons.
• Filtering date strings lexically without first validating a stable ISO format or parsing dates.
• Filling every null with zero and changing the business meaning.
• Modifying a filtered view through chained indexing.
• Using .query() with untrusted input or awkward column names without understanding its expression rules.
• Forgetting that duplicated source rows also duplicate filtered results.

Try it yourself

Create three outputs: pending orders worth at least $150, returned orders from the final two months in the file, and rows where quantity or unit price is missing. For each output, select only useful review columns and print its count.

Then compare the number of filtered rows before and after drop_duplicates(). Decide whether the two duplicate records should be removed using the full row or only order_id. Write the business-key decision down before changing the data; a duplicate identifier with different attributes is a different problem from an exact repeated row.

Production notes

Filters should be observable. Record input rows, rows passing each major rule, rows rejected for quality reasons, and final output rows. A sudden fall to zero can then be distinguished from a genuinely quiet business day. Keep rejection reasons mutually understandable: “cancelled status” and “missing price” describe different conditions and may require different owners.

Avoid embedding a long chain of unexplained literals in one expression. Put reporting windows, accepted statuses, and thresholds in named configuration when they vary by run. Validate configuration before applying it, especially start and end dates. Half-open time ranges—start inclusive and next-period start exclusive—are often safer for timestamps than constructing a final second of a month.

Performance also depends on when columns are created. Vectorized masks are normally preferable to row-by-row apply, but do not calculate dozens of expensive derived columns before eliminating obviously irrelevant records. At the same time, preserve fields needed to explain why a row was included or excluded. Optimization should not remove the audit trail.