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
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')
% convert data to numeric
data = str2double(txtArray(2:end,DataCols)) % !!! 2:end here, to be consistent with the times !!!
% 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'}
0 个评论
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Time Series 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!