I need to sum the daily values in pandas column (sample_sales) if they fall within date range, represented by 2 columns (payment_start and payment_end), matched in the same row with the daily date column (date). I want to output next month payment to monthly_payment column, monthly_payment is the desired output. I entered just the beginning monthly values to save space, but the range here is daily.
date payment_start payment_end sample_sales monthly_payment next_month_duration
2017-11-05 2017-10-01 2017-11-04 1 0 28
2017-11-06 2017-10-01 2017-11-04 2 0 28
... ... ... ... ...
2017-12-03 2017-11-05 2017-12-02 2 3 28
2017-12-04 2017-11-05 2017-12-02 2 3 28
... ... ... ... ...
2017-12-31 2017-12-03 2017-12-30 3 4 35
2018-01-01 2017-12-03 2017-12-30 3 4 35
... ... ... ... ...
2017-02-04 2017-12-31 2018-02-03 4 6 28
2017-02-05 2017-12-31 2018-02-03 4 6 28
The duration of months follows the following repeating pattern 35, 28, 28 days, so it's not as easy to shift the sample_sales by a month.
I am currently using this function where I added next_month_duration:
df['monthly_payment'] = df.apply(lambda x: df.loc[(df['payment_start'] + df['next_month_duration'] <= x.name) & (x.name <= df['payment_end'] + df['next_month_duration']), ['sample_sales']].sum(), axis=1)
The problem that I'am facing is that by pushing the sum of sample_sales by next_month_duration, leaves blanks values or double sums because of 28 and 35 days month duration.
Ultimately, I want to sum the values in monthly_payment from previous month based on the date range and to assign the summed value to each date in the following month inside the monthly_payment column.
