check database, save wrong rows in a new table and delete them from original database

1 次查看（过去 30 天）
Erica Corradi
Erica Corradi 2018-7-5
评论： Erica Corradi 2018-7-6
Hello everyone! I hope that someone can help me. I have a timetable in whose column named Column1streamId I have the name of my sensors, while in the Column1type column I have the type of sensor. I would like to check my timetable about how my sensors work. For example, the sensor called dbuid-13 (in the Column1streamId column) is the bed sensor and tells me if the patient is in / out of bed (in the Column1type column). Now I would like to analyze if for each bed/in corresponds to a bed/out (of course in chronological order) and I would like to check that between the two there is not a time greater than 24 hours. If there were more than 24 hours between the bed/in and the bed/out, I would like to save those rows in a new table, but delete them from my original database. Is there someone who can help me? Thanks in advance!!
  3 个评论
显示 1更早的评论
Erica Corradi
Erica Corradi 2018-7-5
I want to check also other sensors: dbuid-12 (the door of the fridge, that can be open/close door) and dbuid-3 (the entrance door, that can be open/close). For these sensors I want to know only if they are always in sequence (for each open door corresponds to a close door). The timetable is always ordered in reverse chronological order.
Paolo
Paolo 2018-7-5
Can we not adapt the solution from the previous question for this? With output times we can easily determine whether each output spans over less than 24 hours.

请先登录，再进行评论。

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

回答（1 个）

Guillaume
Guillaume 2018-7-5
编辑：Guillaume 2018-7-5
tab_night = sortrows(tab_night); %order timetable chronologically
dbuid13_rows = find(tab_night.Column1streamId == 'dbuid-13'); %get rows that correspond to sensor
tab_dbuid13 = tab_night(dbuid13_rows, :); %extract portion of table that corresponds to sensor
isduplicate = [false; diff(double(tab_dbuid13.Column1type)) == 0]; %find consecutive identical bed/in or bed/out
duplicate_rows = dbuid13_rows(isduplicate); %list of all the rows in original table that have duplicates
tab_dbuid13(isduplicate, :) = []; %remove from working table
dbuid13_rows(isduplicate) = []; %also remove from list of rows
%Now we can only have consecutive bed/in bed/out pairs (or bed/out bed/in pairs)
hourdiff = hours(tab_dbuid13.t(2:2:end) - tab_dbuid13.t(1:2:end)); %delay between corresponding bed/in - bed/out (or bed/out - bed/in)
delay_rows = dbuid13_rows(2*find(hourdiff >= 24)' - [1; 0]); %list of both rows which have a difference of 24 hours
error_rows = [duplicate_rows; delay_rows(:)]; %concatenate both lists of errors
error_table = tab_night(error_rows, :); %move all error rows into new table
error_table.reason = categorical([repelem({'duplicate'}, numel(duplicate_rows)), repelem({'delay'}, numel(delay_rows))]');
tab_night(error_rows, :) = []; %and delete
That's for dbuid-13 only.
Note that I don't check what your sequence of bed in/out starts with bed/in (or bed/out) so the difference is either between bed/in bed/out or bed/out bed/in. I wasn't sure if it was important.
  8 个评论
显示 6更早的评论
Guillaume
Guillaume 2018-7-6
So the whole code is:
tab_night = sortrows(tab_night); %order timetable chronologically
dbuid13_rows = find(tab_night.Column1streamId == 'dbuid-13'); %get rows that correspond to sensor
tab_dbuid13 = tab_night(dbuid13_rows, :); %extract portion of table that corresponds to sensor
isduplicate = [false; diff(double(tab_dbuid13.Column1type)) == 0]; %find consecutive identical bed/in or bed/out
duplicate_rows = dbuid13_rows(isduplicate); %list of all the rows in original table that have duplicates
tab_dbuid13(isduplicate, :) = []; %remove from working table
dbuid13_rows(isduplicate) = []; %also remove from list of rows
%Now we can only have consecutive bed/in bed/out pairs (or bed/out bed/in pairs)
%ensure we start with a bed/in and finish on a bed/out
if tab_dbuid13.Column1type(1) == 'bed/out'
tab_dbuid13(1, :) = []; %ignore 1st row if it's a bed/out
dbuid13_rows(1) = []; %and ignore corresponding original row index
end
if tab_dbuid13.Column1type(end) == 'bed/in'
tab_dbuid13(end, :) = []; %ignore 1st row if it's a bed/out
dbuid13_rows(end) = []; %and ignore corresponding original row index
end
hourdiff = hours(tab_dbuid13.t(2:2:end) - tab_dbuid13.t(1:2:end)); %delay between corresponding bed/in - bed/out (or bed/out - bed/in)
delay_rows = dbuid13_rows(2*find(hourdiff >= 24)' - [1; 0]); %list of both rows which have a difference of 24 hours
error_rows = [duplicate_rows; delay_rows(:)]; %concatenate both lists of errors
error_table = tab_night(error_rows, :); %move all error rows into new table
error_table.reason = categorical([repelem({'duplicate'}, numel(duplicate_rows)), repelem({'delay'}, numel(delay_rows))]');
tab_night(error_rows, :) = []; %and delete

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Matrices and Arrays 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by