Is there a function (pandas or otherwise) to interpolate quarterly data to monthly while matching averages within each quarter?

user_16803649 0
Viewed 213

I have a dataframe with quarterly forecasts that I would like to interpolate to monthly, but with a few caveats: The monthly data in each quarter should average to the quarterly forecast, and the monthly data should trend towards the next quarterly forecast. We currently use an old linear excel macro to do this, but I'm trying to automate workflows in python. If it is possible to do with a cubic spline instead of linearly that would be preferable?

As an example of the forecast vs interpolated data:

# Example
df = pd.DataFrame({
    "Date": ["2019-01-01","2019-02-01","2019-03-01","2019-04-01","2019-05-01","2019-06-01","2019-07-01","2019-08-01","2019-09-01","2019-10-01","2019-11-01","2019-12-01"],
     "Forecast": [0.8,np.nan,np.nan,1.87,np.nan,np.nan,0.07,np.nan,np.nan,-0.73,np.nan,np.nan],
     "Interpolated":[0.11,0.8,1.49,1.97,2.04,1.58,0.8,0,-0.57,-0.83,-0.76,-0.61]
})

And verifying that the average within each quarter does approximately match the quarterly forecast

df['Date'] = pd.to_datetime(df['Date'])
df.set_index('Date', inplace=True)
df['Interpolated'].resample('Q').mean()

A regular cubic spline gets the shaping, but I don't believe that it matches the average within each quarter - are there any obvious functions out there that I've missed? Appreciate any and all help you can give. The date that the forecast data can be on is flexible - I just put in the first month of the quarter for the purposes of this example.

pandas interpolation python
1 Answers

Welcome to Stack Overflow. Nice first question with reproducible example!

This is not an answer, but a long comment.

Since we have one value and need to keep the average, you can't do much. You can have like two very high values, and 1 extremely low to match the average. I would like to have them linear with the second value identical to the forecast - that is the easiest, too. Basically, I would do something that fits the purpose of the data. Do you need them to just look beautiful in chart? Pick something with nice curves. Do you need to analyze them later somehow statistically? Do something that resamples the data series' behaviour.

That being said, creating your own interpolation function would be an interesting journey, but it is not an easy task. It requires some mathematics, like constructing an approximation from piecewise polynomials, splines, etc.. See more here (archived) and in Ding et al. (2007 - doi:10.1016/j.jmaa.2006.02.051)

user_23834080
Related