Pandas: Sum month-over-month based on start- and end date ranges

user_10766718 0
Viewed 559

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        John    50       01/01/2021   31/07/2021
2        John    20       20/02/2021   31/10/2021
3        Mary    40       15/01/2021   30/04/2021
4        Mary    30       20/03/2021   31/08/2021
5        Mary    20       20/03/2021   15/05/2021

I need to set up a dataframe which contains all months of the year and based on in_df aggregates and sums totals for each name, as here follows:

out_df:

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

It's important that each month of the year is represented as columns only once with one sum value per name. The suggestion posted by @jezrael in the post referenced above will create a separate month column for each entry, leading to multiple Mar-21, for instance, in the example above where Index 4 and Index 5both have a start date in March.

Any suggestions to have I might approach this would be greatly appreciated!

pandas datetime python
1 Answers

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)
  1. 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)]
  1. 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
user_128331660
Related