MATLAB: How to merge interpolated data into an existing timetable

interpolated data merging in a timetable

Hi,

i have a long uni variate timeseries data with 1 minute resolution. Here is an attachment of one part of this data set,shifted into timetable.

this attached data has one time slot ( 28 Mar 2017 21:49:00 to 10 Apr 2017 10:28:00) having negative value, which should not be negative. so i have converted this negative value to NaN, and then this timerange data is interpolated. i have succesfully interpolated this timeslot data. but i am not getting a way to put this interpolated data into the existing time table. caz i need full timetable at the end, with the interpolated values as well . Here is my code, please can any one guide me by looking at my code, what am i missing?

one thing more, when i run this code , it displayed clearly the interpolated data with the existing data in the plot, but with a warning "Warning: Columns of data containing NaN values have been ignored during interpolation. " how to get rid of this warning?

Looking forward to any guidance anxiously…

load OneMinute_ObsData;
plot(OneMinute_ObsData.timmendorf_time,OneMinute_ObsData.timmendorf_waterlevel);
for i= 1:size(OneMinute_ObsData,1)
                if( OneMinute_ObsData.timmendorf_waterlevel(i,1)< 0)        
                          
                   OneMinute_ObsData.timmendorf_waterlevel(i,1) = NaN;
                end
end
Timerange_for_interpolation=[datetime('2017-3-28 21:49:00'):minutes(1):datetime('2017-4-10 10:28:00')]';
V = interp1(OneMinute_ObsData.timmendorf_time, OneMinute_ObsData.timmendorf_waterlevel, Timerange_for_interpolation, 'spline');
% Plot the interpolated points.
hold on
plot(Timerange_for_interpolation,V,'r');

Best Answer

Really this is best done with fillmissing. It's just two lines:

>> X = [1;2;-3;-4;5;6];
>> tt = timetable(X,'RowTimes',datetime(2018,12,27,0,0:5,0))
tt =
  6×1 timetable
            Time            X 
    ____________________    __
    27-Dec-2018 00:00:00     1
    27-Dec-2018 00:01:00     2
    27-Dec-2018 00:02:00    -3
    27-Dec-2018 00:03:00    -4
    27-Dec-2018 00:04:00     5
    27-Dec-2018 00:05:00     6
>> tt.X(tt.X<0) = NaN
tt =
  6×1 timetable
            Time             X 
    ____________________    ___
    27-Dec-2018 00:00:00      1
    27-Dec-2018 00:01:00      2
    27-Dec-2018 00:02:00    NaN
    27-Dec-2018 00:03:00    NaN
    27-Dec-2018 00:04:00      5
    27-Dec-2018 00:05:00      6
>> tt1 = fillmissing(tt,"spline")
tt1 =
  6×1 timetable
            Time            X
    ____________________    _
    27-Dec-2018 00:00:00    1
    27-Dec-2018 00:01:00    2
    27-Dec-2018 00:02:00    3
    27-Dec-2018 00:03:00    4
    27-Dec-2018 00:04:00    5
    27-Dec-2018 00:05:00    6

Related Solutions

MATLAB: Timezone to UTC: How to add duration vector to datetime vector

Frank, what's up is Daylight Saving Time, which occurred on March 8th this year. I'm guessing you knew that, but got mixed up in the time arithmetic. Here's what's going on. You have this:

>> time = ['2015/03/08 00:00';'2015/03/08 01:00';'2015/03/08 03:00';'2015/03/08 04:00'];
>> Time = datenum(time,'yyyy/mm/dd HH:MM')
Time =
                    736031
          736031.041666667
                736031.125
          736031.166666667

datenums don't know anything about time zones or DST. Those times mean whatever you want them to mean. Since you then do this ...

>> date = datetime(Time,'ConvertFrom','datenum','Timezone','America/Montreal')
date = 
   08-Mar-2015 00:00:00
   08-Mar-2015 01:00:00
   08-Mar-2015 03:00:00
   08-Mar-2015 04:00:00

... I assume you mean that those datenums are to be interpreted as times in the America/Montreal time zone. But what you've left out is this:

>> date.Format = [date.Format ' z']
date = 
   08-Mar-2015 00:00:00 UTC-5
   08-Mar-2015 01:00:00 UTC-5
   08-Mar-2015 03:00:00 UTC-4
   08-Mar-2015 04:00:00 UTC-4

The second and third times are only an hour apart. Then this ...

>> [UTC_Offset,dst] = tzoffset(date);
>> Date = date - UTC_Offset
Date = 
   08-Mar-2015 06:00:00 UTC-4
   08-Mar-2015 07:00:00 UTC-4
   08-Mar-2015 07:00:00 UTC-4
   08-Mar-2015 08:00:00 UTC-4

... adds 5 hours to the first and second times, but adds only 4 hours to the third and fourth times. But you're doing the addition in the America/Montreal time zone. Adding 5 hours to the first two puts them after the DST shift @2am, which makes them look like you added 6 hours. In other words, your array is not UTC, it's shifted America/Montreal. The easiest thing to do to get UTC times is this:

