# Interpolating binned data such that bin average is preserved

Say I have this binned data as input. The average value $\bar{y}_i$ is given for each successive $\Delta x_i$ interval. For simplicity, let's assume sampling density is uniform within each bin.

Now I want to estimate the underlying function $y$($x$) i.e. I want to be able to get reasonable estimates of $y$ for arbitrary, punctual values of $x$ (e.g. $x$ = 2.3 or 2.5 or whatever). The requirement are:

1. The function must preserve the average over each bin, $\overline{y(x)}_i = \bar{y}_i$, so as to not introduce bias
2. The function must be continuous (i.e. no discontinuities)
3. The function must be non-negative. (Negative values are unphysical.)

Simply looking up the bin value for a given $x$ would satisfy #1, but violate #2 (there are discontinuities at all bin edges).

On the other hand, assigning the entire bin weight to each bin center, and then interpolating between those points, satisfies #2, but violates #1 (regardless of whether it's linear or higher-order spline interpolation). In the illustration below, the 2<$x$<3 bin average is not preserved; it is reduced, as both corners get cut downward.

How can this be done in a way that satisfies both requirements?

Also, what is this operation called? Is this interpolation? (Not sure how to tag this question.)

Here is a paper that describes an iterative method that does what you're asking:

Mean preserving algorithm for smoothly interpolating averaged data

M.D. Rymes, D.R. Myers, Mean preserving algorithm for smoothly interpolating averaged data, Solar Energy, Volume 71, Issue 4, 2001, Pages 225-231, ISSN 0038-092X, https://doi.org/10.1016/S0038-092X(01)00052-4. (http://www.sciencedirect.com/science/article/pii/S0038092X01000524)

Abstract: Hourly mean or monthly mean values of measured solar radiation are typical vehicles for summarized solar radiation and meteorological data. Often, solar-based renewable energy system designers, researchers, and engineers prefer to work with more highly time resolved data, such as detailed diurnal profiles, or mean daily values. The object of this paper is to present a simple method for smoothly interpolating averaged (coarsely resolved) data into data with a finer resolution, while preserving the deterministic mean of the data. The technique preserves the proper component relationship between direct, diffuse, and global solar radiation (when values for at least two of the components are available), as well as the deterministic mean of the coarsely resolved data. Examples based on measured data from several sources and examples of the applicability of this mean preserving smooth interpolator to other averaged data, such as weather data, are presented.

• This does sound like a solution. Too bad it's behind a paywall. – Jean-François Corbett Feb 15 '18 at 8:35
• You'll find a downloadable copy with your favorite search engine; I got it yesterday. – adr Feb 15 '18 at 9:39
• Is there an implementation of the algorithm for R? – RogerioJB Jul 12 '19 at 22:15

The best solution I've got so far is to do a linear interpolation between points at bin centers as shown in the graph in the question, after having done a numerical optimisation of all the $y_i$, iterating until condition #1 is met (and with a harsh penalty for violating #3). Unfortunately, numerical optimisation is a bit of a heavier process than I had hoped for.

Instead of doing numerical optimisation, I tried just setting up and solving a set of linear equations. That is really straightforward and quick, but it is not robust against requirement #3: some of the $y_i$ can end up negative, which is nonsensical. Unfortunately, #3 is a non-linear thing and can't be incorporated in the set of linear equations, as far as I can tell.

• Could you provide any further details about the method you describe "setting up and solving a set of linear equations"? The algorithm described above works well, but it is computationally expensive, requiring at least as many iterations as interpolated timesteps. For a problem not requiring condition #3, is there a way to achieve a means-preserving interpolation that is more efficient than Rymes and Myers (2001)? – JedO Jun 7 at 4:29

Binning is highly discouraged because of inefficiency, discontinuity, and arbitrariness. But you have made the implicit assumption that the bins should be non-overlapping. Making the bins overlap and having many more of them will alleviate some of the problems although regression splines are better.

Don't use bin centers to represent the distribution of $x$ within the bin. Use the mean $x$ within each bin.

• I'm not advocating for or against the use of bins, nor for/against having them overlap. I'm saying, this is the data I have to work with. It's my input. I don't have any higher-grade source of information, unfortunately. Also, given the stated simplifying assumption that samples are uniformly distributed within bins, mean x will be the same as bin center. – Jean-François Corbett Apr 26 '16 at 12:38