Logical indexing: Find row in table by text in column

348 views (last 30 days)
a = {'hi', 'bye'; 'don', 'tcry' };
t = array2table(a);
u = t(t.a1 == 'don', :); % error: Undefined operator '==' for cell
How do I do a logical search for the row where a1 is 'bye'?
If it was numbers, it would be easy:
b = [1,2 ; 3,4];
q = array2table(b);
r = q(b1 == 3, :); # works perfectly
OK I found one way: Use string().
g = t(string(t.a1)=="don", :); % works! ;-)
Are there other ways, better ways, nicer ways?
  1 Comment
Stephen23
Stephen23 on 6 Feb 2018
Edited: Stephen23 on 6 Feb 2018
"Are there other ways, better ways, nicer ways?"
Yes: use strcmp, strcmpi, etc. Do not use == for testing for string/character array equivalence.

Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 6 Feb 2018
To compare strings use the strcmp (link) or strcmpi function:
u = t(strcmp(t.a1, 'don'), :);
u =
1×2 table
a1 a2
_____ ______
'don' 'tcry'
  2 Comments
KAE
KAE on 20 Jun 2019
Just another way to use Star Strider's solution. If you want the index to the row containing your desired value,
iRow = find(strcmp(t.a1, 'don')==1);
Guillaume
Guillaume on 21 Jun 2019
Note that beginners tend to use find more than they should, you typically see:
indices = find(somearray == somevalue);
result = somerarray(indices);
where find wasn't needed at all and was just a waste of time:
isfound = somearray == somevalue;
result = somearray(isfound);
It's actually rare that you do need the indices.

Sign in to comment.

More Answers (4)

Guillaume
Guillaume on 6 Feb 2018
Edited: Guillaume on 6 Feb 2018
Comparison of char arrays is always done with strcmp, never with ==. Thus:
u = t(strcmp(t.a1, 'don'), :)
The string class introduced in r2016b overloads == so that it can be used for comparison , so converting the char arrays to strings is indeed another solution.

Kristupas Karcemarskas
Kristupas Karcemarskas on 17 Apr 2022
I found that it is really easy to use categorise() function
for example:
u=categorise(u);

Peter Perkins
Peter Perkins on 7 Feb 2018
Two other things worth considering:
1) if {'hi', 'bye'; 'don', 'tcry'} are always unique, consider making them row names. Then t('don', :) is what you would use.
2) If {'hi', 'bye'; 'don', 'tcry'} could be a large list of repeated values, consider making them a categorical vector. Then u = t(t.a1 == 'don', :) is what you would use.
But even if those are not an option, if you are using R2016b or later, consider using a string array instead of a cellstr, as Guillaume says.
  2 Comments
Moshe Flam
Moshe Flam on 11 Feb 2018
1) Could you edit your answer and show how to make them row names?
2) Could you edit your answer and show how to make 'hi' and 'don' a categorical vector?
And in case 2 - two questions:
2a. Will I not get the same as I had with my original question? Reminder
u = t(t.a1 == 'don', :); % error: Undefined operator '==' for cell
2b. Will it be correct to use t.a1 == 'don' or should I use strcmp here too?
3. Just a last remark: Guillaume confirmed what I said and explained when and why it was made to work, but did not suggest it.
Peter Perkins
Peter Perkins on 12 Feb 2018
To make them row names, it's just
r.Properties.RowNames = t.a1;
t.a1 = []; % delete the original variable
but if the data are coming from a file you may well be able to read them in as row names. See the doc for readtable.
To make a categorical, it's just
t.a1 = categorical(t.a1)
but you will probably want to look over the documentation for categorical arrays. Once t.a1 is categorical, you can use ==. It's not the main reason for using categoricals, but it's a convenience (one that string also provides).

Sign in to comment.


Gabor
Gabor on 3 Mar 2021
b = [1,2 ; 3,4];
q = array2table(b);
r = q(b1 == 3, :);
Unrecognized function or variable 'b1'.
  1 Comment
Gabor
Gabor on 3 Mar 2021
b = [1,2 ; 3,4];
q = array2table(b);
r = q(q.b1 == 3, :);
This is how it works

Sign in to comment.

Categories

Find more on Cell Arrays 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!