pandas practice reference: choose and check each operation

From rows to a checked summary

Use the same small dataset to connect selection, grouping and validation. Predict the result before running it.

import pandas as pd
orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104],
    'customer_id': [1, 1, 2, 3],
    'amount_usd': [50, 30, 80, 20],
    'status': ['completed', 'returned', 'completed', 'completed']
})
assert orders['order_id'].is_unique
completed = orders.loc[orders['status'].eq('completed')].copy()
summary = (completed.groupby('customer_id', as_index=False)
    .agg(order_count=('order_id', 'size'), revenue_usd=('amount_usd', 'sum'))
    .sort_values(['revenue_usd', 'customer_id'], ascending=[False, True]))
assert summary['revenue_usd'].sum() == 150
print(summary.to_string(index=False))

Expected revenue totals: customer 2 → $80, customer 1 → $50, customer 3 → $20.

Inspect

head, info, describe

Check rows, dtypes and missing values before transformations.

Why can Series assignment create missing values?

loc, iloc, map

Use loc for label-based selection and iloc for positional selection. When assigning a Series to a DataFrame column, pandas matches index labels. Destination labels 10 and 20 do not match a new Series indexed 0 and 1, even when both contain two rows.

Match separate sources using their documented business keys. Removing labels with to_numpy() makes assignment positional, which can attach values to the wrong records. In a two-order example, swapping $75 and $50 preserves the $125 total while reversing the US and UK attribution. Check record-to-value pairs as well as totals.

Follow the tested Series assignment example and pandas indexing guide. Do not fill alignment-generated missing values with zero before investigating their cause.

Filter

eq, isin, between

Check boundaries and missing inputs.

Count

value_counts

Use dropna=False when missing categories should appear.

Why can a grouped total lose records?

groupby, agg

State the output grain and choose the missing-key rule explicitly. Grouping excludes NA keys by default; use dropna=False when unassigned records must remain. Reconcile both row counts and amounts. Keeping a missing key does not repair missing amounts. Try the tested $300 regional revenue example.

Broadcast

groupby, transform

Return a group statistic aligned to each input row.

Can a validated merge still assign the wrong customer?

merge

Yes. Validate checks the specified key relationship, but a null key can match another null key even when many_to_one passes. Define whether missing IDs are allowed to identify anything, inspect unmatched rows, and check that unknown IDs receive no customer attributes. Row counts and revenue totals alone cannot detect every incorrect match. See the worked null-key attribution example and duplicate-lookup validation exercise.

Combine

concat

Check column/index alignment and duplicates.

Sort

sort_values

Add a stable second key when ties matter.

When is forward filling a missing value justified?

isna, ffill, fillna, dropna

Forward fill is appropriate only when the source defines a value as persisting until an update. Sort chronologically and fill within the entity key. A missing measurement or revoked value must not silently inherit an earlier state.

In a tested USD example, filling a whole column assigns a US account’s $100 limit to a German account with no known limit. Grouped filling keeps Germany missing and carries $100 only within the US account. Preserve the raw column and identify carried values separately. A row-count limit does not enforce a maximum age; check the source timestamp when values expire.

Follow the tested account-boundary example and pandas 2.2 grouped-fill reference. For sums, a known subtotal may still be incomplete: min_count=1 does not require every input to be known.

Should every duplicate key be deleted?

duplicated, drop_duplicates

No. First define the business key and expected row grain. Use duplicated(keys, keep=False) to inspect all records sharing a key. Identical replayed payloads and conflicting amounts require different decisions; repeated order IDs may also represent legitimate order lines.

In a fictional USD extract, a US order appears twice at $75, a UK order appears at $50 and $60, and a German order appears once at $40. After removing the confirmed identical replay, quarantine both conflicting UK versions. The accepted $115 is a subtotal, not complete revenue. Default key deduplication silently chooses the first UK amount; applying drop_duplicates(keys, keep=False) directly removes every repeated key and leaves only $40.

Preserve exception records and document any version-precedence rule. Follow the tested duplicate-key audit and official drop_duplicates reference. Check missing keys separately before accepting records.

How should text flags become true or false?

str.strip, str.lower, map

Normalize whitespace and case, then map only the values allowed by the source specification. Do not parse text flags with astype(bool): both "False" and "0" are nonempty strings and become true. Keep the raw column and distinguish missing inputs from unexpected values.

In a six-record example, two true values, two false values, and two unknown statuses give 33.33% confirmed among all records but 50% among known statuses. State the denominator and report unknowns. Follow the tested CSV flag parsing example and Python truth-value rules.

For substring filters with str.contains, use na=False only when missing means no match.

Does utc=True infer the source timezone?

to_datetime, dt.tz_localize, dt.tz_convert

No. For timestamps without a timezone, to_datetime(..., utc=True) assumes the clock time is already UTC. If the source specifies local time, parse the documented format, localize to the source timezone, then convert to UTC. Do not infer the timezone from a customer’s country.

For July 15, 2026 at 09:00, an America/New_York source becomes 13:00 UTC; an Europe/London source becomes 08:00 UTC. Direct parsing with utc=True produces 09:00 UTC for either naive input. Explicit offsets instead identify an instant that can be converted directly.

Keep the raw values, distinguish missing inputs from parse failures, and choose explicit rules for ambiguous or nonexistent daylight-saving times. Follow the tested local-time conversion example and official pandas datetime reference.

Reshape

pivot, pivot_table, melt

Check whether repeated keys require aggregation.

Can the closest timestamp be the wrong match?

merge_asof

Yes. A nearest match can select a later record. For a reference value effective at an event time, use a backward match, retain the matched timestamp, and set an age limit based on the source contract. Sort both inputs by their time merge key and group by the relevant entity keys.

For a fictional US order at 10:00 UTC, prices of $100 at 09:50 and $120 at 10:01 make the future $120 price the nearest. A backward match uses $100. With a 30-minute tolerance, a two-hour-old German reference stays missing; a UK record at exactly 10:00 can match when exact matches are allowed. The tolerance is an exercise assumption, not a universal rule.

Effective time does not establish when information became available. Audit ingestion timestamps separately for historical decision analysis, and resolve conflicting reference rows before matching. Follow the tested time-matching example and official merge_asof reference.

Time comparisons

shift, pct_change

Sort within each series and check missing periods and zero denominators.

Percentage change needs a denominator

import pandas as pd
revenue = pd.Series([100.0, 120.0, 0.0, 50.0])
previous = revenue.shift()
growth_pct = (revenue - previous).div(previous.where(previous.ne(0))).mul(100)
assert pd.isna(growth_pct.iloc[0]) and pd.isna(growth_pct.iloc[3])
assert growth_pct.iloc[1] == 20 and growth_pct.iloc[2] == -100
print(growth_pct.tolist())

The first and last percentages are missing, not zero. For monthly data, sort by month and check that the previous row really represents the previous month. Within several products, shift within each product rather than across the combined table.