# Smoothing a binned averages

I am trying to smooth some binned data. I have a discrete variable X which might best be thought of as time and a continuous variable Y. I want to know the average Y value for each value of X and this is pretty straight forward. However, if some specific X values have few associated Y values I suffer from high statistical error. I would like to smooth the averages by using the support from their adjacent bins.

An example might be illustrative. Let Y be days and X be sale price from a retail store. If I want to know the average sale price trend over time this can be easily calculated. However if there are days where only one or two items were sold they could cause the plot to fluctuate wildly. I would like to reduce statistical error on each day by incorporating the adjacent few days. I assume there are a number of ways to do this so please let me know if there is a standard.

Please note I do not want to interpolate. I just want to control the statistical fluctuations. Also, the most recent day is likely of the most importance. Since this day only has bins on one side of it I suspect a bias from many methods. Is there a way to add an error bar to the adjusted average in a meaningful way?

----EDIT-----

It seems there are many methods available so I will describe my situation to make it more clear. We are calculating a price index at a car dealership. All sale values are negotiated so we can use this to understand market trends. Many cars are unique so we do not have an apples to apples comparison over time. This prevents a standard "Market Basket" approach. We have a machine learning model which predicts the sale price. We then make a partial dependence plot by predicting all cars ever sold at each time interval. The average of all cars at each time gives the price index. This is just the standard partial dependence in time but has this useful implication. Anyway, there are some fluctuations which come from the model. In some time periods few cars were sold so we would want to smooth dependent on the training data volumes. The averages themselves actually all have the same number of data points since it is a partial dependence plot.

• What do you mean by 'the average sale price'. That is a simple observational statistic and is supposed to fluctuate depending on the variation in the number of products on sale. Possibly you wish to model some sort of underlying statistic like a value for the product on sale. Anyway, the example case does not make it clear to me why you would wish to correct for the number of products on sale and related variations in average sales prices. – Sextus Empiricus Mar 20 '20 at 11:28
• Added an edit to clarify – Keith Mar 20 '20 at 16:09

I don't think there is a single best way to approach this problem. It depends on the nature of the data and what exactly you are trying to discover with your analysis.

If you believe that the price varies smoothly over time, then you could just estimate a regression model for the time trend, which would automatically downweight the areas with few data points. If the shape of the curve is non-linear an additive model using splines might be suitable.

For a more descriptive approach moving average or local regressions (eg loess) would also work. As you say the most recent day is the most important then an 'exponentially weighted moving average' (EWMA) might be what you want https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average.

If you believe that price is geniunely more variable day to day then a random effects model could be appropriate, with for example an autoregressive correlation between the true underlying prices on each day. From this you would obtain a shrunk estimate of the price each day, with more shrinkage toward the adjacent points if there are fewer actual data points on each day. I could elaborate on any of these approaches if you can add more desciption to the question.

• Great answer. I added more detail. Is it clear which I should use? – Keith Mar 20 '20 at 16:09
• sorry for the slow reply. Your edit confused me a bit. It seems you already have a statistical model (an ML one at least). I don't know machine learning techniques very well but as I understand it a partial dependence plots over the margins of one variable (say time) holding values of the others constant, or integrating over the other variables in the model. So this should already do the smoothing! If this isn't smoothing I wonder if you are calculating the partial dependence properly? – George Savva Mar 24 '20 at 7:18
• It is pretty smooth but the smoothness is totally inherited from the model. If there was a particular time window with a small number of entries and an outlier the model might predict incorrectly in this region. The model does not know that it should vary smoothly in time so it may not predict that way. This would appear as a bump in the partial dependence plot. I want a good way to smooth this out. Currently we are just looking at the number of training events in a separate plot and interpreting mentally. – Keith Mar 24 '20 at 18:33
• what kind of model are you fitting, and how is the effect of time allowed to vary within it? i think the answer to your question should be in how your model is estimated. – George Savva Mar 24 '20 at 22:10
• I don't know. Perhaps look at EWMA charts. But ideally I would revisit your modelling to build something that respects what you know about the process, rather than using a poor model (one that is evidently overfitting) then trying to correct the predictions post-hoc. – George Savva Mar 25 '20 at 1:47