How do I create a timetable from a file that contains several data columns with associated time columns?

I've got files which contain data multiple from multiple sensors. Each sensor has it's own timestamp. Not all sensors have the same number of values.

Currently I'm splitting the file into several timetables. Then I'll merge and synchronize the timetables and finally fill the missing values.

Is there a better way to do this, since the original files contain several hundred thousand lines for 50-60 signals?

txtArray=  {'Sig1_Time'                'Sig1_Value' 'Signal2_Time'             'Sig2_Value' 'Sig3_Time'                'Sig3_Value';
            '4/28/2020 6:41:56.555 PM' '92.1822814' '4/28/2020 6:41:56.545 PM' '21.2642456' '4/28/2020 6:40:56.545 PM' '1.26';
            '4/28/2020 6:42:06.655 PM' '92.2822814' '4/28/2020 6:42:06.645 PM' '22.3538671' '4/28/2020 6:50:06.645 PM' '2.35';
            '4/28/2020 6:42:07.665 PM' '92.1922814'	'4/28/2020 6:42:07.655 PM' '22.2642456' ''                         '';
            ''                         ''	          '4/28/2020 6:42:08.665 PM' '23.2822436' ''                         '';
            ''	                       ''	          '4/28/2020 6:42:20.786 PM' '22.2642456' ''                         '';
           };

% find columns with timestamps
TimeCols = contains([txtArray(1,:)],'Time','IgnoreCase',true);

% convert times to numerical values
times = datetime([txtArray(2:end,TimeCols)],'InputFormat','M/d/yyyy h:m:ss.SSS a');

NoVars = sum(~TimeCols);

for j=1:NoVars
% create time column in TimeTable
  TC = times(:,j);
% create data column in TimeTable
  DC = cellfun(@(s) str2double(s),txtArray(:,2*j));
% merge arrays into Timetable, remove invalied (NaT) times
  TT(j).tt = array2timetable(DC(~isnat(TC)),'RowTimes',TC(~isnat(TC)));
end

% merge timetables
for j=1:NoVars
  if ~issorted(TT(j).tt);
    TT(j).tt=sortrows(TT(j).tt);
  end
  if j>1
    if j==2
      Ttable = TT(j-1).tt;
    end
    % synchronize removes dublicate times
    Ttable = synchronize(Ttable,TT(j).tt);   % https://mathworks.com/help/matlab/ref/timetable.synchronize.html
    Ttable = fillmissing(Ttable,'previous'); % https://mathworks.com/help/matlab/ref/fillmissing.html
  end
end

% fill missing values for the first lines which may still be empty
Ttable = fillmissing(Ttable,'next');

% rename properties
Ttable.Properties.VariableNames = {'Var1','Var2','Var3'};

[Voss]

There is a mistake in the code. times is built from rows 2:end of txtArray, but DC is built from all rows of txtArray.

So TC always contains one fewer element than DC. This doesn't throw an error because they are each logically indexed using a logical array the length of TC, but it causes the data in the timetables to be offset by one relative to the times.

txtArray=  {'Sig1_Time'                'Sig1_Value' 'Signal2_Time'             'Sig2_Value' 'Sig3_Time'                'Sig3_Value';
            '4/28/2020 6:41:56.555 PM' '92.1822814' '4/28/2020 6:41:56.545 PM' '21.2642456' '4/28/2020 6:40:56.545 PM' '1.26';
            '4/28/2020 6:42:06.655 PM' '92.2822814' '4/28/2020 6:42:06.645 PM' '22.3538671' '4/28/2020 6:50:06.645 PM' '2.35';
            '4/28/2020 6:42:07.665 PM' '92.1922814'	'4/28/2020 6:42:07.655 PM' '22.2642456' ''                         '';
            ''                         ''	          '4/28/2020 6:42:08.665 PM' '23.2822436' ''                         '';
            ''	                       ''	          '4/28/2020 6:42:20.786 PM' '22.2642456' ''                         '';
           };

1.26 goes with 6:40 PM, and 2.35 goes with 6:50 PM, right?

txtArray(:,end-[1 0])

