CODEWITHSUNDEEP
pythonpython-3.xpandasgroup-bysum
mlenthusiast
mlenthusiast

Reputation: 1194

Group and sum by week

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!

Upvotes: 0

Views: 427

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150735

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

Upvotes: 1

David Erickson
David Erickson

Reputation: 16683

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

Upvotes: 1

Related Questions