How can I divide single values of a dataframe by monthly averages?

average, pandas, resampling, time-series

Solution

First make a grouper:

import pandas as pd

In [1]: grouper = pd.Grouper(freq="1M")

Then make your new column:

In [2]: df['normed'] = df.groupby(grouper).transform(lambda x: x/x.mean())

By passing grouper to the `groupby` method you group your data into one month chunks. Within each chunk you divide the 15 minute interval datum by the mean for that month.

Problem

I have the following 15 minute data as a `dataframe` for 3 years. With the first two columns being the index. ``` 2014-01-01 00:15:00 1269.6 2014-01-01 00:30:00 1161.6 2014-01-01 00:45:00 1466.4 2014-01-01 01:00:00 1365.6 2014-01-01 01:15:00 1362.6 2014-01-01 01:30:00 1064.0 2014-01-01 01:45:00 1171.2 2014-01-01 02:00:00 1171.0 2014-01-01 02:15:00 1330.4 2014-01-01 02:30:00 1309.6 2014-01-01 02:45:00 1308.4 2014-01-01 03:00:00 1494.0 ``` I have used `resample` to get a second series with monthly averages. ``` data_Monthly = data.resample('1M', how='mean') ``` How can I divide the values in the last column by their monthly average with the result being still a time series on 15 minute granularity?

Original source