How to calculate monthly and weekly averages from a dataframe using python?

Question

The below is my dataframe. How to calculate both monthly and weekly averages from this dataframe in python? I need to print month start&end and week start&end then the average of the month and week

**Input
SAMPLE DATASET**            
kpi_id  kpi_name    run_date    value
1       MTTR       5/17/2021    15
2       MTTR       5/18/2021    16
3       MTTR       5/19/2021    17
4       MTTR       5/20/2021    18
5       MTTR       5/21/2021    19
6       MTTR       5/22/2021    20
7       MTTR       5/23/2021    21
8       MTTR       5/24/2021    22
9       MTTR       5/25/2021    23
10      MTTR       5/26/2021    24
11      MTTR       5/27/2021    25


**expected output**
**monthly_mean**            
kpi_name    month_start month_end   value(mean)
    MTTR    5/1/2021    5/31/2021   20
**weekly_mean**         
kpi_name    week_start  week_end    value(mean)
    MTTR    5/17/2021   5/23/2021   18
    MTTR    5/24/2021   5/30/2021   23.5

May have a look at the following post: [Rolling Window](https://stackoverflow.com/a/39917862/13963193) — Phil Leh, Dec 01 '21 at 10:17
It is not working in my case... Please help on how to do this? — Pavithra Kannan, Dec 01 '21 at 10:27

score 4 · Answer 1 · answered Dec 01 '21 at 10:35

4

groupby is your friend

monthly = df.groupby(pd.Grouper(key='run_date', freq='M')).mean() 
weekly = df.groupby(pd.Grouper(key='run_date', freq='W')).mean()

answered Dec 01 '21 at 10:35

igrolvr

343
4
13

I'm getting this error : TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index' – Pavithra Kannan Dec 01 '21 at 10:49
Transform your run_date column like so: `df['run_date'] = pd.to_datetime(df['run_date'],format='%d/%m/%Y')` where `df` is your dataframe – Phil Leh Dec 01 '21 at 12:48
Good point from @PhilLeh, you should have the `run_date` column as a datetime-like object so the groupby method can be applied – igrolvr Dec 01 '21 at 13:46
Can you pls help with the above scenario... I should get the start and end date from the dataset and then it should get the mean for particular period – Pavithra Kannan Dec 01 '21 at 13:57
@PhilLeh Can you pls help me on this – Pavithra Kannan Dec 01 '21 at 14:09

Phil Leh · Accepted Answer · 2021-12-01T15:20:08.573

Extending the answer from igrolvr to match your expected output:

# transformation from string to datetime for groupby
df['run_date'] = pd.to_datetime(df['run_date'], format='%m/%d/%Y')

# monthly
# The groupby() will extract the last date in period, 
# reset_index() will make the group by keys to columns
monthly = df.groupby(by=['kpi_name', pd.Grouper(key='run_date', freq='M')])['value'].mean().reset_index()

# Getting the start of month 
monthly['month_start'] = monthly['run_date'] - pd.offsets.MonthBegin(1)

# Renaming the run_date column to month_end
monthly = monthly.rename({'run_date': 'month_end'}, axis=1)
print(monthly)

# weekly
# The groupby() will extract the last date in period, 
# reset_index() will make the group by keys to columns
weekly = df.groupby(by=['kpi_name', pd.Grouper(key='run_date', freq='W')])['value'].mean().reset_index()

# Getting the start of week and adding one day, 
# because start of week is sunday
weekly['week_start'] = weekly['run_date'] - pd.offsets.Week(1) + pd.offsets.Day(1)

# Renaming the run_date column to week_end
weekly = weekly.rename({'run_date': 'week_end'}, axis=1)
print(weekly)

Thankyou so much! Whole day, I have been stucked with this. trying different things. Appreciating your time and effort for this. — Pavithra Kannan, Dec 01 '21 at 15:20

How to calculate monthly and weekly averages from a dataframe using python?

2 Answers2

Linked