Pandas Multi-Index with multiple conditions

Question

I applied the .loc methodolgy discussed in Select rows in pandas MultiIndex DataFrame because I was recieving a KeyError:'class' even though 'class' exists as (what I thought was a column name) in my existing dataframe. Later finding out that 'class' was one of two indexes in a multiindex ('group' being the secondary index). While the .loc function allows me to select rows with 'First','Second','Third' I'm struggling to determine how to then apply an additional condition to exclude rows where the second index ('group') has blank rows.

Current dataframe looks like this:

class	group	Column1
First	A	123
First
Second	B	123
Third	C	123
Forth	D	123

Current code looks like this:

keep_rows = df.loc[['First','Second','Third']]

My original code looked like this (and was throwing the KeyError due to referenced names beind indexs and not column names)

keep_rows = df[(df['class'].isin(['First','Second','Third'])) & (df['group'].isna())]

Desired dataframe:

class	group	Column1
First	A	123
Second	B	123
Third	C	123

keep_rows = df.reset_index().loc[df['class'].isin(['First', 'Second', 'Third']) & df['group'].notna()] — Moid, Jul 10 '23 at 20:33
df = df.dropna()[['class', 'group', 'Column1']].head(3) may be a little clunky, but it gives the desired dataframe :). — Taylor Rendon, Jul 10 '23 at 20:36
@Moid your script resulted in the original error KeyError: 'class' — 2020db9, Jul 11 '23 at 11:44

Corralien · Accepted Answer · 2023-07-11T14:57:03.813

3

You can use get_level_values:

>>> df[(df.index.get_level_values('class').isin(['First','Second','Third'])) 
       & (df.index.get_level_values('group') != '')]

              Column1
class  group         
First  A        123.0
Second B        123.0
Third  C        123.0

Details:

>>> df.index.get_level_values('class').isin(['First','Second','Third'])
array([ True,  True,  True,  True, False])

>>> df.index.get_level_values('group').notna()
array([ True, False,  True,  True,  True])

edited Jul 11 '23 at 14:57

answered Jul 10 '23 at 20:32

Corralien

109,409
8
28
52

This ran successfully but did not remove the rows with blank values in the 'group' column. – 2020db9 Jul 11 '23 at 11:41
Probably because you have no `NaN` values but empty or `'NaN'` string? – Corralien Jul 11 '23 at 12:07
within the df, the cells are empty. is there another way to remove these rows? – 2020db9 Jul 11 '23 at 12:11
Can you provide the output of `print(df.index)` – Corralien Jul 11 '23 at 13:24
1

MultiIndex([( 'First', 'A'),( 'First', ''),( 'Second', 'B'),( 'Third', 'C')], names=['class','group']) – 2020db9 Jul 11 '23 at 14:53
So you have to check the empty string (I updated my answer) and not the null value. – Corralien Jul 11 '23 at 14:57
Yep, I applied the above change and it was successful. Accepted your answer. Thank you! – 2020db9 Jul 11 '23 at 14:58

Pandas Multi-Index with multiple conditions

1 Answers1