1

Consider the following code:

import pandas as pd

d_foo = {}
d_foo[('a',1,'w')] = 1
d_foo[('b',2,'x')] = 1
d_foo[('c',2,'y')] = 1
d_foo[('d',3,'z')] = 1

df = pd.Series(d_foo)

df_sel = df[:,2,:]

This results in the following Series:

        0
b   x   1
c   y   1

Now say instead of 2, I want to get all rows that have either 2 or 3 in the second position like so:

        0
b   x   1
c   y   1
d   z   1

Initially I tried:

df_sel = df[:,[2,3],:]

However this yielded the following error:

TypeError: '[2, 3]' is an invalid key

Based on this thread I tried the answer to 2b:

df_sel = df[pd.IndexSlice[:,[2,3]],:]

However this yielded the same error as above.

Is it possible to select all rows with key 2 and 3 in the index in an elegant way?

BdB
  • 471
  • 5
  • 18

2 Answers2

3

Basically to find a way to do proper logical indexing. See pandas.index.get_level_values:

import pandas as pd

d_foo = {}
d_foo[('a',1,'w')] = 1
d_foo[('b',2,'x')] = 1
d_foo[('c',2,'y')] = 1
d_foo[('d',3,'z')] = 1

df = pd.Series(d_foo)
df[df.index.get_level_values(1).isin([2,3])]

gives

b  2  x    1
c  2  y    1
d  3  z    1
dtype: int64
Z Li
  • 4,133
  • 1
  • 4
  • 19
  • Thanks, this is almost the answer I was looking for. I realized that I oversimplified my example, my apologies. That is, next to selecting values from level 1, I also need to select some values from other levels at the same time. Unfortunately I did not manage to extend your suggestion to my revised example. – BdB Dec 09 '20 at 08:01
1

You can do this with pd.IndexSlice, you just passed the incorrect slice. With 3 levels you need to use : for the 0th and last level you don't need to slice on. Use .loc to select with this slice.

import pandas as pd

idx = pd.IndexSlice[:, [2,3], :]
df.loc[idx]

b  2  x    1
c  2  y    1
d  3  z    1
dtype: int64
ALollz
  • 57,915
  • 7
  • 66
  • 89
  • 1
    That worked, thanks! One small addition: As I need the 1st level to be dropped, I added df = df.droplevel(1) – BdB Dec 09 '20 at 07:58