Selecting particular data range from table/ columns

Hi there,

I am very new to Matlab and haven't been able to find the following solution.

I have two columns within a table of 1537090 x 2. Within the second column I want to extract numbers that fall within a range of 15 to 50.

Any numbers that are found in that range in column 2 I would like the corresponding row from Column 1 to also be pulled out.

The result will be another two column table but have all values within 15 to 50 from Column 2 and the corresponding rows from Column 1.

Thanks very much in advance and really appreciate any help.

Answer 1

[Star Strider]

Try this:

T1 = table(rand(100,1), randi(75,100,1));                       % Original Table
L = table2array(varfun(@(x)((x>=15) & (x<=50)), T1(:,2)));      % Logical Vector
T2 = T1(L,:);                                                   % Edited Table

.

[Tyson Campbell]

Thanks for your response :)

I just have an additional question, I am unsure exactly where inputs are required in this code.

Say for example my Table is called 'Temp_Rain' which part of the code is this to go?

Or if you can please let me know where inputs are required and what type they are. I am assuming maybe 'table, x and L require any input? Thanks again.

[Star Strider]

My pleasure!

Put it anytime after you create your table. It creates a second table (‘T2’ here), so the original table remains unchanged.

The inputs are the conditional statements in the ‘L’ calculation:

((x>=15) & (x<=50))

that determines what the limits are, and the table name, here ‘Temp_Rain’. Everything else runs on its own.

The line for your table is then:

L = table2array(varfun(@(x)((x>=15) & (x<=50)), Temp_Rain(:,2)));

and the edited table can be any name you want it to be, perhaps:

Temp_Rain_2 = Temp_Rain(L,:);

The ‘L’ calculation can even be created as an anonymous function, and with that the new code is then:

Temp_Rain = table(rand(100,1), randi(75,100,1));                                                                        % Original Table
RowEdit = @(LowerLim, HigherLim, TableName) table2array(varfun(@(x)((x>=LowerLim) & (x<=HigherLim)), TableName(:,2)));  % Function Creates Logical Vector
Temp_Rain_2 = Temp_Rain(RowEdit(15,50,Temp_Rain),:);                                                                    % Edited Table

Everything can even be combined into one anonymous function:

Temp_Rain = table(rand(100,1), randi(75,100,1));                                                                                    % Original Table
RowEdit = @(LowerLim, HigherLim, TableName) TableName(table2array(varfun(@(x)((x>=LowerLim) & (x<=HigherLim)), TableName(:,2))),:); % Function Creates New Table
Temp_Rain_2 = RowEdit(15,50,Temp_Rain);                                                                                             % Edited Table

With this, the ‘RowEdit’ function creates the edited table in one call..

.

[Tyson Campbell]

Thanks so much for your help! That worked perfectly! Appreciate the explanation as well.

[Star Strider]

As always, my pleasure!

[Tyson Campbell]

Hi Star Strider,

Having a look I have realised by data is being changed in column 1 to smaller values when they shouldn't change. Where the values were approximatley 28 to 30 they become anywhere between 0.1 to 0.9.

The values in column 2 that are between 15 to 50 are extracted perfectly. But it looks as though the values in column 1 that are brought through with column 2 are changed when they should just be brought through to the new table as their original values.

This is the code that I used in the end:

Temp_Rain = table(rand(100,1), randi(75,100,1));
L = table2array(varfun(@(x)((x>=15) & (x<=50)), Temp_Rain(:,2)));
Temp_Rain_2 = Temp_Rain(L,:);

Thank you

[Star Strider]

As always,my pleasure.

When I run my example code (the same as the code you just posted), the first column is not changed (nor is the second), and the columns are appropriately aligned in the rows. The only changes are the expected deletions of the rows that do not meet the criteria in the second column.

What you are seeing could be the result of the format you are using, since table objects inherit that format setting.

See if setting:

format long

in the beginning of your script changes the result. Beyond that, I have no idea what the problem could be. (I am using R2020a on Windows 10.)

If the problem is only with your table and not the table in my example code, I would need your table to see what the problem is.

[Tyson Campbell]

I am really appreciative of you taking your time to help. Thanks Star strider.

I am also using R2020a on Windows 10. I have run the code with "format long" and didn't have a change in result.

If you could please have a look at my table to see if you can find where the problem might be, below is a google drive link as the file size exceeded 5mb. Thank you.

https://drive.google.com/file/d/10mP9wqnQ9Fq4Q3E83oYjrXPSLufi48zQ/view?usp=sharing

[Star Strider]

I am getting the expected result:

D = load('Temp_Rain table.mat');
Temp_Rain = D.Temp_Rain;
L = table2array(varfun(@(x)((x>=15) & (x<=50)), Temp_Rain(:,2)));
Temp_Rain_2 = Temp_Rain(L,:);

Then checking:

Before = Temp_Rain(1:10,:)
After = Temp_Rain_2(1:10,:)

produces:

Before =
  10×2 table
  
    VarName2    VarName3
    ________    ________

     31.447       69.79 
     33.191      83.479 
     34.808       88.95 
     30.423      65.394 
     31.536      96.773 
     32.559      93.525 
     36.026      109.91 
     36.716      35.646 
     28.261      52.139 
     28.536       18.59 

After =
  10×2 table
  
    VarName2    VarName3
    ________    ________

     36.716      35.646 
     28.536       18.59 
     34.459      48.416 
     35.179      23.567 
     30.255      18.303 
     34.349      19.674 
      28.64      26.635 
     28.655      47.264 
     28.712      21.465 
     35.886      31.472 

Other than what I already mentioned, I have no idea what could cause the problem you are seeing.

.

[Tyson Campbell]

I reran the code using what you just sent through then and it worked! Temp_Rain_2 resulted in a table of 419751x2. Thanks so much for all your efforts.

[Star Strider]

As always, my pleasure!

(That was the same result I got.)

Answer 2

[Sindar]

idx = (mytable{:,2} >= 15) & (mytable{:,2} <= 50);
newtable = mytable(idx,1:2);

Components of the answer:

• extract the data in the 2nd column (note {}; () would return a table)

mytable{:,2}

• identify which elements of this array (i.e. which rows of the table) are >= 15. This returns a logical array (true where >=15, false elsewhere)

(mytable{:,2} >= 15)

• elementwise AND with the other condition (<=50) so true only in the range

() & ()

• extract the appropriate rows from the table (and columns, though ":" would work as well if you want all the columns)

mytable(idx,1:2)