What is good way to import and scrub several Change of Variable (Mismatched Time-series) data sets, so they can be compared.

I am trying to import several sets of time series data. The issue is that the data is collected when a variable passes a threshold, There is no set sample time. I have attached the output cvs file that I am trying to import. I have also taken a snip as an example.

I am trying to figure out a good way to import the data so it is easy to analyze, i.e. create a new set (Normalized_Temp=(Temp_2-Temp_1)/Amb_Temp), find hourly averages of each set, ect. My current problem is that no two data points have the same timestamp, I arranged the data in excel to show this below.

How do I resolve this in Matlab, what structure should this be imported as and how can they be scrubbed to analyze against each other.

Answer 1

[Peter Perkins]

Corbyn, if you have access to the latest release, R2016b, you can use timetables:

>> t1 = table2timetable(readtable('Datasets.xlsx','Range','A1:B5'))
t1 =
          TimeStamp         Temp1___F_
    ____________________    __________
    05-Oct-2016 12:20:56    74.831    
    05-Oct-2016 13:17:15    74.997    
    05-Oct-2016 14:07:33    75.159    
    05-Oct-2016 14:51:19    75.319    
>> t2 = table2timetable(readtable('Datasets.xlsx','Range','D1:E4'))
t2 =
          TimeStamp         Temp2___F_
    ____________________    __________
    05-Oct-2016 13:07:14    74.662    
    05-Oct-2016 13:52:59    74.831    
    05-Oct-2016 14:39:46    74.997    
>> t3 = table2timetable(readtable('Datasets.xlsx','Range','G1:H13'))
t3 =
          TimeStamp         AmbTemp___F_
    ____________________    ____________
    05-Oct-2016 12:32:33    55.586      
    05-Oct-2016 12:52:21    55.439      
    05-Oct-2016 13:07:15    55.586      
    05-Oct-2016 13:17:17    56.027      
    05-Oct-2016 13:28:53    56.174      
    05-Oct-2016 13:34:33    56.318      
    05-Oct-2016 13:53:00    56.466      
    05-Oct-2016 14:07:35    56.611      
    05-Oct-2016 14:25:11    56.905      
    05-Oct-2016 14:33:17    56.759      
    05-Oct-2016 14:39:47    56.905      
    05-Oct-2016 14:51:21    57.345      
>> t4 = table2timetable(readtable('Datasets.xlsx','Range','J1:K3'));
>> t4.Mode = categorical(t4.Mode)
t4 =
          TimeStamp         Mode 
    ____________________    _____
    05-Oct-2016 13:39:47    Test 
    05-Oct-2016 13:43:34    Start
>> t5 = table2timetable(readtable('Datasets.xlsx','Range','M1:N5'))
t5 =
          TimeStamp         Input
    ____________________    _____
    05-Oct-2016 13:39:30     4   
    05-Oct-2016 13:39:46    10   
    05-Oct-2016 13:43:33     9   
    05-Oct-2016 13:43:48     3

Not exactly sure what you want as your result, but here's one possibility:

>> tt = synchronize(t1,t2,t3,t5,'minutely','spline');
>> tt = synchronize(tt,t4,'first','previous')
tt =
          TimeStamp         Temp1___F_    Temp2___F_    AmbTemp___F_     Input        Mode    
    ____________________    __________    __________    ____________    _______    ___________
    05-Oct-2016 12:20:00    74.828        74.481        55.097          -4892.4    <undefined>
    05-Oct-2016 12:21:00    74.831        74.485        55.167            -5172    <undefined>
[snip]
    05-Oct-2016 13:39:00    75.065         74.78        56.401          -9.2975    <undefined>
    05-Oct-2016 13:40:00    75.068        74.784        56.413            14.62    Test       
    05-Oct-2016 13:41:00    75.071        74.788        56.423           27.641    Test       
    05-Oct-2016 13:42:00    75.074        74.791        56.432           29.399    Test       
    05-Oct-2016 13:43:00    75.078        74.795        56.439           19.523    Test       
    05-Oct-2016 13:44:00    75.081        74.798        56.445           -2.353    Start      
    05-Oct-2016 13:45:00    75.084        74.802         56.45          -36.599    Start   
[snip]   
    05-Oct-2016 14:51:00    75.317        75.035        57.339           -48111    Start      
    05-Oct-2016 14:52:00    75.321        75.039        57.354           -49814    Start

Now everything is synchronized to the same time vector, and you can compute things like (Temp_2-Temp_1)/Amb_Temp). You can also use synchronize to aggregate rather than interpolate, so for example, compute hourly averages.

If you don't have R2016b, you can still do all that, but you'll need to do it "by hand" using interp1 on datetimes. Hope this helps.

[Corbyn Knotts]

Peter, I did not know about timetables; they work perfectly for what I am doing. Thank you.