Adding support for indexing a MultiIndex with a DataFrame and/or bi-dimensional np.array

[toobaz]

(From #15425 )

Currently, (non-Multi)Indexes can be indexed with Series indexers. And this actually also applies to MultiIndexes, of which you would be selecting from the first level. Hence, it seems a natural consequence for MultiIndexes to be indexed with DataFrame indexers.

Moreover, once #15434 is fixed, we will have a bi-dimensional object (MultiIndex) which can be indexed with np.arrays... but only one-dimensional ones! This is also strange.

The feature per se is certainly useful. As a simple real world example, I am currently working with a subjects DataFrame to which I must attribute two columns from design, another DataFrame, depending on a group and time columns of subjects, which are also levels of the MultiIndex of design. I would like to just do

subjects[design.columns] = design.loc[subjects[["group", "time"]]]

Now, I know this could be solved by .joining the two DataFrames... but this is conceptually more complicated (I even currently ignore whether I can join one DataFrame on columns and the other on index levels... but this is OT), to the point that I'm rather doing:

to_mi = lambda df : df.set_index(list(df.columns)).index
subjects[design.columns] = design.loc[to_mi(subjects[["group", "time"]])]

@jorisvandenbossche suggests this feature would add complexity to indexing, "eg, should the column names align on the level names?". I'm personally fine with both answers:

• Yes: then we just use something like to_mi above (transforming a DataFrame in MultiIndex, and then using it to actually index)
• No: then it's really really simple (we just transform the DataFrame into tuples - I had actually already done this in Mi indexing #15425 before rolling back)

"Yes" is probably the cleanest answer (possibly together with allowing indexing with bi-dimensional np.arrays, to obtain the equivalent of the "No" answer). In any case, once we decide, I can take care of this.

[jreback]

can you show a copy-paste example that creates the actual frame you are talking about (a generic simple version is fine, just give generic names to levels and such).

[toobaz]

s = pd.Series(range(4),
              index=pd.MultiIndex.from_product([[1,2], ['a', 'b']],
                                               names=['first', 'second']))

df = pd.DataFrame([[1, 'a'], [2, 'b']],
                  columns=['first', 'second'])

What I would like to do:

s.loc[df]

what I end up doing:

to_mi = lambda df : df.set_index(list(df.columns)).index
s.loc[to_mi(df)]

outputting indeed:

first  second
1      a         0
2      b         3
dtype: int64

However, only now I realize that column names do not matter above. That is: when you index a MultiIndex using a MultiIndex as indexer, level names are not considered! See the following example:

idf = pd.MultiIndex.from_tuples([[1, 'a'], [2, 'b']], names=['other', 'names'])
s.loc[idf]

which outputs

other  names
1      a        0
2      b        3
dtype: int64

So either this current behavior is undesired, or the answer to @jorisvandenbossche's question is trivial: "we don't respect names for MultiIndex indexers, why should we for DataFrame indexers?!"
(But I think the behavior we want to keep with MultiIndex indexers - which e.g. behave strangely now if they have less levels than the indexed one - is better to be discussed separately - let's just say we want DataFrame indexing to be coherent with it)

[toobaz]

Oh, by the way,

In [2]: s = pd.Series(range(4), index=pd.MultiIndex.from_product([['a', 'b'], ['c', 'd']]))

In [3]: s.loc[pd.DataFrame([['a', 'c'], ['b', 'c']]).values] = [100, 101]
Exception ignored in: 'pandas._libs.lib.is_bool_array'
ValueError: Buffer has wrong number of dimensions (expected 1, got 2)

In [4]: s
Out[4]: 
a  c    100
   d      1
b  c    101
   d      3
dtype: int64

I don't exactly understand what's going on with the "Exception ignored", but setter works with 2-d ndarray (by the way: .loc.__getitem__ and .loc.__setitem__ should really share a lot more code).

[toobaz]

xref #12550

[jbrockmendel]

I suspect MultiIndex.from_frame was implemented in the interim since this issue was opened. This behaves the same as your to_mi lambda but is much prettier.

Does the option to do s.loc[pd.MultiIndex.from_frame(df)] satisfy your needs, or do you think that is all-the-more reason to internally do that conversion when we see a DataFrame indexer?

[toobaz]

My preference is still for a MultiIndex to be indexable with a DataFrame (or any 2d array), just like any index is indexable with a Series (or any 1d array).

And again, this is very weird:

In [1]: import pandas as pd

In [2]: s = pd.Series(range(4), index=pd.MultiIndex.from_product([['a', 'b'], ['c', 'd']]))

In [3]: s.loc[pd.DataFrame([['a', 'c'], ['b', 'c']]).values] = [100, 101]

In [4]: s.loc[pd.DataFrame([['a', 'c'], ['b', 'c']]).values]
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In [4], line 1
----> 1 s.loc[pd.DataFrame([['a', 'c'], ['b', 'c']]).values]

File ~/.local/lib/python3.11/site-packages/pandas/core/indexing.py:1103, in _LocationIndexer.__getitem__(self, key)
   1100 axis = self.axis or 0
   1102 maybe_callable = com.apply_if_callable(key, self.obj)
-> 1103 return self._getitem_axis(maybe_callable, axis=axis)

File ~/.local/lib/python3.11/site-packages/pandas/core/indexing.py:1330, in _LocIndexer._getitem_axis(self, key, axis)
   1328 if not (isinstance(key, tuple) and isinstance(labels, MultiIndex)):
   1329     if hasattr(key, "ndim") and key.ndim > 1:
-> 1330         raise ValueError("Cannot index with multidimensional key")
   1332     return self._getitem_iterable(key, axis=axis)
   1334 # nested tuple slicing

ValueError: Cannot index with multidimensional key

... so yes, my point of view is that having now pd.MultiIndex.from_frame is a further reason to at the very least accept MultiIndexes - although this would not solve the inconsistency above, which happens with a 2d array.