Creating a new Column in Python Pandas while handling NaN

Question

I have the following data frame

year month day
1990 05    23
nan  nan   nan
1991 06    24
1992 07    nan
1993 08    26
nan  nan   nan

I need to add a column called birthday with the values as the combined value only when all three columns have values

birthday
1990-05-23
nan
1991-06-24
nan
1993-08-26
nan

I'm creating the column birthday as

en["birthday"] = en["year"].astype(int).astype(str) + '-' + en["month"].astype(int).astype(str) + '-' + en["day"].astype(int).astype(str)

I'm running into a Cannot convert non-finite values (NA or inf) to integer

What is the best way to handle this ? Here, I haven't implemented a check to see if all of them have values either

The dataframe has other columns as well, so would prefer to avoid any global operations that could affect other columns

Using a fillna does help, but I would prefer the empty values as nan

Please provide the entire error output, as well as a minimal reproducible example. — AMC
– AMC, Commented Aug 19, 2020 at 0:17

BENY · Accepted Answer · 2020-08-19 01:14:32Z

2

Your dataframe is well named, just pass to to_datetime

df['bday']=pd.to_datetime(df[['year','month','day']])

df
Out[32]: 
     year  month   day       bday
0  1990.0    5.0  23.0 1990-05-23
1     NaN    NaN   NaN        NaT
2  1991.0    6.0  24.0 1991-06-24
3  1992.0    7.0   NaN        NaT
4  1993.0    8.0  26.0 1993-08-26
5     NaN    NaN   NaN        NaT

From wwnde

df['x']=np.where(df.notna().all(1),df.astype(str).agg(lambda x: '-'.join(x), axis=1), np.nan)

edited Aug 19, 2020 at 1:14

answered Aug 18, 2020 at 23:51

BENY

324k22 gold badges176 silver badges250 bronze badges

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

5 Comments

AChampion Over a year ago

I also got a NaT for index 3.

BENY Over a year ago

@wwnde pd.to_datetime(df[['year','month','day']]) I think if one column is nan, it will return NaN ~ :-)

BENY Over a year ago

@wwnde ah ~ :-) happy coding ~

BENY Over a year ago

@AChampion index 3 should return NaN ~ , I just paste the wrong output before ~

wwnde Over a year ago

@BEN_YO does this help? df['x']=np.where(df.notna().all(1),df.astype(str).agg(lambda x: '-'.join(x), axis=1), np.nan)

Collectives™ on Stack Overflow

Creating a new Column in Python Pandas while handling NaN

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related