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

43 次查看(过去 30 天)
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 个评论

请先登录,再进行评论。

采纳的回答

Voss
Voss 2024-9-13,16:25
编辑:Voss 2024-9-13,16:26
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 个)

类别

Help CenterFile Exchange 中查找有关 Time Series 的更多信息

产品


版本

R2023a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by