rolling quarterly mean

Question

I want to calculate the quarterly average of a time-indexed dataframe column in a rolling fashion. The mean at any timestamp should not contain information about future timestamps.

This is a code to test the function I wrote. Is there a way to optimize it?

import pandas as pd
import numpy as np
import time


date_range = pd.date_range(start="2020-01-01", end="2023-12-31", freq='D')
np.random.seed(0) 
volume_data = np.random.randint(100, 1000, size=len(date_range))
df = pd.DataFrame({'Date': date_range, 'Volume': volume_data})
df.set_index('Date', inplace=True)


def q_mean(df):
    avg = []
    for t in range(200, len(df)):
        
        q = df.index[t].quarter
        df_filt = df[(df.index.quarter == q) & (df.index < df.index[t])]
        avg.append(df_filt['Volume'].mean())

    return avg

start_time = time.time()
result = q_mean(df)
duration = time.time() - start_time

Answer 1

Put aside optimisation: this is probably just wrong. It has a NaN on index 74 where there probably shouldn't be one. I don't trust the implementation, especially in the neighbourhood of the starting condition 200:.

I would delete everything and replace it with a single Series (not a dataframe) to calculate

return volume.groupby(
    pd.Grouper(level=0, freq='QS')
).expanding().mean()

Answer 2

Thank you for offering a reproducible reprex, I appreciate that.

problem definition

calculate the quarterly average ... in a rolling fashion.

Months in the Gregorian calendar do not, alas, all have 30 days. But we won't soon be going back to the five intercalary days of the Revolutionary calendar.

It isn't clear from the review context if there is some regulatory requirement you are trying to conform to. You may find it more convenient to rephrase "quarterly average" as "90-day average". That would let you use the numpy concept of a sliding window, or the excellent support for rolling windows offered by pandas or polars.

caching

Computing (df.index.quarter == q) hundreds and hundreds of times is just silly, given that the current t is in the same old q > 98% of the time. Numpy must examine more than 1400 rows each time. Better to cache the result, and only recompute when t advances into a new quarter.

But that brings us to the sticking point of the final quarter. If there's just a handful of days, do we really want to compute a "smoothed" mean over just a tiny sample?

stability

The OP code proposes μ = 550 for the random variable Volume, which is a stationary process. But imagine that the Generating Process was four light trading days centered on 450, followed by four heavy trading days with mean 650, and then the cycle repeats. In the opening days of a new quarter, do we really want to assert the long-term mean has moved below 500, or above 600? We average across multiple days in order to reduce the variance -- σ for a 3-day sample will be greater than σ for a 90-day sample. The OP code essentially offers large σ in the opening days of the current quarter, with σ dwindling as we move further into the quarter.

specification

If there is a written Requirements document, perhaps part of GAAP rules, then mention that in a """docstring""" or comment. Absent such constraints, I advise you to define a "90-day window" requirement, and implement it with support from the libraries I mentioned above.