Pandas row filtering on multiple columns

user_15965803 0
Viewed 45

I'm trying to filter the following pandas dataframe.

    id        X_1      X_2    X_4     M_1       M_2     M_3 
0    i24     Nan     0.27     Nan     0.20     0.25    0.27 
1    i25     0.45    0.47     0.46    0.42     Nan     0.42  
2    i11     Nan     Nan      0.32    0.32     0.35    0.29    
3    i47     Nan     0.56     0.59     Nan     0.56    0.51

In particular, I want to treat the M_ columns and X_columns separately and keep only those entries that, for each "group" M and X, have at least two non-Nan values. E.g. entry i24 should not be included, because its values in X_1 and X_4 are both Nan, leaving only one correct value in the X "group". i25 and i47 instead should be kept, since they respect the constraints.

Thank you to anyone that can help.

pandas dataframe statistics python
2 Answers
ndf = df.set_index("id")
good_rows = (ndf.groupby(lambda col: col.split("_", maxsplit=1)[0], axis=1)
                .count()
                .ge(2)
                .all(axis=1))
ndf = ndf.loc[good_rows]

where we first set id as the index to keep it aside. Then grouping by the columns' names until first _ and then counting the non-NaN values. If for both X and M counts are greater than or equal to 2, we call it a good row. Then indexing with these rows,

to get

>>> ndf

      X_1   X_2   X_4   M_1   M_2   M_3
id
i25  0.45  0.47  0.46  0.42   NaN  0.42
i47   NaN  0.56  0.59   NaN  0.56  0.51

note that this assumes those values are NaNs; if not but strings, then please do this beforehand:

df = df.replace("Nan", np.nan)
user_93321870

You can first separate out the list of X columns and M columns, then use apply and check if both the column group satisfies the condition of less than 2 NaN values, then create the masking accordingly, finally use the masking to get the required result.

xCols = [col for col in df if col.startswith('X')]  # ['X_1', 'X_2', 'X_4']
mCols = [col for col in df if col.startswith('M')]  # ['M_1', 'M_2', 'M_3']

df[df.apply(lambda row: sum(row[mCols].isna())<2 and sum(row[xCols].isna())<2, axis=1)]

OUTPUT:

    id   X_1   X_2   X_4   M_1   M_2   M_3
1  i25  0.45  0.47  0.46  0.42   NaN  0.42
3  i47   NaN  0.56  0.59   NaN  0.56  0.51
user_91363480
Related