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

Question

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?

Answer 1

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.