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

1 view (last 30 days)
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 Comments
Erica Corradi
Erica Corradi on 5 Jul 2018
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 on 5 Jul 2018
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.

Sign in to comment.

Answers (1)

Guillaume
Guillaume on 5 Jul 2018
Edited: Guillaume on 5 Jul 2018
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 Comments
Guillaume
Guillaume on 6 Jul 2018
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

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!