I have two dataframes like these, df1:
| A | B | Date |
| -------- | -------- |-------------|
| x | a | 03-10-2022 |
| y | b | 02-10-2022 |
| z | c | 01-10-2022 |
| t | d | 30-09-2022 |
| k | e | 29-09-2022 |
| h | f | 28-09-2022 |
| u | g | 27-09-2022 |
df2:
| unnamed | 14-10-2022 | 07-10-2022 | 30-09-2022 | 23-09-2022 |
| ------------- | -------------- |--------------|------------- |------------- |
| x | a | aa | aaa | aaaa |
| y | b | bb | bbb | bbbb |
| rates | 30.2 | 24.8 | 27.6 | 33.1 |
| z | c | cc | ccc | cccc |
In df1 the dates are going as days and days but df2 the dates are going to weekly. I would like to add rates row in df2 to df1 as a column but according dates. For example between 14-10-2022 and 07-10-2022 rates are must be 24.8
I am expecting like this:
| A | B | Date | Rates |
| -------- | -------- |-------------|------------|
| x | a | 03-10-2022 | 27.6 |
| y | b | 02-10-2022 | 27.6 |
| z | c | 01-10-2022 | 27.6 |
| t | d | 30-09-2022 | 27.6 |
| k | e | 29-09-2022 | 33.1 |
| h | f | 28-09-2022 | 33.1 |
| u | g | 27-09-2022 | 33.1 |
How can I do this?
CodePudding user response：
Use merge_asof with filtering df2 for row with rates:
df1['Date'] = pd.to_datetime(df1['Date'], dayfirst=True)
df1 = df1.sort_values('Date')
df22 = df2.set_index('unnamed')
df22.columns = pd.to_datetime(df22.columns, dayfirst=True)
df22 = df22.loc['rates'].to_frame('Rates').sort_index()
df = pd.merge_asof(df1, df22, left_on='Date', right_index=True).sort_index()
print (df)
A B Date Rates
0 x a 2022-10-03 27.6
1 y b 2022-10-02 27.6
2 z c 2022-10-01 27.6
3 t d 2022-09-30 27.6
4 k e 2022-09-29 33.1
5 h f 2022-09-28 33.1
6 u g 2022-09-27 33.1