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?

Fusibility

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.

Approximation

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'])

Reinold

Answer 3

This can be done in one line with:

df.groupby([df.index.year, df.index.month]).transform(lambda x: x/x.mean())

Bushed

Answer 4

data_Monthly = data.resample('M',on='Date').mean()

Ferguson

Welcome to SO! Please consider explaining your answer alongside the code you provide, as it will only help the SO community to understand how your method works. – Monopolist