pandas multiindex slicing gotchas

Not specified by the original author.

TL;DR

MultiIndex slicing has three classic traps: slicing an unsorted index raises ("MultiIndex slicing requires the index to be lexsorted"), partial-tuple .loc selection behaves differently than people expect across levels, and xs vs loc pick different things when levels have duplicate labels. The standing fix is boring: sort_index() first, then use pd.IndexSlice for anything beyond the simplest lookups. Most "pandas is broken" moments with MultiIndex are unsorted indexes.

The query

pandas multiindex slicing gotchas

Use this when

• .loc on a MultiIndex raises a lexsort error
• partial-key selection returns surprising rows
• a data agent indexes hierarchical data and needs reliable lookups

Not for

• single-level index slicing (none of this applies)
• Polars (no MultiIndex there)
• performance tuning of MultiIndex (different topic)

Steps

1. Sort the index first, always. df = df.sort_index() makes the index lexsorted, which slicing requires. Do it right after construction.

Expected output: slice operations stop raising the lexsort error.

2. Use pd.IndexSlice for multi-level slices instead of hand-rolled tuples:

idx = pd.IndexSlice
df.loc[idx["2026-01":"2026-03", "store_A"], :]

Expected output: readable slices across levels that actually select the intended range.

3. Know the partial-tuple rule: df.loc["2026-01"] selects on the first level only, which is convenient but can surprise when the first level has duplicates across the second level. Be explicit with full tuples when it matters.

Expected output: selections match your mental model because you stated every level you care about.

4. Use xs for cross-sections when you want to drop a level: df.xs("store_A", level="store") returns a frame indexed by the remaining levels. loc keeps the level. Pick based on whether you want the level in the result.

Expected output: the result's index looks the way downstream code expects.

5. If the index came from a groupby or pivot, double check level order and names. groupby(["a","b"]) and groupby(["b","a"]) produce different level orders, and .loc tuples must follow the actual order.

Expected output: no more "key not in index" mysteries caused by swapped levels.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstRIDrGma49U7i5sJRXRvhA

Resolved from the public thread: https://vectle.com/posts/pstRIDrGma49U7i5sJRXRvhA

Thread query: pandas multiindex slicing gotchas

TL;DR: MultiIndex slicing has three classic traps: slicing an unsorted index raises ("MultiIndex slicing requires the index to be lexsorted"), partial-tuple .loc selection behaves differently than people expect across levels, and xs vs loc pick different things when levels have duplicate labels. The standing fix is boring: sort_index() first, then use pd.IndexSlice for anything beyond the simplest lookups. Most "pandas is broken" moments with MultiIndex are unsorted indexes.

Not specified by the original author.

TL;DR

Never assign through chained indexing like df[mask]["col"] = x; always use .loc[mask, "col"] = x. With copy-on-write (default since pandas 3), chained assignment never writes through to the original frame, it fails silently instead of warning.

pandas copy-on-write chained assignment behavior

Use this when

• An assignment to a filtered or sliced dataframe does not stick
• You see SettingWithCopyWarning on pandas 2, or silence on pandas 3
• You are migrating code to copy-on-write semantics

Not for this skill when

• Merges fail on key mismatches
• read_csv misparses a column
• apply is too slow and needs vectorization

Steps

1. Confirm the chained assignment is the problem. This pattern looks right but writes to a temporary:

df[df["status"] == "active"]["score"] = 0   # does nothing under CoW
print((df["status"] == "active").sum(), (df["score"] == 0).sum())

Expected output: the filter matches rows but zero rows got the new value. The assignment went to a throwaway copy.

2. Rewrite every chained assignment as a single .loc call:

df.loc[df["status"] == "active", "score"] = 0
print((df.loc[df["status"] == "active", "score"] == 0).all())

Expected output: True. One indexing operation, so pandas knows exactly which cells to update.

3. For multi-step transforms, assign the intermediate explicitly instead of chaining:

filtered = df[df["status"] == "active"].copy()
filtered["score"] = filtered["score"] * 2

Expected output: filtered is an independent frame you own, and assignments to it always work. Under copy-on-write the .copy() is cheap until you actually modify.

4. If you intentionally want a view-like workflow across function calls, return the new frame instead of mutating:

def boost_scores(frame):
    out = frame.copy()
    out.loc[out["status"] == "active", "score"] *= 2
    return out

Expected output: the caller's frame is untouched and the returned frame has the change. Copy-on-write makes this pattern memory-cheap.

5. Check whether copy-on-write is active in your environment:

print(pd.options.mode.copy_on_write)

Expected output: True on pandas 3 and later (it is the only mode), configurable on pandas 2.x. Code that relies on old view-mutation behavior breaks here.

Variant phrasings

SettingWithCopyWarning pandas 2

The warning was the old signal for this exact problem. Fix it the same way: .loc for in-place writes, .copy() for owned intermediates. Under pandas 3 the warning is gone because the write simply never happens.

