Calculating Totals By Month Based On Two Dates In Pandas

I have a dataframe containing two columns of dates: start date and end date. I need to set up a dataframe where all months of the year are set up in separate columns based on the s

Solution 1:

First convert values to datetimes with replace non datetimes to missing values and replace them to some date, then in list comprehension get all months to Series, which is used for pivoting by DataFrame.pivot_table:

end = '2021-12-31'
df['Start'] = pd.to_datetime(df['Start Date'])
df['End'] = pd.to_datetime(df['End Date'], errors='coerce').fillna(end)

s = pd.concat([pd.Series(r.Index,pd.date_range(r.Start, r.End, freq='M'))
                for r in df.itertuples()])

df1 = pd.DataFrame({'Date': s.index}, s).join(df)

df2 = df1.pivot_table(index='Name',
                      columns='Date',
                      values='Value',
                      aggfunc='sum',
                      fill_value=0)

df2.columns = df2.columns.strftime('%b_%y')
print (df2)
Date  Oct_20  Nov_20  Dec_20  Jan_21  Feb_21  Mar_21  Apr_21  May_21  Jun_21  \
Name                                                                           
John     150     150     150      50      70      70      70      20      20   

Date  Jul_21  Aug_21  Sep_21  Oct_21  Nov_21  Dec_21  
Name                                                  
John      20      20      20      20      20      20