ans = 6x2 cell array
    {'Sig3_Time'               }    {'Sig3_Value'}
    {'4/28/2020 6:40:56.545 PM'}    {'1.26'      }
    {'4/28/2020 6:50:06.645 PM'}    {'2.35'      }
    {0x0 char                  }    {0x0 char    }
    {0x0 char                  }    {0x0 char    }
    {0x0 char                  }    {0x0 char    }

Then I run your code ...

% find columns with timestamps
TimeCols = contains([txtArray(1,:)],'Time','IgnoreCase',true);
% convert times to numerical values
times = datetime([txtArray(2:end,TimeCols)],'InputFormat','M/d/yyyy h:m:ss.SSS a');
NoVars = sum(~TimeCols);
for j=1:NoVars
% create time column in TimeTable
  TC = times(:,j);
% create data column in TimeTable
  DC = cellfun(@(s) str2double(s),txtArray(:,2*j));
% merge arrays into Timetable, remove invalied (NaT) times
  TT(j).tt = array2timetable(DC(~isnat(TC)),'RowTimes',TC(~isnat(TC)));
end

And now, 2.35 is gone, and 1.26 incorrectly goes with 18:50 instead of 18:40:

TT(3).tt

ans = 2x1 timetable
            Time            Var1
    ____________________    ____

    28-Apr-2020 18:40:56     NaN
    28-Apr-2020 18:50:06    1.26

[Matthias Wollnik]

Thanks for the hint, I hadn't noticed this before, probably because at the end the missing data is filled.

I fixed it like this, using the same 2:end for DC and moving the fillmissing behind the loop for a more comparable table.

txtArray=  {'Sig1_Time'                'Sig1_Value' 'Signal2_Time'             'Sig2_Value' 'Sig3_Time'                'Sig3_Value';
            '4/28/2020 6:41:56.555 PM' '92.1822814' '4/28/2020 6:41:56.545 PM' '21.2642456' '4/28/2020 6:40:56.545 PM' '1.26';
            '4/28/2020 6:42:06.655 PM' '92.2822814' '4/28/2020 6:42:06.645 PM' '22.3538671' '4/28/2020 6:50:06.645 PM' '2.35';
            '4/28/2020 6:42:07.665 PM' '92.1922814'	'4/28/2020 6:42:07.655 PM' '22.2642456' ''                         '';
            ''                         ''	          '4/28/2020 6:42:08.665 PM' '23.2822436' ''                         '';
            ''	                       ''	          '4/28/2020 6:42:20.786 PM' '22.2642456' ''                         '';
           };

% find columns with timestamps
TimeCols = contains([txtArray(1,:)],'Time','IgnoreCase',true);

% convert times to numerical values
times = datetime([txtArray(2:end,TimeCols)],'InputFormat','M/d/yyyy h:m:ss.SSS a');

NoVars = sum(~TimeCols);

for j=1:NoVars
% create time column in TimeTable
  TC = times(:,j);
% create data column in TimeTable
  DC = cellfun(@(s) str2double(s),txtArray(2:end,2*j));      % --- use the same 2:end as for times
% merge arrays into Timetable, remove invalied (NaT) times
  TT(j).tt = array2timetable(DC(~isnat(TC)),'RowTimes',TC(~isnat(TC)));
end

% merge timetables
for j=1:NoVars
  if ~issorted(TT(j).tt);
    TT(j).tt=sortrows(TT(j).tt);
  end
  if j>1
    if j==2
      Ttable = TT(j-1).tt;
    end
    % synchronize removes dublicate times
    Ttable = synchronize(Ttable,TT(j).tt);   % https://mathworks.com/help/matlab/ref/timetable.synchronize.html
  end
end

% --- moved the first "fillmissing" behind the loop for a more comparable table
Ttable = fillmissing(Ttable,'previous'); % https://mathworks.com/help/matlab/ref/fillmissing.html
% fill missing values for the first lines which may still be empty
Ttable = fillmissing(Ttable,'next');

% rename properties
Ttable.Properties.VariableNames = {'Var1','Var2','Var3'};

Do you know a better way to create a synchronized timetable, or is this the way to do it?

[Voss]

