Remove rows with less than certain amount of measurements from timetable

Hi all,

I have a 517x2 timetable that I've reduced based on some conditions shown in my code below. Next, I want to remove any rows with measurements that do not contribute a full day's worth of measurements. A full day contains 8 measurements, so in the photo we see that rows with April 19 and May 6 dates would be removed, but May 7 and 8 would not since they contain 8 measurements per day each. Please let me know if this does not make sense. Any tips are appreciated, this is my first time using timetables / tables. Thank you very much in advance.

%%  Hm0 <4m &  Tp > 8s

Hm0_OM = Hm0_yy';
Tp_OM = Tp_yy';
dnum_OM = dnum_yy';

% Create table with values
OM_occ = timetable(dnum_OM, Hm0_OM, Tp_OM);

%Remove values based on O&M requirements
OM_occ(OM_occ.Hm0_OM > 1.5,:)=[];
OM_occ(OM_occ.Tp_OM > 8,:)=[];

% Remove rows that do not contribute to a full day's worth of measurements (8 per day)

[Hank]

If you're willing to share the data, could you attach a .mat file with the time table?

[gd]

Hi Henry,

Please see attached .mat

Answer 1

[Hank]

I have some idea about getting the times and unique times

T = tab.Time
uT = unique(T);

and then checking how many times each unique time appears in T in a for loop. Let me know if it needs tweaking.

rm = zeros(size(T),'logical'); % array tracking rows to be removed
for i=1:length(uT)
    A = uT(i)==T; % logical array, size of T
    if sum(A)<8 % sum(A) counts the entries with that unique datetime
        rm = rm | A; % add the rows to the rm array by logical or.
    end
end
T(rm,:) = []; % delete rows that didn't match spec

If we start removing rows within the loop, the loop index will lose sync with the array, hence the rm array.

[gd]

Hi Henry,

Thank you for taking time to help! Implementing this, I first get that uT is the same size as T since all dates are unique. Then, with the second part of your code, I get that T = 0x1 datetime array. I think I understand what you are attempting to do, but may need some time to work through it.

[Hank]

Gabrielle, its a neat problem, fun to attempt.

Thanks for sharing the data, now I'll know if what I say has any bugs. I think it will be possible to strip the datetime of the H:M:S before running unique. I'm trying to figure out how. I don't manipulate datetime objects that much.

[Hank]

Things got maybe a little out of hand, but I believe this works. Let me know if you want more explainer

%%
load('OM_occ.mat');

%%
% convert DT array to cell array of date strings
times = OM_occ.dnum_OM; % get copy of times column
times.Format = 'dd-MMM-yyy'; % Set DT format to only DMY, (remove HMS)
% this way char(T(i)) returns '19-Jan-1980'
tchar = arrayfun(@(t) char(t), times ,'uni',0); % casting DT to char in a loop returns cell array
[~,ui] = unique(tchar); % unique function compares cell strings like we want.
% ui are the indices of the unique array elements
uT = tchar(ui); % unique datetimes

%%
rm = zeros(size(times),'logical'); % array tracking rows to be removed
for date=uT' % for each unique time
    A = strcmp(date,tchar); % times that are the same date as 'date'
    if sum(A)<8 % sum(A) counts the entries with that unique datetime
        rm = rm | A; % add the rows to the rm array by logical or.
    end
end

OM_occ_trim = OM_occ(~rm,:); % Keep rows that are not in rm.

[gd]

Henry,

Thank you so much for the help. I appreciate you putting in the time to help me. This looks great, thank you again and be well!

[Hank]

The trick was finding a way to compare the datetime objects that would be true if they're the same date (ignoring the time of day). I think converting to strings was a sloppy way to do this, but it allows us to use the unique function and to count dates that are the same with strcmp.

I'd love to see if someone comes up with a cleaner method.