Python - How to sort values into a weekly series/dataframe?

user_19574423 0
Viewed 50

I have a df as follows:

|User | Date      | Items|
|-----|-----------|------|
|x    | 2021/03/01| 3    |
|x    | 2021/03/09| 5    |
|y    | 2021/03/09| 11   |
|z    | 2021/03/17| 7    |

I want to summarise this as follows:

|User | Week 1| Week 2| Week 3| Total|
|-----|-------|-------|-------|------|
|x    |   3   | 5     |   0   |   8  |
|y    |   0   | 11    |   0   |  11  |
|z    |   0   | 0     |   7   |   7  |

What is the best way for me to do this?

Apologies if I have the formatting wrong, I'm very new to this - many thanks

pandas dataframe python
3 Answers

Assuming your week of month are based on the day number:

(df.assign(wom=pd.to_datetime(df['Date']).dt.day.floordiv(7).add(1))
   .pivot_table(index='User', columns='wom', values='Items',
                aggfunc='sum', fill_value=0, margins='columns')
   .add_prefix('Week ').rename(columns={'Week All': 'Total'})
   .iloc[:-1].reset_index().rename_axis(columns=None)
)

output:

  User  Week 1  Week 2  Week 3  Total
0    x       3       5       0      8
1    y       0      11       0     11
2    z       0       0       7      7
user_163434640

Code:

I have copy this fun from somewhere

def week_number_of_month(date_value):
     return 'Week ' + str((date_value.isocalendar()[1] - date_value.replace(day=1).isocalendar()[1] + 1))

for idx, val in enumerate(df['Date']):
    df.loc[idx, 'Date'] = week_number_of_month(pd.to_datetime(val))

using pivot table, converting column values to columns

df = df.pivot_table(values='Iteams', index=df.User, columns='Date', aggfunc='first').fillna(0)

df['Total'] = df[df.columns].sum(axis=1)
df

Output:

Date    Week 1  Week 2  Week 3  Total
User                
x       3.0     5.0     0.0     8.0
y       0.0     11.0    0.0     11.0
z       0.0     0.0     7.0     7.0
user_138884860

You can try to calculate roughly the week of the month, and then pivot.

df.Date = pd.to_datetime(df.Date)

df['week'] = df.Date.dt.day//7+1

out = df[['User','Items','week']].pivot('User','week').fillna(0)
out = out.assign(total=out.sum(axis=1))

Output:

      Items            total
week      1     2    3      
User                        
x       3.0   5.0  0.0   8.0
y       0.0  11.0  0.0  11.0
z       0.0   0.0  7.0   7.0
user_152838590
Related