CODEWITHSUNDEEP
pythonpandasdataframegroup-by
user9946692
user9946692

Reputation:

groupby week - pandas dataframe

I would like to group a pandas dataframe by week from the last entry day, and make the sum for each column /per week.

(1 week : monday -> sunday, if the last entry is tuesday, the week is is composed of monday and tuesday data only, not today - 7 days)

df:

             a  b  c  d  e
2019-01-01   1  2  5  0  1
...
2020-01-25   2  3  6  1  0
2020-01-26   1  2  3  4  5

expected output:

       week  a  b  c  d  e 
       104   9  8  8  8  7
       ...
       1     7  8  8  8  9

code:

df = df.rename_axis('date').reset_index()
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')
df.groupby(DF.date.dt.strftime('%W')).sum()

Problem: not the week number I want and the weeks n of each year are grouped in the same line

Upvotes: 1

Views: 658

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150805

Try extract the iso calendar (year-week-day), then groupby:

s = dt.index.isocalendar()

dt.groupby([s.year, s.week]).sum()

You would get something like this:

            a   b   c   d   e
year week                    
2019 1     18  33  31  26  25
     2     36  31  25  28  31
     3     33  22  44  22  29
     4     36  36  35  33  31
     5     27  30  26  31  36

Upvotes: 1

Related Questions