Aggregating weekly data by group into monthly sums in pandas

user_18412497 0
Viewed 264

This seems pretty straightforward to do but I'm very new to pandas and I'm not sure where to start. I have a dataset that contains weekly data for multiple clinics. Every week begins on a Sunday and ends on a Saturday. I'd like to aggregate it into monthly data and keep it sorted by clinic.

This is what it currently looks like:

  In [2]: df
  Out[2]:
  Week                         Clinic Appointments Cancellations
  2021-11-28 to 2021-12-04     fee    40            4
  2021-11-28 to 2021-12-04     fi     21            2
  2021-12-05 to 2021-12-11     fee    36            3
  2022-02-20 to 2022-02-26     fee    10            1
  2022-02-27 to 2022-03-05     fee    45            3
  2022-02-27 to 2022-03-05     fi     30            1
  TOTAL (all clinics)          ---    182           14

And this is what I want it to become:

  Month     Clinic Appointments  Cancellations
  Nov '21   fee     40           4
  Nov '21   fi      21           2
  Dec '21   fee     36           3
  Feb '22   fee     55           4
  Feb '22   fi      30           1
  TOTAL     ---     182          14

So the way that I would group a week with a month is if the beginning date (the Sunday) falls within that month. Also, not all clinics will have data for every week.

What I've tried:

I've been trying to use

df.groupby(['Clinic', 'Week'])

but from there I'm not sure how to aggregate the sorted weekly data and return it as a new excel worksheet in the format I want. Any hints would be welcome.

pandas python
2 Answers

'Week' is not in the year_month format you need in your expected output, so you need to first convert them into year_month by:

date = df['Week'].str.split(' ', expand=True)[0]
year_month = pd.to_datetime(date, errors='coerce').dt.strftime('%Y-%b').fillna(date)

before you use groupby:

df.groupby([year_month, 'Clinic']).sum()
user_110654650

just to add to the above comment from Raymond, using:

dt.strftime('%Y-%m')

instead of

dt.strftime('%Y-%b')

will sort correctly the output.

user_119904520
Related