You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
How to fine a range of values before and after a specific value with a condition
2 views (last 30 days)
Show older comments
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
4 Comments
Johannes Fischer
on 25 Feb 2022
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
on 25 Feb 2022
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
on 25 Feb 2022
@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?
Accepted Answer
Star Strider
on 25 Feb 2022
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).
.
18 Comments
Star Strider
on 25 Feb 2022
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
on 25 Feb 2022
- 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
on 25 Feb 2022
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
on 27 Feb 2022
@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
on 27 Feb 2022
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
on 27 Feb 2022
@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
on 27 Feb 2022
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
on 27 Feb 2022
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
on 27 Feb 2022
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
on 28 Feb 2022
@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
on 28 Feb 2022
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.
.
More Answers (0)
See Also
Categories
Find more on Trigonometry in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)