Filter out rows with groupby that are all NaN for a specific column

user_10059652 0
Viewed 45

I am trying to filter out rows when groupby id when all values in column a are NaN. So, across all id, if all observations across the dates are NaN, I want to filter the row out. e.g. I want to filter out id = 2

df

            date  id   a    b    c   d
    0   1/1/2000   1  10   20  10.0  11
    1   1/1/2000   2  NaN  21   1.0  11
    2   1/1/2000   3  15   20  14.0  11
    3   1/1/2000   4  NaN  24  13.0  11
    4   1/2/2000   1  10   25  10.0  11
    5   1/2/2000   2  NaN  20  13.0  15
    6   1/2/2000   3  10   26  22.0  11
    7   1/2/2000   4  10   20  16.0  13
    8   1/3/2000   1  10   20  10.0  11
    9   1/3/2000   2  NaN  20  13.0  11
    10  1/3/2000   3  10   20  18.0  11
    11  1/3/2000   4  10   20  10.0  11

desired dataframe

        date  id   a    b    c   d
0   1/1/2000   1  10   20  10.0  11
1   1/1/2000   3  15   20  14.0  11
2   1/1/2000   4  NaN  24  13.0  11
3   1/2/2000   1  10   25  10.0  11
4   1/2/2000   3  10   26  22.0  11
5   1/2/2000   4  10   20  16.0  13
6   1/3/2000   1  10   20  10.0  11
7   1/3/2000   3  10   20  18.0  11
8   1/3/2000   4  10   20  10.0  11
pandas python
2 Answers

Test non missing values by Series.notna and then get all groups with at least one match by GroupBy.any, GroupBy.transform is used for return Series with same size like original, so possible filter by boolean indexing:

df = df[df['a'].notna().groupby(df['id']).transform('any')]
print (df)
        date  id     a   b     c   d
0   1/1/2000   1  10.0  20  10.0  11
2   1/1/2000   3  15.0  20  14.0  11
3   1/1/2000   4   NaN  24  13.0  11
4   1/2/2000   1  10.0  25  10.0  11
6   1/2/2000   3  10.0  26  22.0  11
7   1/2/2000   4  10.0  20  16.0  13
8   1/3/2000   1  10.0  20  10.0  11
10  1/3/2000   3  10.0  20  18.0  11
11  1/3/2000   4  10.0  20  10.0  11

Or use DataFrame.loc for filter also id with non missisng a and then filter original column by Series.isin with boolean indexing too:

df = df[df['id'].isin(df.loc[df['a'].notna(), 'id'])]
print (df)
        date  id     a   b     c   d
0   1/1/2000   1  10.0  20  10.0  11
2   1/1/2000   3  15.0  20  14.0  11
3   1/1/2000   4   NaN  24  13.0  11
4   1/2/2000   1  10.0  25  10.0  11
6   1/2/2000   3  10.0  26  22.0  11
7   1/2/2000   4  10.0  20  16.0  13
8   1/3/2000   1  10.0  20  10.0  11
10  1/3/2000   3  10.0  20  18.0  11
11  1/3/2000   4  10.0  20  10.0  11
user_29010020

Notice not fast as transform but easy to understand

out = df.groupby('id').filter(lambda x : x['a'].notnull().any())
Out[31]: 
        date  id     a   b     c   d
0   1/1/2000   1  10.0  20  10.0  11
2   1/1/2000   3  15.0  20  14.0  11
3   1/1/2000   4   NaN  24  13.0  11
4   1/2/2000   1  10.0  25  10.0  11
6   1/2/2000   3  10.0  26  22.0  11
7   1/2/2000   4  10.0  20  16.0  13
8   1/3/2000   1  10.0  20  10.0  11
10  1/3/2000   3  10.0  20  18.0  11
11  1/3/2000   4  10.0  20  10.0  11
user_79645270
Related