I have two pandas dataframes for daily price of Oil and Gold like this:
Oil dataframe
| Date | Oil Price |
|---|---|
| 4-14-2023 | 20 |
| 4-16-2023 | 40 |
| 4-17-2023 | 80 |
Gold dataframe
| Date | Gold Price |
|---|---|
| 4-14-2023 | 10 |
| 4-17-2023 | 15 |
| 4-18-2023 | 20 |
Now I want to merge the tow dataframes, where I will add the gold price column to the oil dataframe. The Date column in the oil dataframe is index and is consider as the reference. The final dataframe should be like this:
Merged dataframe
| Date | Oil Price | Gold Price |
|---|---|---|
| 4-14-2023 | 20 | 10 |
| 4-16-2023 | 40 | NAN |
| 4-17-2023 | 80 | 15 |
Gold price in the date 4-18-2023 has been ignored as this date is not exist in the oil dataframe. NAN value added to the second date as it's that date is not available in the gold dataframe. I tried to use the merge function:
reduce(lambda left,right: pd.merge(left,right,on=['Date'],
how='left'), data_frames)
But It doesn't filter the dates based on the oil Date and I obtain many mismatches data. Any help please?
