Irregular Time Series to Regular using Interpolation

Hi,

I'm pretty new to Matlab so forgive me if I'm asking an easy question!

I currently have two sets of data in csv format - both of which are not regularly spaced at all. What I need is to interpolate the data so I can have a value from both sets for specific times and at equal intervals of 15 minutes.

Table1.csv sample:

20/12/2014 08:55, 0.63

20/12/2014 14:37, 0.61

20/12/2014 16:51, 0.61

20/12/2014 17:15, 0.61

20/12/2014 17:45, 0.6

20/12/2014 18:00, 0.6

20/12/2014 18:15, 0.6

20/12/2014 18:30, 0.6

Table2.csv sample (a bit more regularly spaced, but there are some irregular gaps) -

20/12/2014 03:00, 0.1

20/12/2014 06:00, 0

20/12/2014 09:00, 0.2

20/12/2014 12:54, 0.7

20/12/2014 15:00, 0.5

20/12/2014 18:00, 0.3

20/12/2014 21:19, 0

Now what I'd love to get out is something that looks like this:

Datetime, Table1 Value, Table2 Value

20/12/2014 18:00, 0.5 , 0.1

20/12/2014 18:15, 0.3 , 0.2

20/12/2014 18:30, 0.2 , 0.6

20/12/2014 18:45, 0.1 , 0.2

20/12/2014 19:00, 0.2 , 0.0

Can anybody help? Thanks :)

Answer 1

[Star Strider]

This will get you part way there:

Table1 = {'20/12/2014 08:55', 0.63
    '20/12/2014 14:37', 0.61
    '20/12/2014 16:51', 0.61
    '20/12/2014 17:15', 0.61
    '20/12/2014 17:45', 0.6
    '20/12/2014 18:00', 0.6
    '20/12/2014 18:15', 0.6
    '20/12/2014 18:30', 0.6};

DTn = datenum(Table1(:,1), 'dd/mm/yyyy HH:MM');
ti = 1/(60/15 * 24);                    % Time Interval
DTiv = [DTn(1):ti:DTn(end)]';           % Interpolation Vector
Table12 = cell2mat(Table1(:,2));        % Vector: Column #2 Of Table1
DT15 = interp1(DTn, Table12, DTiv);     % Interpolated Column #2
NewTable1 = {datestr(DTiv, 'dd/mm/yyyy HH:MM')  DT15};

Result = [NewTable1{1} repmat(' ', size(NewTable1{2})) num2str(NewTable1{2}, '%.2f')];
Result5 = Result(1:5,:)                 % Display First 5 Rows

produces:

Result5 =

20/12/2014 08:55 0.63
20/12/2014 09:10 0.63
20/12/2014 09:25 0.63
20/12/2014 09:40 0.63
20/12/2014 09:55 0.63

I don’t understand how you get the ‘Table2’ values in your desired output. The values in column #2 for ‘Table2’ go from 0.3 at 18:00 to 0 at 21:19, so they would monotonically decrease (linearly by default) over the interval you list.

I will leave you to resolve that, and the way you want to merge the two tables. There is obviously something I do not understand in what you want to do. Meanwhile, my code snippet will perform the interpolation. You may have to experiment with it to get the result you want.

[n93]

Hey, thanks for your answer, it seems to have worked when I tried it with one of my tables!

In answer to your question, the data I've shown in my question are just samples - so it's just that in that sample the data happens to be decreasing linearly but over the entire dataset it's very varied. Does that help with making it more clear?

Would you be able to let me know how I could plot 'Result' on a graph?

Thanks again :D

[Star Strider]

My pleasure!

There are several likely ways to plot it. I would use the intermediate results ‘DTiv’ and ‘DT15’, with the datetick function:

figure(1)
plot(DTiv, DT15)
datetick('x', 'dd/mm/yyyy HH:MM')

You will likely want to experiment with the axis label format to get the result you want. I used the original format of your date and time data to specify how datetick displays the dates. You may want to change that.

As written in my ‘figure(1)’, the plot looks like:

[n93]

Thanks so much! :D

I'm guessing the data from the second table could be added by setting the start and end times for the interpolation to be the same and with the same interval?

[Star Strider]

My pleasure!

Yes. If the times are the same, you should have no problem in matching them up. All you would need to do is to is to add the ‘column #2’ data in the interpolated second file to the matching times in the first file. You would do that essentially as I created ‘NewTable1’. (Also, although the interpolation routines can extrapolate, I would not suggest you do so. Use the data and time ranges you have that the two files have in common.)

[Trung Hieu Le]

Hello Sirs,

How can I save or export this result (.char) to cell or .txt?

Thanks

Answer 2

[John D'Errico]

Convert the date/time info to a serial numeric value using datenum.

datenum('20/12/2014 08:55')
ans =
          9654.37152777778

Then use interp1 to interpolate, at values that are 15 minutes apart.

That interpolation increment is one quarter of an hour, so based on a day as 1, we get an increment of...

1/24/4
ans =
      0.0104166666666667

Answer 3

[Chris Barnhart]

You can convert your dates and times into serial date with:

dsn = datenum('20/12/2014 18:45','dd/mm/yyyy HH:MM');

Then you can verify it worked with:

datestr(dsn)

The serial date format is in unit of days since sometime long ago. Thus in serial date, 1 hour is 1/24

Once the times are in serial format, they be come the x values for the interpolation function interp1 I'll call these time_in

The y values are from your table(s). Not sure if y can be a Nx2 or it must be 1D (Nx1) I'll call these y1_in, y2_in

Finally you need the output times in serial date format. Pick a start and stop time. This could be manually:

dstart = datenum('20/12/2014 14:00','dd/mm/yyyy HH:MM')
dend = datenum('20/12/2014 23:00','dd/mm/yyyy HH:MM')

Or by finding min and max of the time_in values. However, you'll need to 'round' the min and max :

dmin = datenum('20/12/2014 14:05','dd/mm/yyyy HH:MM')
dmax = datenum('20/12/2014 22:25','dd/mm/yyyy HH:MM')
dstart = floor(dmin*24) / 24;
dend = ceil(dmax*24) / 24;
% check your calculations
datestr(dstart)
datestr(dend)

Finally create the output time series on 15 min intervals:

time_out = dstart : 0.25/24  : dend ; 
datestr(time_out)

Put assemble the output values with: y1_out = interp1(time_in, y1_in, time_out)

Use doc interp1 to see what some of the interpolation options are.