See my answer: https://www.mathworks.com/matlabcentral/answers/2152625-how-do-i-create-a-timetable-from-a-file-that-contains-several-data-columns-with-associated-time-colu#answer_1516325

[Matthias Wollnik]

Great! Thank you.

[Voss]

You're welcome!

Answer 1

[Voss]

txtArray=  {'Sig1_Time'                'Sig1_Value' 'Signal2_Time'             'Sig2_Value' 'Sig3_Time'                'Sig3_Value';
            '4/28/2020 6:41:56.555 PM' '92.1822814' '4/28/2020 6:41:56.545 PM' '21.2642456' '4/28/2020 6:40:56.545 PM' '1.26';
            '4/28/2020 6:42:06.655 PM' '92.2822814' '4/28/2020 6:42:06.645 PM' '22.3538671' '4/28/2020 6:50:06.645 PM' '2.35';
            '4/28/2020 6:42:07.665 PM' '92.1922814'	'4/28/2020 6:42:07.655 PM' '22.2642456' ''                         '';
            ''                         ''	          '4/28/2020 6:42:08.665 PM' '23.2822436' ''                         '';
            ''	                       ''	          '4/28/2020 6:42:20.786 PM' '22.2642456' ''                         '';
           };

% find columns with timestamps
TimeCols = contains(txtArray(1,:),'Time','IgnoreCase',true);

% other columns are data
DataCols = ~TimeCols;

% make sure the number of time columns and data columns is the same
assert(nnz(TimeCols) == nnz(DataCols))

% convert times to datetime values
times = datetime(txtArray(2:end,TimeCols),'InputFormat','M/d/yyyy h:m:ss.SSS a')

times = 5x3 datetime array
   28-Apr-2020 18:41:56   28-Apr-2020 18:41:56   28-Apr-2020 18:40:56
   28-Apr-2020 18:42:06   28-Apr-2020 18:42:06   28-Apr-2020 18:50:06
   28-Apr-2020 18:42:07   28-Apr-2020 18:42:07   NaT                 
   NaT                    28-Apr-2020 18:42:08   NaT                 
   NaT                    28-Apr-2020 18:42:20   NaT                 

% convert data to numeric
data = str2double(txtArray(2:end,DataCols)) % !!! 2:end here, to be consistent with the times !!!

data = 5×3

92.1823 21.2642 1.2600 92.2823 22.3539 2.3500 92.1923 22.2642 NaN NaN 23.2822 NaN NaN 22.2642 NaN

<mw-icon class=""></mw-icon>

<mw-icon class=""></mw-icon>

% for each column, take the times and data where the times are not NaT
% then sort each by time and put them in a timetable
good_time = ~isnat(times);
TT = [];
for j = 1:size(times,2)
    TC = times(good_time(:,j),j);
    DC = data(good_time(:,j),j);
    [TC,idx] = sort(TC);
    DC = DC(idx);
    TT(j).tt = array2timetable(DC,'RowTimes',TC);
end

% synchronize all timetables (using a comma-separated list constructed
% from the tt field of each element of the TT struct array), then 
% fillmissing and fillmissing
Ttable = fillmissing(fillmissing(synchronize(TT.tt),'previous'),'next');

% modify the variable names
Ttable.Properties.VariableNames = {'Var1','Var2','Var3'}

Ttable = 10x3 timetable
            Time             Var1      Var2     Var3
    ____________________    ______    ______    ____

    28-Apr-2020 18:40:56    92.182    21.264    1.26
    28-Apr-2020 18:41:56    92.182    21.264    1.26
    28-Apr-2020 18:41:56    92.182    21.264    1.26
    28-Apr-2020 18:42:06    92.182    22.354    1.26
    28-Apr-2020 18:42:06    92.282    22.354    1.26
    28-Apr-2020 18:42:07    92.282    22.264    1.26
    28-Apr-2020 18:42:07    92.192    22.264    1.26
    28-Apr-2020 18:42:08    92.192    23.282    1.26
    28-Apr-2020 18:42:20    92.192    22.264    1.26
    28-Apr-2020 18:50:06    92.192    22.264    2.35