Conditionally select rows and add to new table

Question

Jonathan Cheong 2020-11-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/661383-conditionally-select-rows-and-add-to-new-table

回答： Mathieu NOE 2020-11-30

采纳的回答： Mathieu NOE

在 MATLAB Online 中打开

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

5 个评论
显示 3更早的评论隐藏 3更早的评论

Mathieu NOE 2020-11-27

在 MATLAB Online 中打开

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 2020-11-30

编辑：Jonathan Cheong 2020-11-30

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 2020-11-30

在 MATLAB Online 中打开

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 2020-11-30

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/661383-conditionally-select-rows-and-add-to-new-table#answer_560023

在 MATLAB Online 中打开

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');