I'm really not sure what to search for. If the answer to this is googleable, I'd be happy to hear what I should google.
I have a dataset of energy meter data. Readings are taken at roughly monthly intervals, though it is irregular. The data describe total energy use since the previous reading was taken. I want to resample that data to estimate usage for each month (the first to the last day). I'm looking into linear and spline interpolation, but both methods lead to high error rates. As shown below, integrating either of these methods for the known energy use of an interval results in some very large differences.
Is there a way to generate an interpolation for data like this while still being able to match the function over known ranges?
# Create Sample Meter Timeseries ts <- data.frame(start_date = as.Date(c("2015-09-18", "2015-10-20", "2015-11-25", "2015-12-23", "2016-01-22")), end_date = as.Date(c("2015-10-20", "2015-11-25", "2015-12-23", "2016-01-22", "2016-02-21")), energy_use_kWh = c(3211,172,5566,8120,1344)) # Convert interval use into daily rate ts$rate <- ts$energy_use_kWh/as.numeric(ts$end_date - ts$start_date) # Generate midpoint of each interval ts$mid_date <- ts$start_date + (ts$end_date - ts$start_date)/2 # Generate linear and spline interpolations between interval midpoints approxfun_fun <- approxfun(x = ts$mid_date, y = ts$rate, rule = 2) splinefun_fun <- splinefun(x = ts$mid_date, y = ts$rate, method = 'natural') # Compare ts$approx_estimate <- apply(ts[,c('start_date','end_date')], MARGIN = 1, function(x) integrate(f = approxfun_fun, lower = as.Date(x), upper = as.Date(x))$value) ts$spline_estimate <- apply(ts[,c('start_date','end_date')], MARGIN = 1, function(x) integrate(f = splinefun_fun, lower = as.Date(x), upper = as.Date(x))$value) result <- ts[c("start_date","end_date","energy_use_kWh","approx_estimate","spline_estimate")] result$approx_error <- with(result, abs(energy_use_kWh - approx_estimate)/energy_use_kWh) result$spline_error <- with(result, abs(energy_use_kWh - spline_estimate)/energy_use_kWh)
EDIT: I have implemented GeoMatt22's suggestion over my sample data and found some interesting results...
Taking the integral over the known billing ranges matches perfectly with the known data, so that is definitely a success. The cumulative energy use graph looks pretty reasonable, though the derivative graph definitely looks a little strange. Within a billing period, the rate change is smooth and continuous, but there are some cases of instantaneous usage rate changes, where one day it is decreasing rapidly and the next day it is increasing rapidly.
This is the best answer so far, even though the day-to-day rates look a little funky. I'd welcome improvements that address this, but I am also happy with what I've got so far.
Edit 2: I tried changing the spline method from "monoH.FC", which computes a monotone Hermite spline, to "Hyman", which computes a monotone cubic spline using Hyman filtering. The results I got are a bit more continuous, and pass my personal eye-test a little better, though it's still not nicely continuous.
Edit 3: I built a function for the C2 monotone interpolant, as suggested by GeoMatt22. It took me a long time, but I got it to work! Method is from C2 rational quadratic spline interpolation to monotonic data (1983).
If you want more info on the code, I'd be happy to share. Currently in development, but it seems to be working well. The derivative curve, which corresponds to the daily use rate, has no unsightly kinks and integrating that function over each billing interval reproduces the known values exactly.