Convert sum from monthly to quarterly values

Question

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

Before: After:

Answer 1

You could use panda's resample to group your data into quarterly blocks. I think the key thing to note is that your dates start at the end of the month, so you need to set it to resample from the start of the month. The main problem is that the rest of your columns don't really aggregate well, but you can just update them from the original data by index.

def convert_to_quarterly(df):
    """Aggregates 3 months of data to a quarterly value."""
    df.date = pd.to_datetime(df['date'])
    df = df.set_index("date")
    agg_columns = ['data_1', 'data_2']
    extra_cols = [x for x in df.columns if x not in agg_columns]
    df_out = df.resample("QS-JUN")[agg_columns].sum()
    df_out.index = df_out.index + MonthEnd(1)
    df_out[extra_cols] = df[extra_cols]  
    return df_out

data = convert_to_quarterly(data)

Since we resampled by month start, if you want the dates to be from the end of the month, you can use pandas.tseries.offsets MonthEnd to fix your dates.

Alternatively, you could keep your rollingsum method and just generate your quarter end dates by a daterange:

pd.date_range(data.date.min(),data.date.max(),freq="Q")

Also, converting the date column and setting it as index is not really the responsibility of a monthly_to_quarterly function, so you might want to consider separating the concerns and doing that outside of the function from a design perspective.