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

user_3493315 0
Viewed 78

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.

enter image description here

pandas dataframe python
2 Answers

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
user_163434640

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
user_186247780
Related