Preserving NaN values when using groupby and lambda function on dataframe

Question

Following on from this question, I have a dataset as such:

    ChildID   MotherID   preDiabetes
0     20      455        No
1     20      455        Not documented
2     13      102        NaN
3     13      102        Yes
4     702     946        No
5     82      571        No
6     82      571        Yes
7     82      571        Not documented
8     60      530        NaN

Which I have transformed to the following such that each mother has a single value for preDiabetes:

    ChildID   MotherID   preDiabetes
0   20        455        No
1   13        102        Yes
2   702       946        No
3   82        571        Yes
4   60        530        No

I did this by applying the following logic:

if preDiabetes=="Yes" for a particular MotherID, assign preDiabetes a value of "Yes" regardless of the remaining observations
else if preDiabetes != "Yes" for a particular MotherID, I will assign preDiabetes a value of "No"

However, after thinking about this again, I realised that I should preserve NaN values to impute them later on, rather than just assign them 'No". So I should edit my logic to be:

if preDiabetes=="Yes" for a particular MotherID, assign preDiabetes a value of "Yes" regardless of the remaining observations
else if all values for preDiabetes==NaN for a particular MotherID, assign preDiabetes a single NaN value
else assign preDiabetes a value of "No"

So, in the above table MotherID=530 should have a value of NaN for preDiabetes like so:

    ChildID   MotherID   preDiabetes
0   20        455        No
1   13        102        Yes
2   702       946        No
3   82        571        Yes
4   60        530        NaN

I tried doing this using the following line of code:

   df=df.groupby(['MotherID', 'ChildID'])['preDiabetes'].apply(
       lambda x: 'Yes' if 'Yes' in x.values else (np.NaN if np.NaN in x.values.all() else 'No'))

However, running this line of code is resulting in the following error:

TypeError: 'in ' requires string as left operand, not float

I'd appreciate if you guys can point out what it is I am doing wrong. Thank you.

score 1 · Answer 1 · answered Jul 29 '20 at 10:09

You can do using a custom function:

def func(s):

    if s.eq('Yes').any():
        return 'Yes'
    elif s.isna().all():
        return np.nan
    else:
        return 'No'

df  = (df
       .groupby(['ChildID', 'MotherID'])
       .agg({'preDiabetes': func}))

print(df)

   ChildID  MotherID preDiabetes
0       13       102         Yes
1       20       455          No
2       60       530         NaN
3       82       571         Yes
4      702       946          No

René · Accepted Answer · 2020-07-29T11:49:33.663

You can try:

import pandas as pd
import numpy as np
import io

data_string = """ChildID,MotherID,preDiabetes
20,455,No
20,455,Not documented
13,102,NaN
13,102,Yes
702,946,No
82,571,No
82,571,Yes
82,571,Not documented
60,530,NaN
"""

data = io.StringIO(data_string)
df = pd.read_csv(data, sep=',', na_values=['NaN'])
df.fillna('no_value', inplace=True)
df = df.groupby(['MotherID', 'ChildID'])['preDiabetes'].apply(
         lambda x: 'Yes' if 'Yes' in x.values else (np.NaN if 'no_value' in x.values.all() else 'No'))
df

Result:

MotherID  ChildID
102       13         Yes
455       20          No
530       60         NaN
571       82         Yes
946       702         No
Name: preDiabetes, dtype: object

Ah okay, so it was just a case of NaN values that should be converted to string, got it! This worked for me, thanks, I've accepted this answer. — sums22, Jul 29 '20 at 11:48

Grzegorz Skibinski · Answer 3 · 2020-07-29T10:47:14.197

Try:

df['preDiabetes']=df['preDiabetes'].map({'Yes': 1, 'No': 0}).fillna(-1)

df=df.groupby(['MotherID', 'ChildID'])['preDiabetes'].max().map({1: 'Yes', 0: 'No', -1: 'NaN'}).reset_index()

First line will format preDiabetes to numbers, assuming NaN to be everything other than Yes or No (denoted by -1).

Second line assuming at least one preDiabetes is Yes - we output Yes for the group. Assuming we have both No and NaN - we output No. Assuming all are NaN we output NaN.

Outputs:

>>> df

   MotherID  ChildID preDiabetes
0       102       13         Yes
1       455       20          No
2       530       60         NaN
3       571       82         Yes
4       946      702          No

Preserving NaN values when using groupby and lambda function on dataframe

3 Answers3