How to fine a range of values before and after a specific value with a condition

Hello,

Hope everyone is fine!

I have an issue regarding the data analysis in Matlab, if someone could support me.

I have a table of different columns (datetime, VAR1 and VAR2). I need to:

- first find a specific value of VAR1 (eg. X=5)

- then find a range of values of VAR2 before and after X, according to the values of VAR1 equal to 0.

I need to find something like that:

Datetime VAR1 VAR2

... 0 y1

... 0 y2

... 0 y3

... 0 y4

... 5 y5

... 0 y6

... 0 y7

... 0 y8

... 0 y9

[Johannes Fischer]

Your question is a bit unclear. What do you mean with 'according to the values of VAR1 equal to 0'.?

What would be the expected output in your shown example?

[Laty El]

I am sorry for the unclear question. Well, I need to find in the column "VAR1" a high value (eg. 5) with the condition: a range of rows (eg: 6 rows before and after with the value 0).

[Voss]

@Latifa EL BOUJDAINI: Can you have different number of rows before and after a 5? E.g., if you have 3 rows with 0 before the 5, and 6 rows with 0 after the 5, do you keep them all or do you just keep 3 before and 3 after?

[Laty El]

@_ For my case i need to find many 0 rows before and after, and i have to use the same number of rows (before and after).

Answer 1

[Star Strider]

Try this —

