Selecting rows from Pandas Dataframe with same values in one column that have only missing in another

user_15748497 0
Viewed 117

In the following code, under column A, foo and tog have only missing values in column B. However, I can't simply use is_na() to filter all missing values, since there is one bar that has a missing value.

df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
                          'tog', 'bar', 'bar'],
                   'B' : [np.nan, 2, np.nan, 4, np.nan, 6, np.nan],
                   'C' : [2.0, 5., 8., 1., 2., 9., 3.]})

I've tried with df.groupby('A').filter(df['B'] == 'NaN'), but that returns an error:

'Series' object is not callable.

How can I filter or select for foo and tog? Much appreciated!

Edit: I'm cleaning a dataset that has a few missing values, but spread out amongst a lot of rows. As such, I can't just simply select for named elements corresponding with column A (e.g. foo and tog).

In other words, I need the following

    A   B   C
1   bar 2.0 5.0
3   bar 4.0 1.0
5   bar 6.0 9.0
6   bar NaN 3.0
missing-data pandas python
1 Answers

filter expects a function and you can pass one that checks if not all of the values in B are NaN:

df.groupby("A").filter(lambda x: ~x.B.isna().all())

to get

     A    B    C
1  bar  2.0  5.0
3  bar  4.0  1.0
5  bar  6.0  9.0
6  bar  NaN  3.0

where foo and tog are filtered out since they have all NaN's in B column.

user_93321870
Related