I'm attempting to aggregate multiple columns of monthly data into quarterly chunks. Currently, I'm applying a rolling sum to the columns and then selecting only every third row to 'delete' or hide the rows.
I get the feeling that not only is there a far simpler approach, but there is a more Pythonic/Pandorable way of doing this.
import pandas as pd
data = pd.DataFrame({'parent_id': [1, 1, 1, 1, 1, 1, -99999, -99999, -99999],
'id': [123, 123, 123, 123, 123, 123, 123, 123, 123],
'data_1': [10, 20, 30, 40, 50, 60, 0, 0, 0],
'data_2': [10, 20, 30, 40, 50, 60, 0, 0, 0],
'period': [0, 1, 2, 3, 4, 5, 6, 7, 8],
'date': ['2017-06-30', '2017-07-31', '2017-08-31',
'2017-09-30', '2017-10-31', '2017-11-30',
'2017-12-31', '2018-01-31', '2018-02-28'],
'quarter': [0, 0, 0, 1, 1, 1, 2, 2, 2]})
def convert_to_quarterly(df, date):
"""Aggregates 3 months of data to a quarterly value."""
columns = ['data_1', 'data_2']
dates = pd.to_datetime(df['date'])
quarter_end_dates = map(lambda offset: (date + pd.DateOffset(months=offset)).to_period('M').to_timestamp('M'), np.arange(0, 40, 3))
df_grouped = df.groupby('id')
#df[columns] = df_grouped[columns].apply(pd.rolling_sum, window=3, min_periods=1)
return df.loc[df[dates.isin(quarter_end_dates)].index]
convert_to_quarterly(data, date=pd.to_datetime('2017-06-30'))