Merge 2 pandas data frames on multiple columns

user_16492842 0
Viewed 89

I have 2 dataframes with one of them containing the prediction and actual values for the previous months. As shown below it has prediction from January and February. The values for Febuary are Null.

DF1

Date Key Value prediction
1-1-21 A 33211 22123
1-1-21 BA 43231 32132
1-1-21 C 13431 43432
1-2-21 A Nan 23421
1-2-21 BA Nan 44443
1-2-21 C Nan 32133

The second dataframe has date, key and value for February. I would like to merge them to form a new table with all actual values and a forecasts in one table

DF2

Date Key Value
1-2-21 A 33212
1-2-21 BA 52121
1-2-21 C 23123

I want to merge the Values on the key and date into the first table. Final should look like this

Date Key Value prediction
1-1-21 A 33211 22123
1-1-21 BA 43231 32132
1-1-21 C 13431 43432
1-2-21 A 33212 23421
1-2-21 BA 52121 44443
1-2-21 C 23123 32133

I tried pd.merge it creates a new column instead of join into one column and pd.join but it does a left join and drops some of the prediction data. combine first simply appends to the data

pandas dataframe merge python
1 Answers

Here's potentially a way to do it in one (albeit long) line:

df1['Value'] = df1.apply(lambda row: row['Value'] if pd.notna(row['Value']) else df2[(df2['Date'] == row['Date']) & (df2['Key'] == row['Key'])].iloc[0]['Value'], axis=1)
user_92427950
Related