Group and sum by week

Question

I have a dataframe where the columns are by day in this format:

a    b    c     01/01/2020  01/02/2020  01/03/2020 ...
1000 2000 3000  2           5           7
.
.
.

These are just arbitrary values. What I want is to sum the date columns and group them by week, like week_1, week_2,... so on and so forth. So for the example above it would look like:

a    b    c     week_1...
1000 2000 3000  14
.
.
.

Is there a clean way to do it for columns? I know I can sum all the columns by selecting the date columns and summing them on the axis, but I'm not sure how to do it per week. Any help is appreciated!

You probably need to melt the dataframe, create a new column which indicates what group they are in, then pivot and sum — Chris, May 28 '20 at 17:50

score 1 · Answer 1 · answered May 28 '20 at 17:54

1

try .stack or .melt to get columns on to rows and dt.week to get the week number. then do a groupby. Here is a post for week number: converting a pandas date to week number

answered May 28 '20 at 17:54

David Erickson

16,433
2
19
35

score 1 · Accepted Answer · answered May 28 '20 at 17:59

You can do:

# move `a`, `b`, `c` out of columns
df = df.set_index(['a','b','c'])

# convert columns to datetime
df.columns = pd.to_datetime(df.columns)

# groupby sum:
(df.groupby(df.columns.week, axis=1)
   .sum() 
   .add_prefix('week_')
   .reset_index()
)

Output:

      a     b     c   week_1
0  1000  2000  3000       14

Group and sum by week

2 Answers2