How to filter multi-index dataframe by column with multiple levels?

Question

Having the following pandas dataframe, I like to reduce the dataframe to just include columns ('count','LARGE') and ('50%','LARGE') and also all LabelX columns.

pd.DataFrame({('Label1', ''): {2363: 'D2',
2375: 'D2',
2387: 'D2',
2783: 'D2'},
('Label2', ''): {2363: 'D3',
2375: 'D3',
2387: 'D3',
2783: 'D3'},
('Label3', ''): {2363: 'D4',
2375: 'D4',
2387: 'D4',
2783: 'D4'},
('Label4', ''): {2363: 'na',
2375: 'na',
2387: 'na',
2783: 'na'},
('Label5', ''): {2363: 'False',
2375: 'False',
2387: 'False',
2783: 'False'},
('Label6', ''): {2363: 'D5',
2375: 'D5',
2387: 'D5',
2783: 'D5'},
('Label7', ''): {2363: 'A S',
2375: 'B S',
2387: 'C C',
2783: 'W I'},
('count', 'LARGE'): {2363: 777.0,
2375: 777.0,
2387: 777.0,
2783: 777.0},
          ('50%', 'LARGE'): {2363: pd.Timedelta('0 days 00:00:20'),
2375: pd.Timedelta('0 days 00:15:53'),
2387: pd.Timedelta('0 days 00:16:00'),
2783: pd.Timedelta('0 days 00:01:04')},
          ('50%', 'MEDIUM'): {2363: pd.Timedelta('0 days 00:00:20'),
2375: pd.Timedelta('0 days 00:12:49'),
2387: pd.Timedelta('0 days 00:13:54'),
2783: pd.Timedelta('0 days 00:01:01')},
         }
        )

Already tried the approach dropping columns with:

.drop(columns=[('count','LARGE'),('count','SMALL')])

What I would like to know is if I can specify what to keep instead of dropping not needed columns. My use case has a lot of columns then dropping requires more code...

Example of expected output:

pd.DataFrame({('Label1', ''): {2363: 'D2',
2375: 'D2',
2387: 'D2',
2783: 'D2'},
('Label2', ''): {2363: 'D3',
2375: 'D3',
2387: 'D3',
2783: 'D3'},
('Label3', ''): {2363: 'D4',
2375: 'D4',
2387: 'D4',
2783: 'D4'},
('Label4', ''): {2363: 'na',
2375: 'na',
2387: 'na',
2783: 'na'},
('Label5', ''): {2363: 'False',
2375: 'False',
2387: 'False',
2783: 'False'},
('Label6', ''): {2363: 'D5',
2375: 'D5',
2387: 'D5',
2783: 'D5'},
('Label7', ''): {2363: 'A S',
2375: 'B S',
2387: 'C C',
2783: 'W I'},
('count', 'LARGE'): {2363: 777.0,
2375: 777.0,
2387: 777.0,
2783: 777.0},
          ('50%', 'LARGE'): {2363: pd.Timedelta('0 days 00:00:20'),
2375: pd.Timedelta('0 days 00:15:53'),
2387: pd.Timedelta('0 days 00:16:00'),
2783: pd.Timedelta('0 days 00:01:04')}
         }
        )

Scott Boston · Accepted Answer · 2019-07-20 03:19:39Z

2

You can just keep columns where level1 equals to '' or 'LARGE', with boolen indexing:

df.loc[:, df.columns.get_level_values(1).isin(['', 'LARGE'])]

answered Jul 20, 2019 at 3:19

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

BENY · Accepted Answer · 2019-07-20 03:54:16Z

0

One pattern filter

df.filter(like='L')

answered Jul 20, 2019 at 3:54

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

How to filter multi-index dataframe by column with multiple levels?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related