How to use an an if statement which has two criteria to be met to create a new table

(apologies if the explanation is hard to follow - I'm struggling to describe it!)

I have a table and I want to create a sub-table of it, if a certain criteria is met.

There are two criteria, either of which is sufficient to join the new table: 1) if the value in one column 1 meet a certain critera, or if the value in column 1 doesn't meet the criteria but the value in column 2 does meet a new criteria.

To expand on the data and the challenge I've attached a photo of the data in a simplified format (and the new table that I am trying to create - with eliminated columns in red).

I have water stress data (where 1 = not stressed, 2 = moderately stressed, 3 = stressed) and flooding risk data (where the number represents increased flooding risk) for ten parcels of land. I want to create a table where all the data are either not stressed (water stress value < 2) or water stress values are above 1 but flooding risk are non-zero.

I've tried a few options using the conditionals but not really made any progress - any help would be greatly appreciated - thanks!

Answer 1

[dpb]

>> W=[1 2 1 2 1 3 2 0 0 2].';
>> F=[0 0 2 2 0 0 3 3 0 0].';
>> ix=(W<2) | (W>1 & F>0);
>> [W(ix) F(ix)]
ans =
     1     0
     1     2
     2     2
     1     0
     2     3
     0     3
     0     0
>> 

[Caspar Donnison]

Thanks that is the result that I am after - much appreciated!

How can I convert this back into a table with the original column names? I know a long way of doing this (using array2table) but the actual data sheet I have has around 30 columns and so I don't want to do this manually for each one.

[dpb]

Same table or a new one? If don't want to overwrite the old table, then something like

>> T=table(W,F);
>> NT=T(ix,:)
NT =
  7×2 table
    W    F
    _    _
    1    0
    1    2
    2    2
    1    0
    2    3
    0    3
    0    0
>> 

To overwrite; just reassign the RHS to the existing variable.

[Caspar Donnison]

Yes a new one, thanks I can see how that would work.

My challenge is that the data I gave above is a simplified version of what I have - I actually have around 30 columns and so the new table needs all the other colums too - I don't really want to write each column name out in creating a new table as I'm liable to make an error, unless there is no other way around.

Is there another option here? Perhaps rather than creating a new table from the original I can write a script to 'screen' out those rows that I don't want and still keep the original table?

[dpb]

Didn't you try it?????

It doesn't use var names excepting for the two specific columns that need for the logical addressing vector; the columns are referred to by the ":" in the expression creating the new table.

[Caspar Donnison]

Thanks - yes I did try it and the other columns were not coming through into the new table.

I am re-trying again now and I see that I need to write the name of the original table into the code that you gave in your 2nd answer (T = table('original table name', W, F) and that gives me the first table's columns too. However, the new table seems to have a two-tier column title which isn't allowing me to do further calculations to the table (e.g create new columns). I need to somehow have the same format as the original table, but only with those rows that the conditions allow.

I've attached a screen-shot of this new table which doesn't seem quite right (with a dummy column called 'Bananas' - I'm not really investigating banana presence in water stressed and flooded land):

[dpb]

" I see that I need to write the name of the original table into the code that you gave in your 2nd answer (T = table('original table name', W, F)"

NO! I just did that so that I would have an example table to go from in the command window...and the only variables I happened to have laying around at the time were W and F so I used them.

Simply writing

NT=T(ix,:);

will create a new table NT with all columns extant in T and the rows as given by the logical addressing vector ix that satisfy the conditions you wanted for what to incorporate into the new table.

I'm not sure how you managed it, but you'll see that your table above is 7x3(!) only--the first four "columns" are actually only one--a cell variable Test, maybe? or you somehow nested a table inside a table (wasn't aware that could be done if that is what it actually is showing). The other two colums are W and F.

Again, all you need (and want) is the one assignment statement to select the subset of the wanted rows and all columns of the original table on the RHS and assign those to a new variable on the LH. The second subscript colon ":" automagically gets all the columns.

Read the documentation on using the table object -- it goes into exhaustive detail on the addressing modes with examples.

[Caspar Donnison]

Thanks - I've got there in the end, re-read all of this and started again and see how I over-complicated things. It also seems that I did nest a table in another table accidentally in the process. Thanks for helping me throughout!

[dpb]

No problem, sometimes it's difficult to step back and run back to the basics when get started down a wrong road...

If this solved the problem; be good to go ahead and "Accept" the answer to indicate so to others not take time thinking might still be open issues...