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

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!!

[Guillaume]

Is dbuid-13 the only sensor you want to check? If not what are the rules for the others?

Can we assume that the timetable is always ordered and that this order is always in reverse chronological order?

[Erica Corradi]

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]

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.

Answer 1

[Guillaume]

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.

[Erica Corradi]

Thanks for your answer! The sequence is important: it must be from bed/in to bed/out. Your code give me an error: Function 'subsindex' is not defined for values of class 'duration'.

Error in AAL (line 114) 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)

[Guillaume]

Function 'subsindex' is not defined for values of class 'duration

Have you got a variable called hours that shadows the hours function? If so you'll have to rename that variable.

The sequence is important: it must be from bed/in to bed/out.

So what should happen if the sequence starts with bed/out as in your example data? The initial bed/out should be considered an error? Or just ignored? Either is trivial to implement, I just need to know.

[Erica Corradi]

Ah okay for the change of name of the variable. Regarding the sequence, if it starts with bed/out, I want to ignore it. Furthermore, the code give me another error in the same row seen before: Data inputs must be the same size, or any of them can be a scalar.

[Guillaume]

if it starts with bed/out, I want to ignore it.

before the hourdiff = ... line:

if tab_dbuid13.Column1type == 'bed/out'
    tab_dbuid13(1, :) = [];   %ignore 1st row if it's a bed/out
    dbuid13_rows(1) = [];     %and ignore corresponding original row index
end

[Erica Corradi]

Maybe there is an error in the code. I get a strange vector 'isduplicate' because it has this dimension: 399x1. But tab_dbuid13 dimension is 360x5. Why are the rows increased?

[Guillaume]

Before the line

tab_dbuid13(isduplicate, :) = [];  %remove from working table

both tab_dbuid13 and isduplicate have 399 rows. After that line, after the duplicates have been removed from the working table (so we can now find consecutive in/out) the two are obviously different size.

I made a slight mistake in my last comment, the if should be:

if tab_dbuid13.Column1type(1) == 'bed/out'

As it was it didn't remove the first bed/out (or anything else).

You also need to remove the last row if it's a bed/in, so:

if tab_dbuid13.Column1type(end) == 'bed/in'
   tab_dbuid13(end, :) = [];
   dbuid13_rows(end) = [];
end

[Guillaume]

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

[Erica Corradi]

Thank you very much!!!