pandas IndexSlice does not accept integer list

Question

Using pandas IndexSlice, is it possible to use a list of integers? I get KeyError: 'the label [xxxx] is not in the [columns]' when I use a list of integers (even when the values in the multiIndex level are formatted as strings):

vals = np.random.randn(4)
df = pd.DataFrame({'l1': ['A', 'B', 'C', 'B'], 'l2': ['9876', '6789', '5432',
    '1234'], 'l3': ['Y', 'X', 'Y', 'Y'], 'value': vals})
df.set_index(['l1', 'l2', 'l3'], inplace=True)

idx = pd.IndexSlice

# None of the following works
df.loc[idx[:, 6789, :]]
df.loc[idx[:, [6789, 1234], :]]

df.reset_index(inplace=True)
df.l2 = df.l2.astype('str')
df.set_index(['l1', 'l2', 'l3'], inplace=True)
df.loc[idx[:, '6789', :]]

cs95 · Accepted Answer · 2018-03-17 21:46:20Z

3

For starters, your index columns are made up of strings, there's no chance of that working.

To slice out a single value, use the idiomatic xs:

df.xs('6789', level='l2')

          value
l1 l3          
B  X  -1.955361

For a list of values, specify an axis parameter to loc;

df.loc(axis=0)[idx[:, ['6789', '1234'], :]]

               value
l1 l2   l3          
B  6789 X  -1.955361
   1234 Y   0.703208

Note this also works identically to xs for a scalar;

df.loc(axis=0)[idx[:, '6789', :]]

               value
l1 l2   l3          
B  6789 X  -1.955361

answered Mar 17, 2018 at 21:46

cs95

406k106 gold badges745 silver badges798 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MaxU - stand with Ukraine Over a year ago

your index columns are made up of strings - very good catch! :-)

cs95 Over a year ago

@MaxU Must be the coffee I made today, has been quite helpful in keeping me awake :P

A. Slowey Over a year ago

Edited above to my original list of integers, as that also was giving me an error. Adding (axis=0) after loc is the key lesson, in addition to consistency between the dtype of the values in the index level and in the slicer.

cs95 Over a year ago

@A.Slowey No problem. But for the purpose of this Q&A, better leave it as it is (otherwise your edit will invalidate MaxU's answer) :)

A. Slowey Over a year ago

@coldspeed That's totally fair; your and MaxU's answers provide two alternatives and my previous comment emphasizes the most important parts, so that others understand everything they need to know to handle either case. I am curious, however, how (axis=0) and appending a : after the full idx expression achieves the same thing

MaxU - stand with Ukraine · Accepted Answer · 2018-03-17 22:18:17Z

1

Alternative option:

In [76]: df.loc[pd.IndexSlice[:, '6789', :], :]
Out[76]:
               value
l1 l2   l3
B  6789 X   1.306962

PS pay attention at string value '6789' and at the last : in the:

df.loc[pd.IndexSlice[...], :] 
#  NOTE:     ---->         ^

answered Mar 17, 2018 at 22:18

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Collectives™ on Stack Overflow

pandas IndexSlice does not accept integer list

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related