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

Question

Matthias Wollnik 2024-9-13

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2152625-how-do-i-create-a-timetable-from-a-file-that-contains-several-data-columns-with-associated-time-colu

评论： Voss 2024-9-17

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'};

5 个评论
显示 3更早的评论隐藏 3更早的评论

Voss 2024-9-13

编辑：Voss 2024-9-13

在 MATLAB Online 中打开

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 2024-9-16

在 MATLAB Online 中打开

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?

Matthias Wollnik 2024-9-17

Great! Thank you.

Voss 2024-9-17

You're welcome!

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Voss 2024-9-13

编辑：Voss 2024-9-13

在 MATLAB Online 中打开

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

5 个评论
显示 3更早的评论隐藏 3更早的评论

采纳的回答

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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

5 个评论 显示 3更早的评论隐藏 3更早的评论

采纳的回答

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

5 个评论
显示 3更早的评论隐藏 3更早的评论

0 个评论
显示 -2更早的评论隐藏 -2更早的评论