ENH: implement drop_levels argument in loc

[arw2019]

xref #6249, #18262, #35301

Following up on #35301. This is an outstanding piece of DataFrame.xs functionality that's currently not available in .loc. Since xs is slated for deprecation I'd like implement this in .loc

Example

Let's define a multiindexed dataframe (example from xs docs)

   ...:  import pandas as pd
   ...:  
   ...: d = {  
   ...:     'num_legs': [4, 4, 2, 2],  
   ...:     'num_wings': [0, 0, 2, 2],  
   ...:     'class': ['mammal', 'mammal', 'mammal', 'bird'],  
   ...:     'animal': ['cat', 'dog', 'bat', 'penguin'],  
   ...:     'locomotion': ['walks', 'walks', 'flies', 'walks'] 
   ...:     }  
   ...:  
   ...: df = pd.DataFrame(data=d) 
   ...: df.set_index(['class', 'animal', 'locomotion'], inplace=True)                                                 

In [2]: df                                                                                                            
Out[2]: 
                           num_legs  num_wings
class  animal  locomotion                     
mammal cat     walks              4          0
       dog     walks              4          0
       bat     flies              2          2
bird   penguin walks              2          2

xs and .loc both let us take a cross-section through a MultiIndex

In [3]: import pandas._testing as tm 
    ...: 
    ...: res_xs = df.xs(('mammal', slice(None))) 
    ...: res_loc  = df.loc[('mammal', slice(None))] 
    ...: tm.assert_frame_equal(res_xs, res_loc)                                                                       

but with xs we can choose to keep the index columns through which we're slicing using the drop_level argument:

In [4]: df.xs(('mammal', slice(None), 'flies'), drop_level=False)                                                    
Out[4]: 
                          num_legs  num_wings
class  animal locomotion                     
mammal bat    flies              2          2

new API

Looking for ideas on what's appropriate here. Based off of suggestions in #6249 would something like this work?

In [ ]: df.loc(drop_levels=False)[('mammal', slice(None), 'flies')]      
Out[4]: 
                          num_legs  num_wings
class  animal locomotion                     
mammal bat    flies              2          2
                 

[simonjayhawkins]

Thanks @arw2019 for writing this up.

Looking for ideas on what's appropriate here. Based off of suggestions in #6249 would something like this work?

I think that would make sense.

we already have an axis parameter

>>> df.loc(axis=1)["num_legs"]
class   animal   locomotion
mammal  cat      walks         4
        dog      walks         4
        bat      flies         2
bird    penguin  walks         2
Name: num_legs, dtype: int64
>>>

If axis is specified, then we would not drop levels from the axis given if drop_levels=False

However, if axis is not specified and we have a 2d indexer in order to allow the behaviour to be controlled for both axis, we may need to have drop_levels={True, False, 'index', 'columns'}

[simonjayhawkins]

There may be api breaking implications with adding a drop_levels parameter.

currently

>>> df.loc[(slice(None), "cat", slice(None))]
                   num_legs  num_wings
class  locomotion
mammal walks              4          0
>>>
>>> df.loc[(slice(None), "cat"), :]
                          num_legs  num_wings
class  animal locomotion
mammal cat    walks              4          0
>>>
>>> df.loc[(slice(None), "cat", slice(None)), :]
                          num_legs  num_wings
class  animal locomotion
mammal cat    walks              4          0
>>>

so I don't think that the default could be True

[TomAugspurger]

Haven't read too closely yet, but I don't think we should treat xs being able to do something loc can't as an argument for making loc more complicated. Rather, that's an argument for not deprecating xs.

[jbrockmendel]

I agree with Tom

[jbrockmendel]

Where does it say xs is slated for deprecation?

[simonjayhawkins]

listed under potential at #18262 with a couple of -1's in the discussion and #6249 (comment)

[mroeschke]

Seems to be not much enthusiasm for this feature even if xs doesn't get deprecated, so closing due to lack of support. Can reopen if another use case supporting this feature is raised