pandas 0.20: df with columns of multi-level indexes - How do I filter with condition on multiple columns?

Question

I want to find all rows where all 3 columns are >0. How do I do so? Thanks! I know that using loc with IndexSlicer can return a column of True/False.

But it doesn't work with condition for multiple columns, or return a table of values.

Importance| A         |  B   | C       |
Category | Cat1       | Cat2 | Cat1    |
         |Total Assets| AUMs | Revenue |
Firm 1   | 100        | 300  | 300     |
Firm 2   | 200        | 3400 | 200     |
Firm 3   | 300        | 800  | 400     |
Firm 4   | NaN        | 800  | 350     |

idx=pd.IndexSlice
df.sort_index(ascending=True, inplace=True, axis=1)
df.loc[:,idx[:,'Cat1','Total Assets']]>0

Importance| A         |  
Category | Cat1       | 
         |Total Assets| 
Firm 1   | T       | 
Firm 2   | T       | 
Firm 3   | T       |
Firm 4   | F       |

Desired Output:

Importance| A         |  B   | C       |
Category | Cat1       | Cat2 | Cat1    |
         |Total Assets| AUMs | Revenue |
Firm 1   | 100        | 300  | 300     |
Firm 2   | 200        | 3400 | 200     |
Firm 3   | 300        | 800  | 400     |

why pandas 0.2? also can you provide the source code for the dataframe : df.to_dict() — sammywemmy
– sammywemmy, Commented Jul 27, 2021 at 4:02

Corralien · Accepted Answer · 2021-07-27 06:10:55Z

2

IIUC:

>>> df[df.iloc[:, 1:].gt(0).all(axis=1)]

  Importance           A     B       C
    Category        Cat1  Cat2    Cat1
             TotalAssets  AUMs Revenue
0      Firm1       100.0   300     300
1      Firm2       200.0  3400     200
2      Firm3       300.0   800     400

Update

I only want the filtering for col 'TotalAssets'>0 & 'Revenue'>0?

# idx = pd.IndexSlice
>>> df[df.loc[:, idx[:, :, ['TotalAssets', 'Revenue']]].gt(0).all(axis=1)]

  Importance           A     B       C
    Category        Cat1  Cat2    Cat1
             TotalAssets  AUMs Revenue
0      Firm1       100.0   300     300
1      Firm2       200.0  3400     200
2      Firm3       300.0   800     400

edited Jul 27, 2021 at 6:10

answered Jul 27, 2021 at 4:40

Corralien

121k8 gold badges44 silver badges69 bronze badges

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

3 Comments

Peter Over a year ago

Thanks for the reply! Is there a way to do the filtering for referring the multi-index columns? Be'cos it is not always the case that the columns are in position 1 onwards. Also what if I only want the filtering for col 'TotalAssets'>0 & 'Revenue'>0?

Corralien Over a year ago

@Peter. I updated my answer. Is it what you expect?

Peter Over a year ago

Yes the update is what I want. Will run the code to check when I have the time. Thanks so much!

Collectives™ on Stack Overflow

pandas 0.20: df with columns of multi-level indexes - How do I filter with condition on multiple columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related