create dataframe as week and their weekly sum from dictionary of datetime and int-CodePudding

I have datetime and int values dictionary like below.

details = {
    datetime.datetime.strptime("04-01-2021", "%d-%m-%Y") : 15,
    datetime.datetime.strptime("05-01-2021", "%d-%m-%Y") : 25,
    datetime.datetime.strptime("10-10-2021", "%d-%m-%Y") : 10,
    datetime.datetime.strptime("11-10-2021", "%d-%m-%Y") : 11.5,
    datetime.datetime.strptime("10-11-2021", "%d-%m-%Y") : 20,
    datetime.datetime.strptime("12-11-2021", "%d-%m-%Y") : 23.19,
}

I would like convert this weekly using pandas dataframes like below. Week starts from Monday and ends with Sunday. We need to accumulate all datetimes within the week and need to put sum in that months week cell.

CodePudding user response：

IIUC, this is not as simple as it seems. The trickiest part is to obtain the week number:

out = (pd.Series(details)
   .reset_index(name='value')
   .assign(month=lambda d: d['index'].dt.strftime('%b-%y'),
           week=lambda d: ((d['index']
                              -pd.offsets.MonthEnd(0)
                              -pd.offsets.MonthBegin(1))
                             .dt.isocalendar().week
                             .astype(int)
                             .rsub(d['index'].dt.isocalendar().week)
                             .clip(1)
                            ),
          )
   .pivot_table(index='week', columns='month', values='value', aggfunc='sum')
)

output:

month  Jan-21  Nov-21  Oct-21
week                         
1        40.0   43.19    10.0
2         NaN     NaN    11.5

CodePudding user response：

According to standard ISO 8601 Weeks start with Monday and end on Sunday.

Although, in the US, Canada, and Japan, it's counted as the second day of the week. There is a solution which represents ISO 8601:

import datetime
import calendar
details = {
    datetime.datetime.strptime("04-01-2021", "%d-%m-%Y") : 15,
    datetime.datetime.strptime("05-01-2021", "%d-%m-%Y") : 25,
    datetime.datetime.strptime("10-10-2021", "%d-%m-%Y") : 10,
    datetime.datetime.strptime("11-10-2021", "%d-%m-%Y") : 11.5,
    datetime.datetime.strptime("10-11-2021", "%d-%m-%Y") : 20,
    datetime.datetime.strptime("12-11-2021", "%d-%m-%Y") : 23.19,
}
dti = pd.DataFrame(details.items())
result_df = pd.DataFrame(data=0, columns=list(range(12)), index=list(range(1,6)))

dti['mod'] = dti[0].dt.day%7
dti['iso_day'] = dti[0].dt.isocalendar().day
dti['month_day'] = dti[0].dt.day
dti['iso_day<mod'] = dti['iso_day'] < dti['mod']
dti['day_to_week'] = 0
dti.loc[dti['iso_day<mod'] == True, 'day_to_week'] = np.ceil(dti[0].dt.day/7)   1
dti.loc[dti['iso_day<mod'] == False, 'day_to_week'] = np.ceil(dti[0].dt.day/7)
dti['month'] = dti[0].dt.month
dti
           0      1  mod   iso_day  month_day   iso_day<mod  day_to_week  month
0 2021-01-04  15.00    4         1          4          True            2      1
1 2021-01-05  25.00    5         2          5          True            2      1
2 2021-10-10  10.00    3         7         10         False            2     10
3 2021-10-11  11.50    4         1         11          True            3     10
4 2021-11-10  20.00    3         3         10         False            2     11
5 2021-11-12  23.19    5         5         12         False            2     11

for i in range(dti.shape[0]):
    sum_, week, month = dti.loc[i, [1, 'day_to_week', 'month']]
    result_df.loc[week, month] = result_df[month][week]   sum_
    
result_df.index = ['Week1', 'Week2', 'Week3', 'Week4', 'Week5']
result_df.columns = result_df.columns.map(lambda x: calendar.month_abbr[x])
result_df

Output:

          Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep   Oct    Nov
Week1  0    0    0    0    0    0    0    0    0    0   0.0   0.00
Week2  0   40    0    0    0    0    0    0    0    0  10.0  43.19
Week3  0    0    0    0    0    0    0    0    0    0  11.5   0.00
Week4  0    0    0    0    0    0    0    0    0    0   0.0   0.00
Week5  0    0    0    0    0    0    0    0    0    0   0.0   0.00