how to fill a column value from another dataframe if dates are the same

Question

i have two dataframes df1 and df2 have same values on columns, i would like to have a function to fill df1 exchange rates column with exchange rates from a date in df2

also if possible interpolate nans from df2

df1
                                                                                                                                                                                                                                                                                                                                                                                                                               "date"        "value"     "Exchange_Rate"
2019-Jan    35691
2019-Jan    17076
2019-Dec    988
2019-Dec    1996


df2

"exchange_rate" "date"     "real_or_forecast"

19.126386   2019-Jan  real
19.197585   2019-Feb  real
19.269133   2019-Mar  real
19.089059   2019-Apr  real
19.042815   2019-May  real
19.142962   2019-Jun  real
NaN         2019-Jul  forecast
NaN         2019-Aug  forecast
19.237154   2019-Sep  forecast
NaN         2019-Oct  forecast
19.559262   2019-Nov  forecast
NaN         2019-Dec  forecast
19.559262       2020-Jan  forecast

    def get_fill_currency(df):   
    for value in df['date']
        if df1['date'] == date :
            return value['exchange_rate']

df1
                                                                                                                                                                                                                                                                                                                                                                                                                           "date"        "value"     "Exchange_Rate"
2019-Jan    35691        19.126386       
2019-Jan    17076        19.126386
2019-Dec    988          19.559262
2019-Dec    1996         19.559262

Possible duplicate of [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Quang Hoang, Jul 06 '19 at 02:00

score 0 · Accepted Answer · answered Jul 06 '19 at 02:01

0

IIUC, you can use map, with set_index, and interpolate:

df1[2] = df1[0].map(df2.set_index('date')['exchange_rate'].interpolate())

Output:

          0      1          2
0  2019-Jan  35691  19.126386
1  2019-Jan  17076  19.126386
2  2019-Dec    988  19.559262
3  2019-Dec   1996  19.559262

answered Jul 06 '19 at 02:01

Scott Boston

147,308
15
139
187

Yes, you are creating a series with key/value mapping for map to use. – Scott Boston Jul 07 '19 at 00:13

how to fill a column value from another dataframe if dates are the same

1 Answers1