How to group/average dataframe with the custom date range in pandas?-CodePudding

Could you please help with question how to group/average one dataframe with the date index from another df in pandas? I have two dfs - one with weekly price data (with irregular dates) and the second df with daily exchange rates. I want to calculate averages of FX rate by date periods from weekly price data df.

            Prices
10.01.2022  0,56
14.01.2022  0,1
20.01.2022  0,22
28.01.2022  0,19
04.02.2022  0,29
11.02.2022  0,13

            FX
03.01.2022  11,71
04.01.2022  11,74
05.01.2022  11,80
06.01.2022  11,89
07.01.2022  11,98
10.01.2022  11,80
11.01.2022  11,80
12.01.2022  11,73
13.01.2022  11,76
   …          …
03.02.2022  11,99
04.02.2022  12,01
07.02.2022  11,96
08.02.2022  11,88
09.02.2022  11,80
10.02.2022  11,74
11.02.2022  11,73

Want to have something like this:

            Prices  FX avr
10.01.2022  0,56    n/a
14.01.2022  0,1     11,82
20.01.2022  0,22    12,03
28.01.2022  0,19    12,34
04.02.2022  0,29    12,10
11.02.2022  0,13    11,82

CodePudding user response：

The output is not fully clear, but you can use a merge_asof to align the two datasets:

df1.index = pd.to_datetime(df1.index, dayfirst=True)
df2.index = pd.to_datetime(df2.index, dayfirst=True)

(pd.merge_asof(df2, df1.assign(date=df1.index),
                    left_index=True, right_index=True, direction='forward')
   .assign(FX=lambda d: d['FX'].str.replace(',', '.').astype(float))
   .groupby('date').agg({'Prices': 'first', 'FX': 'mean'})
)

output:

           Prices         FX
date                        
2022-01-10   0,56  11.820000
2022-01-14    0,1  11.763333
2022-02-04   0,29  12.000000
2022-02-11   0,13  11.822000