Conditionally select rows and add to new table

For example, I have a 2000 x 3 table: Column_1: Datetime Column_2: Rain Column_3: Soil Moisture

The actual table has more variables but I plan to get to it once I know how to undergo the coding.

The idea is to append the rows to a new table if they meet these conditions:

1) While rain > 3 and ends when rain < 2 for 2 subsequent days

2) The 1st condition cannot be less than 4 days

I've been trying to code this out but have gone nowhere:

for i = 1:height(ddtable)
    ii = 0;
    finaldd(:,:) = [];
    while ddtable.RAIN > 3 
        ii = ii + 1;
        if ii < 4
            break
        elseif ii > 4
            finaldd(ddtable);
        end  
                   
    end
    
end

[Mathieu NOE]

hello

maybe if you would share also the input file we could help you further !

[Jonathan Cheong]

Hey Mathieu thanks for the quick response!

Sure here's the shortened file attached, shouldn't have any missing values but not cleaned.

For reference, RAIN is in mm.

If there's anything else let me know, because it's my 1st time doing something like this

[Mathieu NOE]

ok

so this is a first trial

I will not pretend it is 100% finished but I let you check this already

load('ddtable2.mat'); % ddtable2

date_ymd = ddtable2.DT;
date_days = datenum(date_ymd);
rain = ddtable2.RAIN;
SM = ddtable2.SM_12h;

samples = length(rain);
% first loop 
k = 0;
q = 0;

% let's analyse the data by buffer data slection and analysis
for ci = 1:length(rain)-3
    % cond1 : % 4 days and all with rain > 3
    buffer1 = rain(ci:ci+3); % 4 days buffer
    cond1 = all(buffer1>3);   % will be 1 if buffer is contiguous and all values are > 3
    if cond1 == 1
        k = k+1;
        ind_start1(k) = ci;
        ind_stop1(k) = ci+3;
    end
    
    % cond2 : % 2 days contiguous with rain < 2
    buffer2 = rain(ci:ci+1); % 2 days buffer
    cond2 = all(buffer2<2);   % contiguous and all < 2
    if cond2 == 1
        q = q+1;
        ind_start2(q) = ci;
        ind_stop2(q) = ci+1;
    end    
end

% now let's check when we have a ind_start2 just after one value of 
m = 0;
for ck = 1:length(ind_start1)
    ind = find(ind_start2>ind_start1(ck));
    if ~isempty(ind)
        m = m+1;
        ind_start22(m) = ind_start2(ind(1));
        ind_start11(m) = ind_start1(ck);
    end
end
%  keep only unique values 
[c2,ia2,ic2] = unique(ind_start22);
ind_start222 = ind_start22(ia2);
ind_start111 = ind_start11(ia2);

% finally , stack (concatenate) data that fullfills the condition
time_index = [];
data_rain = [];

for cc = 1:length(ia2)
    time_index = [time_index  (ind_start111(cc):ind_start222(cc))]; % concatenation
    data_rain = [data_rain; rain(ind_start111(cc):ind_start222(cc))]; % concatenation
end

figure(1),plot((1:samples),rain,'b',time_index,data_rain,'-*r');
legend('all data','selected data');

[Jonathan Cheong]

Wow, this is superb work. I've taken a look at it and understand most parts, thank you very much!

I've done a few changes to it because my goal is: Plot datetime against SM value with the index found.

Would you like to submit your comment as an answer so that I can mark it as accepted?

[Jonathan Cheong]

takerow = ddtable2(time_index,:);
figure(2)
y2 = takerow.SM_12h;
x = linspace(datetime('2011-07-14 00:00:00'),datetime('2020-01-01 00:00:00'),length (y2));
plot(x,y2)

This is the new code I've done for the plot. Once again, thanks!

Answer 1

[Mathieu NOE]

hello

so official answer below (modified original code)

good luck for the future !

load('ddtable2.mat'); % ddtable2
date_ymd = ddtable2.DT;
date_days = datenum(date_ymd);
rain = ddtable2.RAIN;
SM = ddtable2.SM_12h;
samples = length(rain);
% first loop 
k = 0;
q = 0;
% let's analyse the data by buffer data slection and analysis
for ci = 1:length(rain)-3
    % cond1 : % 4 days and all with rain > 3
    buffer1 = rain(ci:ci+3); % 4 days buffer
    cond1 = all(buffer1>3);   % will be 1 if buffer is contiguous and all values are > 3
    if cond1 == 1
        k = k+1;
        ind_start1(k) = ci;
        ind_stop1(k) = ci+3;
    end
    
    % cond2 : % 2 days contiguous with rain < 2
    buffer2 = rain(ci:ci+1); % 2 days buffer
    cond2 = all(buffer2<2);   % contiguous and all < 2
    if cond2 == 1
        q = q+1;
        ind_start2(q) = ci;
        ind_stop2(q) = ci+1;
    end    
end
% now let's check when we have a ind_start2 just after one value of 
m = 0;
for ck = 1:length(ind_start1)
    ind = find(ind_start2>ind_start1(ck));
    if ~isempty(ind)
        m = m+1;
        ind_start22(m) = ind_start2(ind(1));
        ind_start11(m) = ind_start1(ck);
    end
end
%  keep only unique values 
[c2,ia2,ic2] = unique(ind_start22);
ind_start222 = ind_start22(ia2);
ind_start111 = ind_start11(ia2);
% finally , stack (concatenate) data that fullfills the condition
time_index = [];
data_rain = [];
for cc = 1:length(ia2)
    time_index = [time_index  (ind_start111(cc):ind_start222(cc))]; % concatenation
    data_rain = [data_rain; rain(ind_start111(cc):ind_start222(cc))]; % concatenation
end
figure(1),plot((1:samples),rain,'b',time_index,data_rain,'-*r');
legend('all data','selected data');