4.3 Filtering, Sorting, and Assigning Values

Safe selection gives us a coordinate contract. We can now build analytical transformations from three explicit stages: construct a condition, choose and order records, then create or update fields. Keeping those stages visible makes a pipeline easier to review and test.

import pandas as pd

deliveries = pd.DataFrame({
    "order_id": ["A104", "A105", "A106", "A107", "A108", "A109"],
    "zone": ["North", "South", "West", "South", "North", "Central"],
    "minutes": pd.Series([31, 52, 44, 38, 47, None], dtype="Int64"),
    "fee": [8.5, 9.0, 7.5, 8.0, 10.0, 6.5],
    "cancelled": pd.Series([False, False, False, True, False, False], dtype="boolean"),
})

Compose filters from named conditions

Suppose an operations team needs active North or South orders taking at least 40 minutes. Name each condition before combining it:

slow = deliveries["minutes"].ge(40)
target_zone = deliveries["zone"].isin(["North", "South"])
active = ~deliveries["cancelled"].fillna(False)

mask = slow & target_zone & active
alerts = deliveries.loc[mask]

The method .ge(40) is equivalent to >= 40 but can read naturally in a chain. .isin(...) expresses membership without a long series of equality checks. Filling a missing cancellation flag with False is a policy decision: here it means “treat unknown as active.” A safer operational policy might reject unknown flags or treat them as cancelled. Name the policy in code and tests.

Named conditions create a useful debugging trace:

audit = deliveries.assign(
    slow=slow,
    target_zone=target_zone,
    active=active,
    selected=mask,
)

If one order is unexpectedly excluded, the audit columns show which condition failed. Remove diagnostic columns from published output, but keep the ability to reproduce them.

DataFrame.query can express some filters compactly:

minimum = 40
alerts = deliveries.query(
    "minutes >= @minimum and zone in ['North', 'South'] and not cancelled"
)

Use query when its expression remains clear to the team. Ordinary masks are easier when column names are dynamic, missing-value policy is complex, or intermediate conditions need inspection.

Sort by declared keys and tie rules

sort_values returns a sorted DataFrame and leaves the source order unchanged unless inplace=True is requested:

queue = alerts.sort_values(
    ["minutes", "order_id"],
    ascending=[False, True],
    na_position="last",
)

The primary key is minutes descending. The secondary key is order ID ascending, which makes tied durations deterministic. Without a declared tie rule, equal-key rows may appear in an order that depends on earlier operations.

For a single sort key, kind="stable" preserves input order among ties:

stable_by_minutes = deliveries.sort_values(
    "minutes",
    kind="stable",
    na_position="last",
)

Use sort_index when the labels themselves define the desired order. Do not sort merely to make output look tidy: row order may encode ranking, chronology, or a sampling sequence. State why the order exists.

The filtering lab runs a small policy language rather than exposing threshold controls. Write named predicates, combine them with Boolean logic, and declare sort keys. A row-level audit trace shows every true and false intermediate mask alongside the final stable queue, so unexpected exclusions can be diagnosed rather than merely observed.

Create derived columns with vectorized expressions

A whole-column assignment is direct and readable:

deliveries["cost_per_minute"] = (
    deliveries["fee"] / deliveries["minutes"]
)

pandas aligns the Series result to the DataFrame index. Missing minutes propagate into the derived column. A zero-minute record would produce an infinite value rather than a meaningful cost, so validate the denominator before trusting the calculation.

assign returns a new DataFrame and is convenient in a method chain:

analysis = (
    deliveries
    .assign(
        is_late=lambda frame: frame["minutes"].gt(40),
        cost_per_minute=lambda frame: frame["fee"].div(frame["minutes"]),
    )
    .sort_values("minutes", ascending=False, na_position="last")
)

Within assign, callables receive the DataFrame as transformed so far. Later assigned columns may refer to earlier ones in the same call, but avoid long dependency chains that hide the transformation order.

Do not use row-wise .apply(axis=1) for ordinary column arithmetic. Vectorized Series operations communicate intent better and usually avoid repeated Python function calls. Chapter 6 will compare map, apply, and vectorized transformations in depth.

Update a subset in one indexing operation

To update status for late, uncancelled orders, identify both row and column targets in one .loc operation:

late_active = (
    deliveries["minutes"].gt(40)
    & ~deliveries["cancelled"].fillna(False)
)

deliveries.loc[late_active, "status"] = "late"

This expression clearly says which object is modified, which rows qualify, and which column receives the value.

Avoid chained assignment:

# Wrong: assignment targets an intermediate object.
deliveries[deliveries["minutes"] > 40]["status"] = "late"

In pandas 3.x, Copy-on-Write (CoW) is the default. An object produced by indexing behaves as a separate object when modified, so chained assignment cannot update the original DataFrame and raises ChainedAssignmentError. Older pandas versions often emitted SettingWithCopyWarning because the result could be ambiguous. The durable rule across versions is the same: perform the update in one .loc operation.

If the goal is to build an independent working table, copy intentionally and then update it:

working = deliveries.loc[deliveries["zone"] == "South"].copy()
working.loc[:, "priority"] = "review"

Now source preservation is part of the code's contract rather than an accidental side effect.

The assignment lab is a stateful Copy-on-Write debugger. Type commands that create filtered objects or explicit copies, mutate them with .loc, or deliberately attempt chained assignment. Separate object tables and an execution trace reveal which cells changed and whether the original df was ever targeted.

Verify the affected scope

Assignments deserve tests just like calculations. Save the intended labels and compare before and after:

target_ids = deliveries.index[late_active]
untouched_ids = deliveries.index[~late_active.fillna(False)]
before_untouched = deliveries.loc[untouched_ids, "status"].copy()

deliveries.loc[late_active, "status"] = "late"

assert deliveries.loc[target_ids, "status"].eq("late").all()
assert deliveries.loc[untouched_ids, "status"].equals(before_untouched)

Be careful when assigning a Series: pandas aligns its index labels to the target rows. That is powerful when labels are correct, but a mismatched Series can fill unexpected missing values. Inspect or explicitly align both sides when the labels come from different sources.

So far, our DataFrames were created inside Python. The next section treats files as untrusted boundaries: parsing is a set of decisions, successful import is not validation, and export needs a round-trip contract.