Fill in the missing time stamps in measurment data when the system is runnign but not when the system was OFF
Show older comments
Hello everyone,
I have 3 measurements in 3 excel sheets, each having two columns : Time stamp, State
The data is more or less continuously recorded (with different time resolution in 3 sheets) when the system was running and is discontinuous when the system was OFF.
What i want is for all the time stamps to match each other so that i can do my analysis.
I tried to import it in Matlab, and match the time stamps. I got some results by making the whole data continuous (i.e. filling in the missing time stamps). However this is not what i want as i would like to keep the discontinuity when the system was OFF so that the histogram data doesnt count unnecessary information.
I have attached a portion of the 3 excel sheets for example (In the raw data the discontinuity also happens for DAY as the system was turned OFF at night).
As one can see Data1.xlsx has the best time resolution and i would like if Data2 and Data3 could also match this time by filling in the missing points. However one can also see that there is a huge discontinuity between 12:53 and 14:01 which i would like to keep that way.
I kindly request someone to help me with this problem.
Accepted Answer
Data1 = readtimetable('Data1.xlsx');
Data2 = readtimetable('Data2.xlsx');
Data3 = readtimetable('Data3.xlsx');
Maybe one of the following approaches using synchronize will work for what you want to do. The difference is how the missing State values are filled in.
1. Use NaNs for missing State values:
AllData = synchronize(Data1,Data2,Data3,Data1.Time);
disp(AllData)
2. Use the nearest State value that exists:
AllData = synchronize(Data1,Data2,Data3,Data1.Time,'nearest');
disp(AllData)
3 Comments
Sachin Hegde
on 16 Jul 2024
You're welcome!
Yes, that's possible. You'll need to define a threshold that specifies the maximum size of the time gap that should be filled in; below I use 59 minutes. Gaps in time whose size is at or smaller than the threshold are filled in, and gaps larger than the threshold are effectively left in place.
The process is: First, the timestamps of the start and end of gaps larger than the threshold are stored, then the synchronized timetable is retimed to impose a regular 1-second interval (thus filling in all gaps, regardless of size), and finally any times in the retimed timetable that are within any large gap are removed (thus removing the filled-in data from the large gaps and effectively re-imposing the large gaps).
Data1 = readtimetable('Data1.xlsx');
Data2 = readtimetable('Data2.xlsx');
Data3 = readtimetable('Data3.xlsx');
% synchronize like before:
AllData = synchronize(Data1,Data2,Data3,Data1.Time,'nearest');
% define a gap threshold:
gap_threshold = minutes(59);
% find the locations of gaps bigger than the threshold:
idx = find(diff(AllData.Time) > gap_threshold);
% record the start and end times of these large gaps:
gap_starts = AllData.Time(idx);
gap_ends = AllData.Time(idx+1);
% retime the timetable, resulting in a 1-second uniform timetable (i.e.,
% all gaps filled in):
AllData = retime(AllData,'regular','nearest','TimeStep',seconds(1));
% now remove the data from within the large gaps:
for ii = 1:numel(gap_starts)
remove_idx = AllData.Time > gap_starts(ii) & AllData.Time < gap_ends(ii);
AllData(remove_idx,:) = [];
end
% check the result:
disp(AllData)
Sachin Hegde
on 16 Jul 2024
More Answers (0)
Categories
Find more on Data Preprocessing in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
