Pandas: Create New Column Based on Conditions of Multiple Columns

Question

I have the following dataset:


 ID            AAA                  BBB                  CCC                   DDD
1234    {'2015-01-01': 1}    {'2016-01-01': 1,   {'2015-01-02': 1}     {'2016-01-02': 1} 
                             '2016-02-15': 2}
1235    {'2017-11-05': 1,    {'2018-01-05': 1}         NaN             {'2017-01-06': 1} 
        '2018-06-05': 1}

In the cell, ‘key’ is the date when someone is hospitalized and ‘value’ is the number of days.

I need to create a new column for hospitalization ('Yes' or 'No').

The condition to be 'yes':

The column [AAA or BBB] as well as the column [CCC or DDD] both should have filled-in dates.
The date in the column [CCC or DDD] should be the next day of the date in the column [AAA or BBB].

For example, if [AAA or BBB] has a date of January 01, 2020. For 'yes', the date in [CCC or DDD] should be January 02, 2020.

Desired output:

 ID            AAA              BBB                  CCC                     DDD               Hospitalized
1234    {'2015-01-01': 1}    {'2016-01-01': 1,   {'2015-01-02': 1}     {'2016-01-02': 1}            Yes
                             '2016-02-15': 2}
1235    {'2017-11-05': 1,    {'2018-01-05': 1}         NaN                  NaN                      No
        '2018-06-05': 1}  
1236    {'2017-11-05': 1,    {'2018-01-05': 1}         NaN             {'2018-01-06': 1}            Yes 
        '2018-06-05': 1}

I have tried the following code, but this captures if the dates are present but doesn't capture the timestamp.

df['hospitalized'] = (df
                     .apply(lambda r: 'yes' if (1 if pd.notna(r.loc[['AAA', 'BBB']]).any() else 0) + 
                                               (1 if pd.notna(r.loc[['CCC', 'DDD']]).any() else 0) > 1 
                            else 'no', axis=1))

Any suggestions would be appreciated. Thanks!

Pygirl · Accepted Answer · 2021-01-19 15:56:26Z

1

df:

df = pd.DataFrame([[1234, {'2015-01-01': 1}, {'2016-01-01': 1, '2016-02-15': 2}, {'2015-01-02': 1}, {'2016-01-02': 1}], [1235, {'2017-11-05': 1,'2018-06-05': 1}, {'2018-01-05': 1}, np.nan, np.nan]], columns= ['ID', 'AAA', 'BBB', 'CCC', 'DDD'])

Try:

import itertools
from dateutil import parser
import datetime
def func(x):
    A_B_dates = list(map(parser.parse,list(itertools.chain(*[x['AAA'].keys()] + [x['BBB'].keys()]))))
    C_D_dates = list(map(parser.parse,list(itertools.chain(*[x['CCC'].keys()] + [x['DDD'].keys()]))))
    for date1 in A_B_dates:
        if date1+datetime.timedelta(days=1) in C_D_dates:
            return 'yes'
    return 'no'

df = df.where(df.notna(), lambda x: [{}])    
df['Hospitalised'] = df.apply(func, axis=1)

df:

    ID       AAA                                BBB                                CCC                  DDD                 Hospitalised
0   1234    {'2015-01-01': 1}                   {'2016-01-01': 1, '2016-02-15': 2}  {'2015-01-02': 1}   {'2016-01-02': 1}   yes
1   1235    {'2017-11-05': 1, '2018-06-05': 1}  {'2018-01-05': 1}                   {}                  {'2017-01-06': 1}   no

answered Jan 19, 2021 at 15:56

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Roy Over a year ago

Thank you so much @Pygirl. Time has come to learn more about itertools.chain. Thank you for introducing. Appreciate! Would like to know one thing -- why replacing NaN values are important here?

Pygirl Over a year ago

Because of this: x['AAA'].keys() .keys() if you are having nan you will get an error.

Roy Over a year ago

Make sense as NaN would have float values and keys() wouldn't work on that. Thank you again, @Pygirl!

Roy Over a year ago

Hi @Pygirl -- Would you be able to help here: stackoverflow.com/questions/67429159/…

Collectives™ on Stack Overflow

Pandas: Create New Column Based on Conditions of Multiple Columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related