1

It's not a cubic spline as the line always passes through the data points. It's not a polynomial - I have mapped a 6th order polynomial over seven data points and it is very different. Bard admits to not knowing:"The moving average algorithm that Google Sheets uses to generate smooth line charts is not a simple weighted moving average algorithm.

"A simple weighted moving average algorithm would not go through all of the data points in the series."The moving average algorithm that Google Sheets uses to generate smooth line charts is not a simple weighted moving average algorithm. A simple weighted moving average algorithm would not go through all of the data points in the series." Here's the type of data I'm working with:

Raw data - not smoothed

And here's how Sheets interpolates it:Smoothed data I'm looking for a way to replicate the smoothing programatically in order to interpolate between the data points similarly to Sheets.

BlastWave
  • 151

2 Answers2

1

Google Sheet smoothened test data:

It's definitely not cubic spline interpolation.

NB: cubic spline interpolation does go through all the points!

Piecewise quadratic does look quite similar, even if not exactly the same.

Cubic Hermite spline looks even closer (using scipy.interpolate.PchipInterpolator in Python).

Vrael
  • 62
  • Can you tell me how you did the cubic spline interpolation? – BlastWave Oct 01 '23 at 04:23
  • Using scipy.interpolate.CubicSpline in Python – Vrael Oct 01 '23 at 21:36
  • Thanks. I am new to Python (although old to C, PHP, Java...!) and haven't explored scipy. Can scipy provide a way to evaluate the curve at intermediate points, like x= 2.2? If so, could you point me to a good reference please? – BlastWave Oct 01 '23 at 21:45
  • Here's a simple example. You can then evaluate cs at any point (for example cs(0.45)).

    For documentation: https://docs.scipy.org/doc/scipy/reference/generated/scipy.interpolate.CubicSpline.html

    import numpy as np; from scipy.interpolate import CubicSpline; x = np.linspace(0, 3, 3); y = np.sin(x); cs = CubicSpline(x, y);

     – Vrael Oct 01 '23 at 22:09
  • Thanks, I'm finding Py very accessible and powerful. Any further thoughts on which interpolation Sheets uses to smooth lines? – BlastWave Oct 01 '23 at 22:23
  • You could also try asking on a Google product forum / support (even maybe Excel as they have a very similar option) rather than trying to reverse engineer it. – Vrael Oct 02 '23 at 02:07
0

It looks like a Catmull-Rom type of interpolant. At each point $P_i$, the slope is taken to be the slope of the line $P_{i-1} P_{i+1}$. Then, for each interval, you the two end-points and the two end-slopes, which you can fit with cubic curve.

bubba
  • 43,483
  • 3
  • 61
  • 122