How to aggregate values of pandas datetimeindex in specific ranges?-CodePudding

I have the following problem. Given is this pandas DataFrame

df = pd.DataFrame({
    'Dates': pd.to_datetime(['2022-04-15','2022-05-15','2022-06-15','2022-07-15',
                             '2022-08-15','2022-09-15','2023-10-15']),
    'Values': [100,150,200,150,100,250,100]
})

    Dates       Values
0   2022-04-15  100
1   2022-05-15  150
2   2022-06-15  200
3   2022-07-15  150
4   2022-08-15  100
5   2022-09-15  250
6   2023-10-15  100

Now I wan't to accumulate df['Values'] into this specific date range

daterange1 = pd.date_range('2022-03-31','2022-04-30', freq='M')
daterange2 = pd.date_range(daterange1[-1],'2022-11-30', freq='3M')
daterange = daterange1.union(daterange2)

DatetimeIndex(['2022-03-31', '2022-04-30', '2022-07-31', '2022-10-31'], dtype='datetime64[ns]', freq=None)

A grouping would look like this:

    Dates       Values  
----------------------- 2022-03-31 Sum=0
0   2022-04-15  100
----------------------- 2022-04-30 Sum=100
1   2022-05-15  150
2   2022-06-15  200
3   2022-07-15  150
----------------------- 2022-07-31 Sum=500
4   2022-08-15  100
5   2022-09-15  250
6   2023-10-15  100
----------------------- 2022-10-31 Sum=450

This should be the result:

    Dates       Values
0   2022-03-31  0
1   2022-04-30  100
2   2022-07-31  500
3   2022-10-31  450

Is there a way to group them according to this pattern?

Thanks in advance :)

CodePudding user response：

You can check with cut

out = df.groupby(pd.cut(df.Dates,daterange.union([pd.to_datetime('2022-02-28')]),labels = daterange)).sum() #.reset_index()
Out[376]: 
                     Values
Dates                      
2022-03-31 00:00:00       0
2022-04-30 00:00:00     100
2022-07-31 00:00:00     500
2022-10-31 00:00:00     350