Pandas dropna from a subset based on condition

user_16903514 0
Viewed 50

What is the most efficient way to dropna for a subset of records based on a condition?

Here's some example data:

import numpy as np
d = {'T': [1,1,1,1,2,2,2,2], 'ID': [156, 156, 156, 156, 157, 157, 157, 157],'PMN': [np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan],'XN': [1, np.nan, 5, np.nan, np.nan, np.nan, np.nan, np.nan]}
df = pd.DataFrame(data=d)

I would like to dropna within column XN when T is 1. I understand how to dropna from a subset, but the condition of T==1 is what's throwing me.

df.dropna(axis=0, subset=['XN'], inplace=True)

Here's the desired output:

out = {'T': [1,1,2,2,2,2], 'ID': [156, 156, 157, 157, 157, 157],'PMN': [np.nan, np.nan, np.nan, np.nan, np.nan, np.nan],'XN': [1, 5, np.nan, np.nan, np.nan, np.nan]}
out_df = pd.DataFrame(data=out)
out_df

Thanks!

pandas
2 Answers

Create a boolean condition, and filter the dataframe with it :

condition = df['T'].eq(1) & df.XN.isna()
df.loc[~condition]

   T   ID  PMN   XN
0  1  156  NaN  1.0
2  1  156  NaN  5.0
4  2  157  NaN  NaN
5  2  157  NaN  NaN
6  2  157  NaN  NaN
7  2  157  NaN  NaN
user_71757130

It's probably easier not to use dropna, and just do this by selecting the rows you want:

out_df = df[(df['T'] != 1) | (~df['XN'].isna())]

EDIT with more information:

sammywemmy's answer is much clearer here, in my opinion.

Instead of specifying the rows we don't want and dropping them, we are specifying the rows we do want and selecting them.

The rows we don't want are where T is 1 and XN is NA. sammywemmy writes this as condition = df['T'].eq(1) & df.XN.isna() and then selects using ~condition to invert.

In my original answer, I distributed the negation (~) into the formula using DeMorgan's law.

~(A & B) = (~A | ~B).

In English, the translation of "drop where T is 1 and XN is NA" means we need to keep all the rows where T is not 1 and all the rows where XN is not NA.

(Perhaps the trouble you had with sammy's answer was missing the assignment? Since the selection won't happen inplace. new_df = df.loc[~condition])

user_50020620
Related