Replace nan values in one column of a dataframe with another column value of second dataframe

user_19826427 0
Viewed 34

I have a dataframe df1 which has customer id and a min_date, the second dataframe has a column open_dt where there are some nan values which I want to replace with min_date value where the customerid matches

df1

    CUSTOMERID  OPEN_DT
0   BATCH7MRN1  2019-03-26
1   BATCH7MRN10 2016-09-02
2   BATCH7MRN100    2016-04-30
3   BATCH7MRN101    2016-09-15
4   BATCH7MRN102    2020-03-05
... ... ...
376 BATCH7MRN94 2020-10-17
377 BATCH7MRN96 2016-12-21
378 BATCH7MRN97 2021-02-25
379 BATCH7MRN98 2010-08-27
380 BATCH7MRN99 2019-03-20

df2

    CUSTOMERID  OPEN_DT
0   BATCH7MRN1  2019-03-26
1   BATCH7MRN1  2019-03-26
2   BATCH7MRN1  2019-03-26
3   BATCH7MRN1  2019-03-26
4   BATCH7MRN1  2019-03-26
... ... ...
36115   BATCH7MRN99 2021-11-02
36116   BATCH7MRN99 2021-11-02
36117   BATCH7MRN99 2021-11-02
36118   BATCH7MRN99 2021-11-02
36119   BATCH7MRN99 2021-11-02

I have tried joining both and replacing the nan value field with the min date but it didn't worked

pandas python
1 Answers

IIUC, first merge df1 and df2 on the column CUSTOMERID. Then, replace the nan values by the merged column from df1.

df2 = df2.merge(df1, on='CUSTOMERID', suffixes=['2', '1'])
df2.loc[df2['OPEN_DT_2'].isnull(), 'OPEN_DT_2'] = df2['OPEN_DT_1']
df2 = df2.drop(columns='OPEN_DT_1')
user_88141310
Related