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.

Answer 2

I think it is generally recommended to use Grouper instead of TimeGrouper. Have a look at this. For example, if your column is called Date, use

grouper = pd.Grouper(key='Date', freq='M')

instead of using TimeGrouper and then continue as @Zelazny7 suggested. If your column is not a datetime index then use

df['Date'] = pd.to_datetime(df['Date'])