How to select rows from multiindex dataframe based on a condition in one column

Question

I have a multiindex dataframe that looks like this:

df = {'C': {('S', 0): 'A',
  ('S', 2): 'A',
  ('T', 0): 'A',
  ('T', 1): 'A',
  ('T', 3): 'A',
  ('U', 1): 'A',
  ('U', 2): 'A',
  ('U', 0): 'A',
  ('V', 0): 'A',
  ('W', 2): 'A',
  ('W', 0): 'A',
  ('X', 0): 'A',
  ('Y', 3): 'A',
  ('Z', 0): 'A',
  ('Z', 1): 'A'},
 'D': {('S', 0): '15',
  ('S', 2): '22',
  ('T', 0): '20',
  ('T', 1): '20',
  ('T', 3): '20',
  ('U', 1): '18',
  ('U', 2): '14',
  ('U', 0): '14',
  ('V', 0): '14',
  ('W', 2): '22',
  ('W', 0): '25',
  ('X', 0): '15',
  ('Y', 3): '17',
  ('Z', 0): '04',
  ('Z', 1): '16'},
 'E': {('S', 0): 1.0,
  ('S', 2): 1.0,
  ('T', 0): 2.0,
  ('T', 1): 2.0,
  ('T', 0): 2.0,
  ('U', 1): 2.0,
  ('U', 2): 2.0,
  ('U', 0): 2.0,
  ('V', 0): 1.0,
  ('W', 2): 1.0,
  ('W', 0): 1.0,
  ('X', 0): 1.0,
  ('Y', 3): 2.0,
  ('Z', 0): 3.0,
  ('Z', 1): 3.0}}

I want to keep the level 0 rows if in its level 1 there is a value >=2

the output will look something like this:

outp = {'C': {('S', 0): 'A',
  ('S', 2): 'A',
  ('T', 0): 'A',
  ('T', 1): 'A',
  ('T', 3): 'A',
  ('U', 1): 'A',
  ('U', 2): 'A',
  ('U', 0): 'A',
  ('W', 2): 'A',
  ('W', 0): 'A',
  ('Y', 3): 'A'},
 'D': {('S', 0): '15',
  ('S', 2): '22',
  ('T', 0): '20',
  ('T', 1): '20',
  ('T', 3): '20',
  ('U', 1): '18',
  ('U', 2): '14',
  ('U', 0): '14',
  ('W', 2): '22',
  ('W', 0): '25',
  ('Y', 3): '17'},
 'E': {('S', 0): 1.0,
  ('S', 2): 1.0,
  ('T', 0): 2.0,
  ('T', 1): 2.0,
  ('T', 0): 2.0,
  ('U', 1): 2.0,
  ('U', 2): 2.0,
  ('U', 0): 2.0,
  ('W', 2): 1.0,
  ('W', 0): 1.0,
  ('Y', 3): 2.0}}

What I did is I got the value from level 0 when level 1 >= 2, but because when doing this I deleted the values 0 and 1 from level 1 that should stay, I had to create another dataframe with the gotten values and then merge using 'inner'. I got the desired output but for sure I took the long and probably stupid way.

How could I do this in a better way?

Thanks.

Henry Ecker · Accepted Answer · 2021-07-10 02:01:53Z

3

Let's try with groupby filter on level=0 and filter to keep level 0 values when there is any value in index level 1 (get_level_values) greater than or equal to 2:

outp = (
    df.groupby(level=0)
        .filter(lambda s: (s.index.get_level_values(1) >= 2).any())
)

outp:

     C   D    E
S 0  A  15  1.0
  2  A  22  1.0
T 0  A  20  2.0
  1  A  20  2.0
  3  A  20  NaN
U 1  A  18  2.0
  2  A  14  2.0
  0  A  14  2.0
W 2  A  22  1.0
  0  A  25  1.0
Y 3  A  17  2.0

answered Jul 10, 2021 at 2:01

Henry Ecker♦

35.9k19 gold badges48 silver badges67 bronze badges

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

Comments

sammywemmy · Accepted Answer · 2021-07-10 03:15:18Z

3

Get the indices in level 0, where level 1 >= 2, and index the main df :

df.loc[df.query("ilevel_1 > =2").index.get_level_values(0)]

     C   D    E
S 0  A  15  1.0
  2  A  22  1.0
T 0  A  20  2.0
  1  A  20  2.0
  3  A  20  NaN
U 1  A  18  2.0
  2  A  14  2.0
  0  A  14  2.0
W 2  A  22  1.0
  0  A  25  1.0
Y 3  A  17  2.0

answered Jul 10, 2021 at 3:15

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Comments

rhug123 · Accepted Answer · 2023-08-08 19:00:00Z

1

Here is a different way:

(df.loc[df.reset_index(level=1)
        .groupby(level=0)['level_1']
        .transform(lambda x: x.ge(2).any()).to_numpy()])

or

df.loc[df.index.to_frame().groupby(0)[1].transform(lambda x: x.ge(2).any())]

edited Aug 8, 2023 at 19:00

answered Jul 10, 2021 at 3:50

rhug123

8,8801 gold badge14 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to select rows from multiindex dataframe based on a condition in one column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related