Filtering & Sorting

Understanding Filtering & Sorting

Filtering answers “Which observations belong in this analysis?” Sorting answers “In what order should we inspect them?” A filter removes rows that do not satisfy a rule; sorting rearranges rows without changing their values or membership. Keeping these operations separate helps you distinguish a restricted analytical population from a different presentation of the same data.

In pandas, comparing a column with a value produces a Boolean Series: one true or false result per row. This is a Boolean mask. For example, `df['year'] == 2025` checks every row’s year. Passing that mask to `df.loc[mask]` selects matching rows. The mask retains index labels, so construct it from the DataFrame you intend to filter rather than treating it as an unrelated list of positions.

Combine conditions with `&` for elementwise AND, `|` for elementwise OR, and `~` for NOT. AND requires both conditions; OR requires at least one. Parenthesize each comparison because Python’s operator precedence otherwise changes how expressions are evaluated. For several allowed categories, `df['region'].isin(['East', 'West'])` expresses membership more clearly than repeated equality comparisons joined by OR.

Data types and missing values affect the meaning of your rule. Numeric amounts should be stored as numbers: text sorting can place '100' before '20'. A missing amount does not establish whether a transaction exceeds a threshold. Decide explicitly whether unknown values should be excluded, retained for investigation, or handled separately. With nullable Boolean masks, `.fillna(False)` makes an exclusion policy explicit without replacing missing amounts with zero.

Use `sort_values` to order rows by one or more columns. Ascending order is the default; `ascending=False` puts larger values first. Multiple sort columns resolve ties in sequence. An explicit unique identifier as the final key makes tied results reproducible. Missing sort values appear last by default; `na_position` controls their placement.

Filtering changes which observations support your conclusions. A list of large transactions describes those transactions, not typical sales. Sorting alone introduces no additional observations, and a preview is not a representative sample. These distinctions connect pandas’ documented Boolean indexing and sorting operations to sound interpretation: selection rules define scope, while ordering supports inspection.

Practical walkthrough

Start with a concrete question: “Which recorded transactions from 2025 have known amounts above 1,000, ordered from largest to smallest?” Assume each row represents one transaction, amounts use the same currency, and transaction identifiers are unique. Without these assumptions, comparing amounts or interpreting rows as separate sales could be misleading.

The example constructs its data directly, so it requires pandas but no external file. It deliberately includes an older transaction, a missing amount, an amount exactly at the threshold, and a tie. These cases let you check the rule rather than only observe a plausible-looking result.

First, create the amount column using pandas’ nullable `Float64` type. This preserves numeric behavior while representing an unknown amount with `pd.NA`. Next, build the year and amount comparisons. Join them with AND because both requirements must hold. Apply `.fillna(False)` to the completed mask so an unresolved comparison cannot qualify a row.

Use `.loc` to select qualifying rows and the columns needed for the report. Keeping the identifier makes results traceable to their original records. Then sort by amount descending and identifier ascending. The second key provides a defined order for equal amounts; it does not imply one tied transaction is more important.

Finally, print the selected count and formatted records. Compare exclusions with the original data: the earlier year, missing amount, and boundary value should be absent. Filtering before sorting also avoids sorting excluded rows. For a larger result, apply `.head(10)` after sorting to inspect the ten largest qualifying transactions, not ten arbitrary rows from the input.

Worked example

import pandas as pd

sales = pd.DataFrame({
    'sale_id': ['S1', 'S2', 'S3', 'S4', 'S5', 'S6'],
    'year': [2025, 2024, 2025, 2025, 2025, 2025],
    'amount': pd.Series(
        [1500, 3000, pd.NA, 2200, 1500, 1000],
        dtype='Float64'
    )
})

mask = (
    (sales['year'] == 2025) & (sales['amount'] > 1000)
).fillna(False)

result = sales.loc[mask, ['sale_id', 'year', 'amount']]
result = result.sort_values(
    ['amount', 'sale_id'], ascending=[False, True]
)

print(f'Selected: {len(result)} of {len(sales)} transactions')
print('sale_id,year,amount')
for row in result.itertuples(index=False):
    print(f'{row.sale_id},{row.year},{row.amount:.2f}')

Expected output

Selected: 3 of 6 transactions
sale_id,year,amount
S4,2025,2200.00
S1,2025,1500.00
S5,2025,1500.00

S4 appears first because 2,200 is the largest qualifying amount. S1 and S5 both have amounts of 1,500, so their identifiers determine their order. S2 is excluded despite its larger amount because its year is 2024. S3 has an unknown amount and is excluded under the stated policy. S6 equals 1,000, so it fails the strict greater-than comparison; using `>=` would include it. The output describes three qualifying transactions, not all 2025 sales. Formatting amounts to two decimal places makes the display consistent without changing stored values. Neither selection nor sorting modifies the original `sales` DataFrame in this example.

Common mistakes and how to avoid them

• Using Python’s `and` or `or` between column comparisons.

  Use `&` or `|` and parenthesize each comparison. Python’s logical keywords expect a single truth decision, not one decision per row.

• Calling `sort_values` without keeping its returned result.

  Assign the result to a variable. By default, `sort_values` returns a sorted DataFrame rather than rearranging the original in place.

• Taking `.head(10)` before sorting to find the largest transactions.

  Filter, sort the complete qualifying subset, then take `.head(10)`. Otherwise, you rank only the first ten input rows.

• Assuming filtering resets row labels.

  Filtering preserves original index labels. Use `.iloc[0]` for the first remaining row, or deliberately use `.reset_index(drop=True)` when fresh positional labels are needed.

Review questions

Further reading

• pandas user guide
• scikit-learn user guide