Extract or keep rows based on an array.

I have been trying multible things and researching on the MathWorks site for hours for what should be a very simple thing I believe. So, I have a table of data that contains three columns. In column one there is are values that are repeated but each row is unique. For example:

A = [1 'cat' 24; 1 'dog' 2; 2 'horse' 10; 2 'dog' 3; 3 'sheep' 45; 3 'cow' 203; 4 'cow' 233; 5 'pig' 23; 5 'walrus' 2; 6 'horse' 4]

And I have a vector of values B = [2 4 5]

I want to pull out or keep only the rows where the value in column one of A is the same as any one of the values in the vector B.

In short, want to parse down A from the above to a leaner version with only the data I need. A = [2 'horse' 10; 2 'dog' 3; 4 'cow' 233; 5 'pig' 23; 5 'walrus' 2]

My real example is 3,142 rows in size what should parse down to around 861.

Answer 1

[Geoff Hayes]

Kellie - if we can assume that your A is constructed as a cell array like

A = {1 'cat' 24; 1 'dog' 2; 2 'horse' 0; 2 'dog' 3; 3 'sheep' 45; 3 'cow' 203; 4 'cow' 233; 5 'pig' 23; 5 'walrus' 2; 6 'horse' 4}

then we can find those rows whose first column element is in the set defined by B by evaluating

 A(cell2mat(arrayfun(@(x)ismember(x,B), cell2mat(A(:,1)), 'UniformOutput', false)),:)

which returns

    [2]    'horse'     [  0]
    [2]    'dog'       [  3]
    [4]    'cow'       [233]
    [5]    'pig'       [ 23]
    [5]    'walrus'    [  2]

In the above line of code, we extract the first column of A and convert it from a cell array into a matrix

 cell2mat(A(:,1))

We then use arrayfun to evaluate each element of this column to see if it is a member of B

 arrayfun(@(x)ismember(x,B), cell2mat(A(:,1)), 'UniformOutput', false)

Each call to ismember returns a logical zero (not a member) or one (is a member). We then convert this to a matrix and extract those rows of A that are members of B (so the logical zero means the row will be ignored).

Try the above and see what happens!

[Kellie Anton]

I do not understand. A is a table/matrix. In my real problem, it is a 3142 x 3 table. Does this still apply. The cell2mat is not making sense to me.

[Kellie Anton]

result:

Error using cell2mat (line 42) You cannot subscript a table using linear indexing (one subscript) or multidimensional indexing (three or more subscripts). Use a row subscript and a variable subscript.

[Geoff Hayes]

Kellie - it wasn't clear what your A was (to me) so I assumed that it could be written as a cell array. You can try converting your table into a cell array with table2cell and then trying the above code.

Answer 2

[Andrei Bobrov]

A = {1 'cat' 24; 1 'dog' 2; 2 'horse' 10; 2 'dog' 3; 3 'sheep' 45; 3 'cow' 203; 4 'cow' 233; 5 'pig' 23; 5 'walrus' 2; 6 'horse' 4};
B = [2 4 5];

out = A(ismember([A{:,1}],B),:);

[Kellie Anton]

Error using cell/ismember (line 34) Input A of class cell and input B of class double must be cell arrays of character vectors, unless one is a character vector.

[Kellie Anton]

This actually gets me right back to where I had been two or three hours ago. :(

>> indx = poverty(ismember([poverty(:,1)],rfstates),:); Error using tabular/ismember (line 30) A and B must both be tables.

So, I transformed rfstates into a table rfs, and the result is:

>> indx = poverty(ismember([poverty(:,1)],rfs),:); Error using tabular/ismember (line 35) A and B must contain the same variables.

[Andrei Bobrov]

@Kellie Anton: Please attach your data's example (mat - file).

>> A = {1 'cat' 24; 1 'dog' 2; 2 'horse' 10; 2 'dog' 3; 3 'sheep' 45; 3 'cow' 203; 4 'cow' 233; 5 'pig' 23; 5 'walrus' 2; 6 'horse' 4};
B = [2 4 5];
out = A(ismember([A{:,1}],B),:)
out =
  5×3 cell array
    [2]    'horse'     [ 10]
    [2]    'dog'       [  3]
    [4]    'cow'       [233]
    [5]    'pig'       [ 23]
    [5]    'walrus'    [  2]
>>

[Kellie Anton]

Ok...

[Andrei Bobrov]

out = poverty(ismember(str2double(poverty.State),rfstates),:)

[Kellie Anton]

That nailed it. Thank you!

[Kellie Anton]

I knew it had to be easy! I was missing the formatting issue I think.

[Andrei Bobrov]

Here it is customary to "accepted" the answer that solve your problem...