Pandas: Filter by values within multiple columns

Question

I'm trying to filter a dataframe based on the values within the multiple columns, based on a single condition, but keep other columns to which I don't want to apply the filter at all.

I've reviewed these answers, with the third being the closest, but still no luck:

Setup:

import pandas as pd

df = pd.DataFrame({
        'month':[1,1,1,2,2],
        'a':['A','A','A','A','NONE'],
        'b':['B','B','B','B','B'],
        'c':['C','C','C','NONE','NONE']
    }, columns = ['month','a','b','c'])

l = ['month','a','c']
df = df.loc[df['month'] == df['month'].max(), df.columns.isin(l)].reset_index(drop = True)

Current Output:

   month     a     c
0      2     A  NONE
1      2  NONE  NONE

Desired Output:

   month     a
0      2     A
1      2  NONE

I've tried:

sub = l[1:]
df = df[(df.loc[:, sub] != 'NONE').any(axis = 1)]

and many other variations (.all(), [sub, :], ~df.loc[...], (axis = 0)), but all with no luck.

Basically I want to drop any column (within the sub list) that has all 'NONE' values in it.

Any help is much appreciated.

piRSquared · Accepted Answer · 2017-06-07 15:16:11Z

3

You first want to substitute your 'NONE' with np.nan so that it is recognized as a null value by dropna. Then use loc with your boolean series and column subset. Then use dropna with axis=1 and how='all'

df.replace('NONE', np.nan) \
    .loc[df.month == df.month.max(), l].dropna(axis=1, how='all')

   month     a
3      2     A
4      2  NONE

edited Jun 7, 2017 at 15:16

answered Jun 7, 2017 at 15:01

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

3 Comments

elPastor Over a year ago

So everything before the "Current Output" part I'm happy with. In reality the l = [] would be much longer than a few entries, and sub would simply be l minus the first couple entries. It's filtering for the 'NONE' values within the columns, not the column names themselves, that I'm having issues with.

elPastor Over a year ago

Just added a little more color in my question: "Basically I want to drop any column (within the sub list) that has all 'NONE' values in it."

elPastor Over a year ago

Awesome, that killed two birds with one stone. Thank you sir.

Collectives™ on Stack Overflow

Pandas: Filter by values within multiple columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related