Python 3.6 Pandas Select all row from some columns

user_16677860 0
Viewed 87

Have a dataframe like this:

a   b   c   d
1   1   Nan 1
Nan 2   Nan 2  
Nan 3   Nan 3
4   4   4   Nan

I need to get all rows containing "Nan" from all columns except "с" while column "с" is still visible, so its contents are also important. In other words, the output should be like this:

a   b   c   d
Nan 2   Nan 2
Nan 3   Nan 3
4   4   4   Nan

I was able to do this by selectively replacing the Nan values ​​from the "a, b, d" columns with "value 1" and the values ​​of the "c" column with "value 2", thereby being able to filter as I would like. But a real table contains about 16 billion values, so I think my way is not the best way...

pandas dataframe numpy python
3 Answers

Try using loc with drop, isna and any:

df.loc[df.drop('c', axis=1).isna().any(1)]

Output:

     a    b    c    d
1  NaN  2.0  NaN  2.0
2  NaN  3.0  NaN  3.0
3  4.0  4.0  4.0  NaN
user_87083640

Use isna() on the dataframe for the columns you want to check if NaN exists in the row, then call any on axis=1, finally use this mask to get the required dataframe:

>>> df[df[[c for c in df if c!='c']].isna().any(axis=1)]

     a    b    c    d
1  NaN  2.0  NaN  2.0
2  NaN  3.0  NaN  3.0
3  4.0  4.0  4.0  NaN
user_91363480

you can exlcude c from your target columns and return the index of your target columns.

idx = df.loc[:,~df.columns.str.contains('c')].isna().any(1)

df.loc[idx]


     a  b    c    d
1  NaN  2  NaN    2
2  NaN  3  NaN    3
3    4  4    4  NaN
user_93751020
Related