MATLAB: Creating a table with missing endpoints

matrix transformation fill

Hi, consider the matrix below:
732315  108  1.9193000  1.9193000  1.9193000  1.9193000
732315  113  1.9193000  1.9193000  1.9060000  1.9060000
732315  114  1.9058000  1.9058000  1.9058000  1.9058000
732315  115  1.9060000  1.9061000  1.9060000  1.9060000
I want to transform the matrix into the following:
732315  108  1.9193000  1.9193000  1.9193000  1.9193000
732315  109     NaN             NaN             NaN             NaN 
732315  110     NaN             NaN             NaN             NaN 
732315  111     NaN             NaN             NaN             NaN 
732315  112     NaN             NaN             NaN             NaN 
732315  113  1.9193000  1.9193000  1.9060000  1.9060000
732315  114  1.9058000  1.9058000  1.9058000  1.9058000
732315  115  1.9060000  1.9061000  1.9060000  1.9060000
Any ideas how? thanks

Best Answer

Those look suspiciously like datenums, circa 2005. I'm gonna take a wild guess that 108 is 01:08. I might be completely wrong. If I'm not, use a timetable, and retime:
>> X = [732315 108 1.9193000 1.9193000 1.9193000 1.9193000
        732315 113 1.9193000 1.9193000 1.9060000 1.9060000
        732315 114 1.9058000 1.9058000 1.9058000 1.9058000
        732315 115 1.9060000 1.9061000 1.9060000 1.9060000];
>> T = array2table(X,'VariableNames',{'Date' 'Time' 'X1' 'X2' 'X3' 'X4'});
>> T.Date = datetime(T.Date,'ConvertFrom','datenum');
>> T.Time = hours(floor(T.Time/100)) + minutes(mod(T.Time,100));
>> T.TimeStamp = T.Date + T.Time;
>> T.Date = []; T.Time = [];
>> TT = table2timetable(T)
TT =
  4×4 timetable
         TimeStamp            X1        X2        X3        X4  
    ____________________    ______    ______    ______    ______
    03-Jan-2005 01:08:00    1.9193    1.9193    1.9193    1.9193
    03-Jan-2005 01:13:00    1.9193    1.9193     1.906     1.906
    03-Jan-2005 01:14:00    1.9058    1.9058    1.9058    1.9058
    03-Jan-2005 01:15:00     1.906    1.9061     1.906     1.906
>> TT = retime(TT,'minutely')
TT =
  8×4 timetable
         TimeStamp            X1        X2        X3        X4  
    ____________________    ______    ______    ______    ______
    03-Jan-2005 01:08:00    1.9193    1.9193    1.9193    1.9193
    03-Jan-2005 01:09:00       NaN       NaN       NaN       NaN
    03-Jan-2005 01:10:00       NaN       NaN       NaN       NaN
    03-Jan-2005 01:11:00       NaN       NaN       NaN       NaN
    03-Jan-2005 01:12:00       NaN       NaN       NaN       NaN
    03-Jan-2005 01:13:00    1.9193    1.9193     1.906     1.906
    03-Jan-2005 01:14:00    1.9058    1.9058    1.9058    1.9058
    03-Jan-2005 01:15:00     1.906    1.9061     1.906     1.906
Related Question