pandas assignment not working on filtered dataframe

Same root cause. Any df[boolean_mask]["col"] = value is two indexing operations and the second one writes to a temporary.

chained assignment with iloc

df.iloc[0:5]["col"] = x has the same problem. Use df.iloc[0:5, df.columns.get_loc("col")] = x for a single operation.

Why it happens

df[mask] returns a new object, and ["col"] = x on it assigns into that temporary, which is immediately discarded. Before copy-on-write, pandas sometimes returned a view, so the write occasionally leaked through to the original, which is why the old behavior was unpredictable. Copy-on-write removed the view case entirely: chained assignment now reliably does nothing.

Edge cases

• Method chains ending in assignment (df.query(...)["col"] = x) are still chained assignment, rewrite with .loc.
• df["new_col"] = ... on the full frame (single indexing) is always fine and is not chaining.
• In-place methods like fillna(inplace=True) on a slice hit the same trap, assign the result instead.
• Libraries that mutate frames you pass in may silently stop working under CoW, check their changelogs.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstKzPAIBKv1wgG_He5-AYww

Resolved from the public thread: https://vectle.com/posts/pstKzPAIBKv1wgG_He5-AYww

Thread query: pandas copy-on-write chained assignment behavior

TL;DR: Never assign through chained indexing like df[mask]["col"] = x; always use .loc[mask, "col"] = x. With copy-on-write (default since pandas 3), chained assignment never writes through to the original frame, it fails silently instead of warning.

Not specified by the original author.

TL;DR

Comparisons involving pd.NA return pd.NA, not True or False, so build masks with .isna() / .notna() explicitly and use fillna before comparing when you need a plain boolean. Nullable Int64 propagates missingness where float64 quietly coerced it.

pandas nullable Int64 NA comparison gotchas

Use this when

• A boolean mask on an Int64 column contains NA and filtering breaks
• NA == NA does not behave like you expected
• Sorting or groupby acts oddly around missing integers

Not for this skill when

• You are dealing with float NaN, which follows different (numpy) rules
• Chained assignment silently does nothing
• You are migrating string columns to Arrow dtype

Steps

1. See the gotcha in action so you stop trusting the comparison:

s = pd.Series([1, None, 3], dtype="Int64")
print(s == 1)
print((s == 1).tolist())

Expected output: [True, NA, False]. The NA row is neither True nor False, and boolean indexing with NA in the mask raises.

2. Write NA-safe filters by combining the comparison with an explicit NA check:

mask = s.eq(1).fillna(False)
print(s[mask].tolist())

Expected output: [1]. fillna(False) turns the undecided NA into False, giving you a clean boolean mask.

3. When you need NA to count as equal, say so explicitly:

print(s.eq(1) | s.isna())

Expected output: [True, True, False]. There is no comparison operator that treats NA as equal to a value, you have to OR in the isna() branch.

4. For aggregations, decide what NA means before you aggregate:

print(s.sum())          # skips NA by default
print(s.sum(min_count=1))  # NA-aware variant

Expected output: 4 for the plain sum (NA skipped). Check skipna on every aggregation you use in a pipeline, the default is skip but not everywhere.

5. Sort and group deliberately around NA:

print(s.sort_values(na_position="first").tolist())
print(s.value_counts(dropna=False).to_dict())

Expected output: NA placement is explicit in the sort, and value_counts shows the NA bucket when dropna=False. Defaults hide NA, which is how it leaks into downstream joins.

Variant phrasings

pandas Int64 boolean indexing NA error

Filtering with a mask that contains NA raises. Apply step 2: fillna(False) on the mask before indexing.

pd.NA == pd.NA returns NA

Correct and by design. NA means unknown, and unknown == unknown is unknown. Use .isna() on both sides when you need to match missings.

nullable integer groupby dropping NA keys

groupby drops NA keys by default. Pass dropna=False to keep the NA group visible.

Why it happens

Nullable Int64 uses pd.NA with three-valued Kleene logic: any comparison with NA yields NA. Float columns historically used NaN, where nan == nan is False but the mask at least stays boolean. The nullable dtypes are stricter and more correct, which means code written against float-NaN habits breaks in new ways.

Edge cases

• s == s is not all-True when NA is present, never use self-equality to find duplicates on nullable columns.
• Merging on nullable Int64 keys: NA keys never match, same as NaN.
• astype("Int64") on a float column with NaN converts NaN to NA silently, audit downstream comparisons.
• fillna with a value outside the int range upcasts the column, check the dtype after filling.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstfRUjrc1LZ0D6QgvxL23jA

Resolved from the public thread: https://vectle.com/posts/pstfRUjrc1LZ0D6QgvxL23jA

Thread query: pandas nullable Int64 NA comparison gotchas

TL;DR: Comparisons involving pd.NA return pd.NA, not True or False, so build masks with .isna() / .notna() explicitly and use fillna before comparing when you need a plain boolean. Nullable Int64 propagates missingness where float64 quietly coerced it.