Matching 2 date column in Matlab

Hi, I have a matrix called "a" which consists of a column that should contain the date and time from (01/01/2015 00:00:00) to (31/12/2015 23:54:00), with interval 6mins, the 2nd and 3rd columns contain the measured water level in "m" of the corresponding date in the first column. The dates and time in the first column are not all in the interval of 6 mins. So, for example, in between row 28321 and row 28323, there are missing dates with interval of 6 mins which I would like to fill in. My aim is to add the missing dates into column 1 so I can have a column with complete date and time range from (01/01/2015 00:00:00) to (31/12/2015 23:54:00), with interval of 6mins. Attached is the example of my array. Thanks!

Answer 1

[Peter Perkins]

Samantha, your question isn't all that clear. The first thing is I recommend you use a table, not a cell array. With that, and using datetimes, here's one way to do what you might be asking for:

Create some short data with two gaps.

>> d = datetime(2015,2,28,23,[0 6 24 30 48 54]',0);
>> x = [.91; .91; .73; .74; .68; .66];
>> y = [.97; .93; .74; .76; .67; .65];
>> t = table(d,x,y)
t = 
             d               x       y  
    ____________________    ____    ____
    28-Feb-2015 23:00:00    0.91    0.97
    28-Feb-2015 23:06:00    0.91    0.93
    28-Feb-2015 23:24:00    0.73    0.74
    28-Feb-2015 23:30:00    0.74    0.76
    28-Feb-2015 23:48:00    0.68    0.67
    28-Feb-2015 23:54:00    0.66    0.65

Find the gaps in the data.

>> dt = diff(t.d)
dt = 
   00:06:00
   00:18:00
   00:06:00
   00:18:00
   00:06:00
>> gaps = d(find(dt~= minutes(6)))
gaps = 
   28-Feb-2015 23:06:00
   28-Feb-2015 23:30:00

Create a time vector with no gaps, broadcast the x and y values out to similarly-sized vectors.

>> dComplete = (d(1):minutes(6):d(end))';
>> [~,i] = ismember(d,dComplete);
>> xComplete = NaN(size(dComplete));
>> xComplete(i) = x;
>> yComplete = NaN(size(dComplete));
>> yComplete(i) = y;
>> tComplete = table(dComplete,xComplete,yComplete)
tComplete = 
         dComplete          xComplete    yComplete
    ____________________    _________    _________
    28-Feb-2015 23:00:00    0.91         0.97     
    28-Feb-2015 23:06:00    0.91         0.93     
    28-Feb-2015 23:12:00     NaN          NaN     
    28-Feb-2015 23:18:00     NaN          NaN     
    28-Feb-2015 23:24:00    0.73         0.74     
    28-Feb-2015 23:30:00    0.74         0.76     
    28-Feb-2015 23:36:00     NaN          NaN     
    28-Feb-2015 23:42:00     NaN          NaN     
    28-Feb-2015 23:48:00    0.68         0.67     
    28-Feb-2015 23:54:00    0.66         0.65

[Samantha Chong]

Hi Peter,

Thanks for your reply. Thats exactly what I'm looking for :)

Cheers, Sam