BUG: indexing with missing labels deprecation not applied to MultiIndex

[attack68]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample, a copy-pastable example

df = pd.DataFrame({("z","a"): [1, 2, 3], ("z","b"):[4, 5, 6]})
df.loc[pd.IndexSlice[:, pd.IndexSlice["z", ["a", "b", "c"]]]]  # works: DataFrame with columns 'a' and 'b'.

df = pd.DataFrame({("a"): [1, 2, 3], ("b"):[4, 5, 6]})
df.loc[pd.IndexSlice[:, pd.IndexSlice[["a", "b", "c"]]]]  # KeyError: missing label 'c'

Problem description

Should this behaviour be consistent?
A recent decision to move to KeyError: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#deprecate-loc-reindex-listlike

Expected Output

Both return KeyError?

This can be linked to #32125 also.

[phofl]

Hi, thanks for your report.

@jbrockmendel I think this is somehow intended? I remember vaguely that we have discussed this in the past

[jreback]

this is as intended. if you have a label that is not in the index, you must use .reindex

[jbrockmendel]

@jreback i think the issue is that the first case doesn't raise, not that the second case does

[attack68]

To give this even more context (possibly a bug) @jbrockmendel @jreback :

df = pd.DataFrame({("z","a"): [1, 2, 3], ("z","b"):[4, 5, 6]})

df.loc[:, ("z", ["a", "b", "c"])]                 # NO ERROR: DataFrame returned with columns 'a' and 'b'.
df.loc[:, [("z", "a"), ("z", "b"), ("z", "c")]]   # KeyError: The following labels were missing: MultiIndex([('z', 'c')]

The error message hints that this behaviour is known or at least expected:

Passing list-likes to .loc or [] with any missing labels is no longer supported.

I took a look but the .loc code is quite tricky to get into and well nested. Suggest someone who knows their way around might feel like commenting.

[phofl]

@attack68 I might be able to help a bit here. In case of a nested tuple as indexer, we dispatch to https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.get_locs.html

As you already mentioned this is a bit inconsistent. I try to write the cases we encounter up:

df = pd.DataFrame({("z","a"): [1, 2], ("z","b"):[4, 5], ("y", "b"): [6, 7], ("y", "c"): [6, 7]})

df.loc[:, ("x", ["a", "b", "c"])]  # Already raising since the not list like key is missing, but KeyError x is a bit confusing
df.loc[:, ("z", ["a", "b", "c"])]  # should raise probably
df.loc[:, (["z", "y"], ["a", "b"])]  # should raise probably
df.loc[:, (["z", "y", "x"], ["b"])]  # should raise probably

The ways get_locs is implemented this is quite hard to fix with a meaningful error message. Especially the cases where something like ("y","a") is missing but ("y","b"), ("z", "a"), ("z", "b") all exist is hard to handle.

[attack68]

@phofl I encountered this problem in a new context and have revised my view. Instead of 'just for consistency' sake I think there is good reason Index and MultiIndex behave differently.
I will close this since there is more info there..

see #39775

[attack68]

Hi @phofl: reopening this to discuss the how MultiIndex generates KeyIndex. I have actually changed my mind in the interim for good reason. My original argument was consistency, but I think a better argument for now avoiding KeyError is practical use and no availability to reindex with a MultiIndex. (also take a look here #39775).

In summary for dealing with missing key in a single index the documented recommendation is .reindex.

For a MultiIndex, say:

    [("a", 1, "z"), 
     ("b", 1, "z"), 
     ("b", 2, "z"), 
     ("c", 3, "y"),
     ("d", 5, "y"), 
     ("e", 5, "y")]

then if you intend to index with say: ["f", :] or [(slice(None), 4), :] then you would get a KeyError, so you specifically have to ensure you don't do that by testing for key presence in the specific level_values and exclude certain keys from the lookup.

You cannot simple use .reindex since there are numerous sensible approaches, e.g. trying to add 4 to level_1:

• you could exhaustively concatenate MultIndex.from_product([["a", "b", "c", "d", "e"], [4], ["z", "y"]]) which is memory error prone and impractical.
• you could, at most, double the length of the Index by concatenating the new key in every available location and removing duplicates, i.e. MultiIndex.from_arrays([["a","b","b","c","d","e"], [4,4,4,4,4,4], ["z","z","z","y","y","y"]]).drop_duplicates
• or you could insert a single key presence determined by some nearest neighbour or 'first' algorithm, i.e. just adding the single key: ("a", 4, "z") or ("c", 4, "y")

Thus, I think the behaviour should be considered very carefully for how MultiIndex slicing should operate.