pandas / Basic: labels, types and accepted input / Lesson 6

Sorting, filtering and deterministic outputs

Define a reproducible selected population and tie policy before taking top rows or exporting results.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain stable identity through filtering and index reset.
Choose deterministic tie-breaking and explicit missing placement.

A filtered table answers a narrower question

Filtering defines which observations participate in later calculations. Write the inclusion rule in business terms before translating it into a Boolean mask. Inventory rows with positive units and verified status form a different population from every row with a nonmissing quantity. Record excluded counts and reasons when coverage matters. Parentheses, missing mask values and label alignment can all affect the selected set. A matching row count is weak evidence if the identities differ. Keep an explicit expected identity list in small fixtures and include boundary values such as zero, exactly the threshold and an unknown condition.

Top rows require a tie policy

Sorting descending by score does not fully specify the order of tied rows. If a report takes the first two, input order or an unstable tie arrangement can change which entity appears. Add a meaningful deterministic secondary key, or explicitly choose a policy that retains all ties when that matches the requirement. A business identifier is suitable only when it is unique at the stated grain and its ordering is acceptable. Missing-score placement is another decision, not an invitation to impute an arbitrary value. State whether unknown scores are excluded, reviewed separately or sorted to a documented position.

Declare both primary and secondary order

The illustrative pandas frame has I2 and I1 tied at score nine, plus I3 at seven. Sorting score descending and item ascending produces I1 then I2 for the top two. This is a declared ranking rule, not a claim that item identity itself measures quality. If the source includes duplicate item IDs, the pair of sort keys may still fail to define a unique order. Validate identity first. The expected result is reasoned from the fixture and pandas sorting contract, not observed in a runtime test. Reordering the input should not change this result when the declared keys remain unique.

python Illustrative deterministic top-two selection

import pandas as pd

frame = pd.DataFrame({'item': ['I2', 'I1', 'I3'], 'score': [9, 9, 7]})
ranked = frame.sort_values(['score', 'item'], ascending=[False, True])
top = ranked.head(2)['item'].tolist()
assert top == ['I1', 'I2']
print(top)

Expected output

['I1', 'I2']

Expected order isI1 thenI2 under the explicit score/item rule; pandas was not executed.

Check expected ranking with plain records

The standard-library model sorts the same records with an explicit tuple key of negative score and item identifier. It confirms the intended ranking independently of pandas’s index and dtype behavior. It does not verify sort_values algorithms, nullable comparison or backend-specific ordering. This comparison is useful because a report can be reproducible yet rank the wrong population or use the wrong ascending direction. Test one threshold boundary and one tie that crosses the top-N cutoff. If all ties must be kept, a fixed head(N) result is the wrong business operation even when it is deterministic.

python Offline ranking-rule check

rows = [{'item': 'I2', 'score': 9}, {'item': 'I1', 'score': 9}, {'item': 'I3', 'score': 7}]
ranked = sorted(rows, key=lambda row: (-row['score'], row['item']))
top = [row['item'] for row in ranked[:2]]
assert top == ['I1', 'I2']
print(top)

Expected output

['I1', 'I2']

The plain-record result checks business tie-breaking only, not pandas sort implementation.

Treat output order and schema as part of the result

Filtering and sorting normally preserve source index labels unless explicitly reset. reset_index changes presentation structure and can retain or discard the old index according to its options; it should not erase the only business identifier. For export, specify columns, row order, index handling, numeric formatting and date representation where reproducibility requires them. A textual diff can otherwise change without a business change, while a hidden dtype change can survive a visually identical export. This lesson writes no external output and executes only plain-record ranking. pandas filtering, sorting and serialization remain unexecuted examples with explicit evidence limits.

Common errors and fixes

Top-N membership changes when input rows are shuffled.

Why it happens: Tied primary scores had no complete deterministic policy.
How to fix it: Specify a reviewed secondary key or all-ties policy.
Prevent it next time: Test a shuffled fixture with a cutoff-crossing tie.

Resetting the index destroys the only retained item identity.

Why it happens: Presentation reset was confused with safe identity replacement.
How to fix it: Preserve the business key explicitly before restructuring axes.
Prevent it next time: Assert identity coverage through filter, sort and export.

A filter returns the expected count but the wrong items.

Why it happens: Condition boundaries or label alignment were incorrect.
How to fix it: Compare expected included and excluded identities.
Prevent it next time: Validate set membership rather than row count alone.

Check your understanding

1. Is a deterministic head(2) valid when the requirement is to include every item tied for second place?

Show worked solution

Not necessarily. A fixed row limit can exclude legitimate ties. Define the business cutoff and tie policy first, then choose the corresponding ranking or threshold operation. Reproducibility is valuable but cannot make an incorrectly specified population analytically correct.

2. Why must an export specify index handling even when the report looks correct in a notebook?