python aggregation of two time-series

Question

I have two pandas time-series dataframes and I want to aggregate the values against one time series based on the intervals of the other one. Let me show by example. The first time series is as follows:

        date    value
0 2016-03-21       10
1 2016-03-25       10
2 2016-04-10       10
3 2016-05-05       10

The second one is a date range with 10 calendar days intervals extracted from the above series. I have written the code to extract this from above data.

     date
 0   2016-03-21
 1   2016-03-31
 2   2016-04-10
 3   2016-04-20
 4   2016-04-30

I want to write some code to get this resultant dataframe:

     date        value
 0   2016-03-21  20
 1   2016-03-31   0
 2   2016-04-10  10
 3   2016-04-20   0
 4   2016-04-30  10

Could please suggest a way to do this without using loops(preferably) in python?

Answer 1

You can bin the data in df1 based on bins in df2 dates,

bins = pd.date_range(df2.date.min(), df2.date.max() + pd.DateOffset(10), freq = '10D')
labels = df2.date
df1.groupby(pd.cut(df1.date, bins = bins, right = False, labels = labels)).value.sum().reset_index()


    date        value
0   2016-03-21  20
1   2016-03-31  0
2   2016-04-10  10
3   2016-04-20  0
4   2016-04-30  10