>> date.TimeZone = 'UTC'
date = 
   08-Mar-2015 05:00:00 UTC
   08-Mar-2015 06:00:00 UTC
   08-Mar-2015 07:00:00 UTC
   08-Mar-2015 08:00:00 UTC

Hope this helps.

MATLAB: Comparing date time vectors.

There is, it's not hard, but this seems like such a strange thing to want to do that I feel like you must mean something else.

Cook up fake data:

>> rng default
>> t1 = datetime(1,1,2017,0,0,0):seconds(20):datetime(1,1,2017,0,3,40);
>> x1 = rand(size(t1))';
>> tt1 = timetable(x1,'RowTimes',t1)
tt1 =
  12×1 timetable
            Time              x1   
    ____________________    _______
    10-Jul-0006 00:00:00    0.81472
    10-Jul-0006 00:00:20    0.90579
    10-Jul-0006 00:00:40    0.12699
    10-Jul-0006 00:01:00    0.91338
    10-Jul-0006 00:01:20    0.63236
    10-Jul-0006 00:01:40    0.09754
    10-Jul-0006 00:02:00     0.2785
    10-Jul-0006 00:02:20    0.54688
    10-Jul-0006 00:02:40    0.95751
    10-Jul-0006 00:03:00    0.96489
    10-Jul-0006 00:03:20    0.15761
    10-Jul-0006 00:03:40    0.97059
>> t2 = datetime(1,1,2017,0,0,0):minutes(1):datetime(1,1,2017,0,3,0);
>> t2 = repelem(t2,randi([2 3],1,length(t2)));
>> x2 = rand(size(t2))';
>> tt2 = timetable(x2,'RowTimes',t2)
tt2 =
  10×1 timetable
            Time               x2   
    ____________________    ________
    10-Jul-0006 00:00:00     0.42176
    10-Jul-0006 00:00:00     0.91574
    10-Jul-0006 00:00:00     0.79221
    10-Jul-0006 00:01:00     0.95949
    10-Jul-0006 00:01:00     0.65574
    10-Jul-0006 00:02:00    0.035712
    10-Jul-0006 00:02:00     0.84913
    10-Jul-0006 00:02:00     0.93399
    10-Jul-0006 00:03:00     0.67874
    10-Jul-0006 00:03:00     0.75774

Find minutes with only two corresponding rows in tt2

>> t2u = unique(tt2.Time);
>> numRowsAtEachMinute = histcounts(tt2.Time,[unique(t2u); datetime(Inf,Inf,Inf)])
numRowsAtEachMinute =
     3     2     3     2
>> minutesWithOnlyTwoRows = t2u(numRowsAtEachMinute == 2)
minutesWithOnlyTwoRows = 
  2×1 datetime array
   10-Jul-0006 00:01:00
   10-Jul-0006 00:03:00

Add all NaN to tt2 for those times and sort

>> n = length(minutesWithOnlyTwoRows);
>> i = height(tt2) + (1:n);
>> tt2.x2(i) = NaN;
>> tt2.Time(i) = minutesWithOnlyTwoRows;
>> tt2 = sortrows(tt2)
tt2 =
  12×1 timetable
            Time               x2   
    ____________________    ________
    10-Jul-0006 00:00:00     0.42176
    10-Jul-0006 00:00:00     0.91574
    10-Jul-0006 00:00:00     0.79221
    10-Jul-0006 00:01:00     0.95949
    10-Jul-0006 00:01:00     0.65574
    10-Jul-0006 00:01:00         NaN
    10-Jul-0006 00:02:00    0.035712
    10-Jul-0006 00:02:00     0.84913
    10-Jul-0006 00:02:00     0.93399
    10-Jul-0006 00:03:00     0.67874
    10-Jul-0006 00:03:00     0.75774
    10-Jul-0006 00:03:00         NaN

Throw away the seconds for tt1 and combine

>> tt1.Time = dateshift(tt1.Time,'start','minute');
>> tt = [tt1 tt2]
tt =
  12×2 timetable
            Time              x1          x2   
    ____________________    _______    ________
    10-Jul-0006 00:00:00    0.81472     0.42176
    10-Jul-0006 00:00:00    0.90579     0.91574
    10-Jul-0006 00:00:00    0.12699     0.79221
    10-Jul-0006 00:01:00    0.91338     0.95949
    10-Jul-0006 00:01:00    0.63236     0.65574
    10-Jul-0006 00:01:00    0.09754         NaN
    10-Jul-0006 00:02:00     0.2785    0.035712
    10-Jul-0006 00:02:00    0.54688     0.84913
    10-Jul-0006 00:02:00    0.95751     0.93399
    10-Jul-0006 00:03:00    0.96489     0.67874
    10-Jul-0006 00:03:00    0.15761     0.75774
    10-Jul-0006 00:03:00    0.97059         NaN

Best Answer

Related Solutions

MATLAB: Timezone to UTC: How to add duration vector to datetime vector

MATLAB: Comparing date time vectors.

Related Question