GetRange = @(range,Val,T,Col) strfind(T{:,Col}', [zeros(1,range) Val zeros(1,range)]);
Col2 = zeros(24,1);
Col2(randi(24,24,1)==5) = 5;
Table = table(datetime('now')+hours(0:23).', Col2, rand(24,1), 'VariableNames',{'Datetime','VAR1','VAR2'})

Table = 24×3 table
          Datetime          VAR1      VAR2  
    ____________________    ____    ________

    25-Feb-2022 14:16:05     0       0.94761
    25-Feb-2022 15:16:05     0       0.66998
    25-Feb-2022 16:16:05     0       0.80789
    25-Feb-2022 17:16:05     0       0.39264
    25-Feb-2022 18:16:05     0       0.28526
    25-Feb-2022 19:16:05     0       0.29068
    25-Feb-2022 20:16:05     0       0.44018
    25-Feb-2022 21:16:05     0       0.85058
    25-Feb-2022 22:16:05     5       0.50154
    25-Feb-2022 23:16:05     0       0.11087
    26-Feb-2022 00:16:05     5       0.68359
    26-Feb-2022 01:16:05     0       0.80208
    26-Feb-2022 02:16:05     0      0.053463
    26-Feb-2022 03:16:05     0       0.98176
    26-Feb-2022 04:16:05     0       0.69536
    26-Feb-2022 05:16:05     0       0.61461

idx = find(Table.VAR1 == 5)

idx = 3×1

9 11 20

range = 3;
start = GetRange(3,5,Table,2)

start = 17

for k = 1:numel(start)
    Out{k,:} = Table(start(k) : start(k)+range*2,:);
end
Out{:}

ans = 7×3 table
          Datetime          VAR1      VAR2  
    ____________________    ____    ________

    26-Feb-2022 06:16:05     0       0.11047
    26-Feb-2022 07:16:05     0       0.11028
    26-Feb-2022 08:16:05     0       0.81538
    26-Feb-2022 09:16:05     5       0.96058
    26-Feb-2022 10:16:05     0       0.97877
    26-Feb-2022 11:16:05     0      0.025627
    26-Feb-2022 12:16:05     0       0.48768

It will only detect and return regions with a centre value of 5 (in this example) and zeros within the specified range. So here, it does not return the values at 9 and 11 because they do not meet the criteria, and it does return the value with the centre value at 20 because it meets the criteria.

The ‘GetRange’ function arguments are ‘range’ the range to consider, here 3, ‘Val’ the value to compare (here 5), ‘T’ the table variable name (here ‘Table’), and ‘Col’ the column to test (here 2).

.

[Laty El]

@Star Strider Thank you so much, I will try it and give a feedback again.

[Star Strider]

As always, my pleasure!

Also, if the intervals on either side are not the same, my anonymous function changes to:

GetRange = @(before,after,Val,T,Col) strfind(T{:,Col}', [zeros(1,before) Val zeros(1,after)]);

The ‘before’ and ‘after’ (and ‘range’ in the original version) must be integers greater than zero.

Its behaviour and results it returns are essentially the same in both versions.

.

[Laty El]

@Star Strider ,

• I ve got this not: "Subscripting into a table using three or more subscripts (as in t(i,j,k)) is not supported. Always specify a row subscript and a variable subscript, as in t(rows,vars)."
• I f i want to find value greater than 0 of VAR1, i need to change idx = find(Table.VAR1 == 5) by idx = find(Table.VAR1 > 0), right?

[Star Strider]

The ‘idx’ assignment is simply there to show where the centre value is, and compare that to ‘start’. It has no other purpose in the code. Please do not use it.

The ‘Col’ argument in ‘GetRange’ selects the variable in the table and ‘VAR1’ corresponds to column 2.

So to use my code and ‘GetRange’ function, select a value for the ‘range’ argument and the others, and then use this part of my code:

range = 3;                                              % Number Of Zeros To Either Side Of a 'Val' Value
Val = 5;                                                % Value To Test For
T = Table;                                              % Table To Index Into
Col = 2;                                                % Corresponds To 'VAR1' Here
start = GetRange(range,Val,T,Col);                      % Return Starting Indices Of 'T' Sections
for k = 1:numel(start)
    Out{k,:} = Table(start(k) : start(k)+range*2,:);    % Return Sections Of 'T' Identified By 'GetRange'
end
Out{:}                                                  % Display Results

The ‘GetRange’ function arguments are ‘range’ the range to consider (here 3), ‘Val’ the value to compare (here 5), ‘T’ the table variable name (here ‘Table’), and ‘Col’ the column to test (here 2).

The results will be in the ‘Out’ cell array (as a section of the table).

.

[Laty El]

@Star Strider Thank you alot! Very helpful

[Star Strider]

As always, my pleasure!

[Laty El]

@Star Strider Ive got the results in cells, but i need them in table so i can easily plot my VAR. Could you help me in this?

[Star Strider]

It might be possible to simply vertically concatenate them into a single table, since the individual cells are actually segments of the original table.

See the documentation seciton on Expand Tables for an example. Also consider using the vertcat function.

Example —

GetRange = @(range,Val,T,Col) strfind(T{:,Col}', [zeros(1,range) Val zeros(1,range)]);
N = 48;
Col2 = zeros(N,1);
Col2(randi(24,N,1)==5) = 5;
Table = table(datetime('now')+hours(0:N-1).', Col2, rand(N,1), 'VariableNames',{'Datetime','VAR1','VAR2'})

Table = 48×3 table
          Datetime          VAR1      VAR2  
    ____________________    ____    ________

    27-Feb-2022 01:16:36     0       0.89736
    27-Feb-2022 02:16:36     5       0.86265
    27-Feb-2022 03:16:36     0       0.82162
    27-Feb-2022 04:16:36     0       0.41975
    27-Feb-2022 05:16:36     0       0.38681
    27-Feb-2022 06:16:36     0       0.78674
    27-Feb-2022 07:16:36     0       0.48412
    27-Feb-2022 08:16:36     0      0.033386
    27-Feb-2022 09:16:36     0      0.064861
    27-Feb-2022 10:16:36     0       0.73343
    27-Feb-2022 11:16:36     0       0.60178
    27-Feb-2022 12:16:36     0        0.2897
    27-Feb-2022 13:16:36     0       0.79207
    27-Feb-2022 14:16:36     0       0.34966
    27-Feb-2022 15:16:36     0        0.5766
    27-Feb-2022 16:16:36     5       0.34559

idx = find(Table.VAR1 == 5)

idx = 3×1

2 16 39

range = 3;
start = GetRange(3,5,Table,2)

start = 1×2

13 36

for k = 1:numel(start)
    Out{k,:} = Table(start(k) : start(k)+range*2,:);
end
Out{:}

ans = 7×3 table
          Datetime          VAR1      VAR2  
    ____________________    ____    ________

    27-Feb-2022 13:16:36     0       0.79207
    27-Feb-2022 14:16:36     0       0.34966
    27-Feb-2022 15:16:36     0        0.5766
    27-Feb-2022 16:16:36     5       0.34559
    27-Feb-2022 17:16:36     0      0.062332
    27-Feb-2022 18:16:36     0       0.28907
    27-Feb-2022 19:16:36     0       0.22721

ans = 7×3 table
          Datetime          VAR1     VAR2  
    ____________________    ____    _______

    28-Feb-2022 12:16:36     0      0.26452
    28-Feb-2022 13:16:36     0      0.50936
    28-Feb-2022 14:16:36     0      0.82911
    28-Feb-2022 15:16:36     5      0.55113
    28-Feb-2022 16:16:36     0      0.29853
    28-Feb-2022 17:16:36     0      0.51446
    28-Feb-2022 18:16:36     0      0.59557

Outcat = cat(1,Out{:})                                  % Vertically Concatenate Table Segments

Outcat = 14×3 table
          Datetime          VAR1      VAR2  
    ____________________    ____    ________

    27-Feb-2022 13:16:36     0       0.79207
    27-Feb-2022 14:16:36     0       0.34966
    27-Feb-2022 15:16:36     0        0.5766
    27-Feb-2022 16:16:36     5       0.34559
    27-Feb-2022 17:16:36     0      0.062332
    27-Feb-2022 18:16:36     0       0.28907
    27-Feb-2022 19:16:36     0       0.22721
    28-Feb-2022 12:16:36     0       0.26452
    28-Feb-2022 13:16:36     0       0.50936
    28-Feb-2022 14:16:36     0       0.82911
    28-Feb-2022 15:16:36     5       0.55113
    28-Feb-2022 16:16:36     0       0.29853
    28-Feb-2022 17:16:36     0       0.51446
    28-Feb-2022 18:16:36     0       0.59557

That should produce the desired result. If it does not, please clarify what you want to do.

.

[Laty El]

@Star Strider, Yes, this works good, Thank you. Now, I am trying to apply this on all values greater than 0, not equals to 5 and with the same range = 3;

I tried this: idx = find(Table.VAR1 > 0). However, i got error when using this function :start = GetRange(3,(Table.VAR1 > 0),Table,2)

[Star Strider]

Please do not use the ‘idx’ assignment. It was only there to demonstrate how the code works in my initial answer. It has no use otherwise.

My ‘GetRange’ function is only designed to work with specific values for ‘Val’ because that was the original request, and as the result, that part of it is not vectorised. I am also not certain what the data are that you are working with. That makes it difficult for me to understand how best to approach this. I am not even certain how to simulate it.

One possibility:

GetRange = @(range,Val,T,Col) strfind(T{:,Col}', [zeros(1,range) Val zeros(1,range)]);

range = 3;                                                      % Number Of Zeros To Either Side Of a 'Val' Value
T = Table;                                                      % Table To Index Into
Col = 2;                                                        % Corresponds To 'VAR1' Here
Val = unique(Table.VAR2>0);                                     % Vector Of Values To Test For

for k1 = 1:numel(Val)
    start = GetRange(range,Val(k1),Table,Col);                  % Return Starting Indices Of 'T' Sections
    for k2 = 1:numel(start)
        Out{k2,:} = Table(start(k2) : start(k2)+range*2,:);
    end
    Out{:}                                                      % Optional
    Outcat{k1} = cat(1,Out{:})                                  % Vertically Concatenate Table Segments
end

This version of my code now searches for unique values in ‘VAR1’ that are greater than zero and returns them in ‘Val’ that instead of being a scalar is now a vector. It then searches for each instance of each value and returns a separate ‘Outcat’ table for each one. See the documentation for unique to understand its other arguments and options.

The ‘Outcat’ tables correspond to the elements of the ‘Var’ vector.

I have not tested this version of my code. It should work.

.

[Laty El]

Thank you for the code. It works, but it doesnt give the expected results. In fact, the result should be something like:

VAR1
0           
0
0
0.1
0
0
0
0
0
0
0.6
0
0
0
0
0
0
1
0
0
0

Because i have a col VAR1 including many values higher than 0 with the applied rang.

However, when applying your code, i got a final table including only 1 and then zeros in VAR1

VAR1
0
0
0
1
0
0
0
0
0
0
0
0
0
0

[Star Strider]

It is returning a logical vector. I should have anticipated that even though I could not test my revised code with that ‘VAR1’ vector.

Change the ‘Var’ assignment to:

Val = unique(Table.VAR1(Table.VAR1>0));                         % Vector Of Values To Test For

I tested a version of that, and it works correctly.

.

[Laty El]

@Star Strider Thank you very much, your code works perfectly!

[Star Strider]

As always, my pleasure!

[Laty El]

@Star Strider, I am sorry if I bother you. I have two other colums VAR2 and VAR3 and i want to calculate their averages before and after the found values of VAR1 " Val = unique(Table.VAR1(Table.VAR1>0))", without including the "val" rows.

I used:

columnMeans = [
    mean(T.VAR1([1:3], :), 1);
    mean(T.VAR2([1:3], :), 1);
    mean(T.VAR1([5:7], :), 1);
    mean(T.VAR2([5:7], :), 1);
    mean(T.VAR1([9:12], :), 1)
    mean(T.VAR2([9:12], :), 1)
    ...];

But this doesnt give efficient results. I need to do it in an automatic way, so it would be easy if i want to change the range

[Star Strider]

I tested and added that to my code.

With all the changes, the full code (including the test table) is now —

GetRange = @(range,Val,T,Col) strfind(T{:,Col}', [zeros(1,range) Val zeros(1,range)]);

N = 96;
Col2 = zeros(N,1);
Col2(randi(24,N,1)==5) = 5;
Table = table(datetime('now')+hours(0:N-1).', Col2, rand(N,1), 'VariableNames',{'Datetime','VAR1','VAR2'})

% idx = find(Table.VAR1 == 5)                                     % For Testing Only — Do Not Use This With Real DAta!

range = 3;                                                      % Number Of Zeros To Either Side Of a 'Val' Value
T = Table;                                                      % Table To Index Into
Col = 2;                                                        % Corresponds To 'VAR1' Here
Val = unique(Table.VAR1(Table.VAR1>0));                         % Vector Of Values To Test For

for k1 = 1:numel(Val)
    start = GetRange(range,Val(k1),Table,Col);                  % Return Starting Indices Of 'T' Sections
    for k2 = 1:numel(start)
        Out{k2,:} = Table(start(k2) : start(k2)+range*2,:);
        ColMeans{k2,:} = mean(Table{[start(k2):start(k2)+range-1  start(k2)+(range+1:range*2)],2:end});
    end
    Out{:}                                                      % Optional
    Outcat{k1} = cat(1,Out{:})                                  % Vertically Concatenate Table Segments
    ColMeans{:}                                                 % Optional
    MeansCat{k1} = cat(1,ColMeans{:})                           % Vertically Concatenate Column Means
end

The ‘ColMeans’ and ‘MeansCat’ for ‘VAR1’ will be uniformly zero because all the elements of it are zero by design. My code calculates it anyway, since that was requested.

.

[Laty El]

@Star Strider Thank you very much. I will test your code on my case. :)

[Star Strider]

As always, my pleasure!