Python regularise irregular time series with linear interpolation

Diane picture Diane · Aug 11, 2014 · Viewed 13.4k times · Source

I have a time series in pandas that looks like this:

                     Values
1992-08-27 07:46:48    28.0  
1992-08-27 08:00:48    28.2  
1992-08-27 08:33:48    28.4  
1992-08-27 08:43:48    28.8  
1992-08-27 08:48:48    29.0  
1992-08-27 08:51:48    29.2  
1992-08-27 08:53:48    29.6  
1992-08-27 08:56:48    29.8  
1992-08-27 09:03:48    30.0

I would like to resample it to a regular time series with 15 min times steps where the values are linearly interpolated. Basically I would like to get:

                     Values
1992-08-27 08:00:00    28.2  
1992-08-27 08:15:00    28.3  
1992-08-27 08:30:00    28.4  
1992-08-27 08:45:00    28.8  
1992-08-27 09:00:00    29.9

However using the resample method (df.resample('15Min')) from Pandas I get:

                     Values
1992-08-27 08:00:00   28.20  
1992-08-27 08:15:00     NaN  
1992-08-27 08:30:00   28.60  
1992-08-27 08:45:00   29.40  
1992-08-27 09:00:00   30.00  

I have tried the resample method with different 'how' and 'fill_method' parameters but never got exactly the results I wanted. Am I using the wrong method?

I figure this is a fairly simple query, but I have searched the web for a while and couldn't find an answer.

Thanks in advance for any help I can get.

Answer

mstringer picture mstringer · Sep 27, 2016

You can do this with traces. First, create a TimeSeries with your irregular measurements like you would a dictionary:

ts = traces.TimeSeries([
    (datetime(1992, 8, 27, 7, 46, 48), 28.0),
    (datetime(1992, 8, 27, 8, 0, 48), 28.2),
    ...
    (datetime(1992, 8, 27, 9, 3, 48), 30.0),
])

Then regularize using the sample method:

ts.sample(
    sampling_period=timedelta(minutes=15),
    start=datetime(1992, 8, 27, 8),
    end=datetime(1992, 8, 27, 9),
    interpolate='linear',
)

This results in the following regularized version, where the gray dots are the original data and the orange is the regularized version with linear interpolation.

time series with linear interpolation

The interpolated values are:

1992-08-27 08:00:00    28.189 
1992-08-27 08:15:00    28.286  
1992-08-27 08:30:00    28.377
1992-08-27 08:45:00    28.848
1992-08-27 09:00:00    29.891