Pandas: Sum Month-over-month Based On Start- And End Date Ranges

Building on this previous post, I have a dataframe like the following: in_df: Index Name Value Start Date End Date 0 John 100 21/05/2021 31/12/2021 1

Solution 1:

Follow the steps

1. Parse the strings in the Start and End date columns to datetime

end = pd.to_datetime(df['End Date'], dayfirst=True)
start = pd.to_datetime(df['Start Date'], dayfirst=True)

2. Generate the monthly period range corresponding to Start and End date column

df['period'] = [pd.period_range(*v, freq='M') for v in zip(start, end)]

3. Explode the dataframe on period and pivot the exploded dataframe with aggregation function sum

out = df.explode('period').pivot_table(
        'Value', 'Name', 'period', aggfunc='sum', fill_value=0)
out.columns = out.columns.strftime('%b-%y')

Result

>>> out

period  Jan-21  Feb-21  Mar-21  Apr-21  May-21  Jun-21  Jul-21  Aug-21  Sep-21  Oct-21  Nov-21  Dec-21
Name                                                                                                  
John        50      70      70      70     170     170     170     120     120     120     100     100
Mary        40      40      90      90      50      30      30      30       0       0       0       0