How can I select multiple columns in a table using variable names and wildcards?

Hi all,

I am trying to index and extract a series of variables from a table i've created based on the VariableNames. I would rather index using the VariableNames as opposed to numerical or logical index due to the fact that there might be different numbers of trials for the variables.

Using the below code I can call one variable at a time. The '_1' represents the trial number which could go up to '_20'.

C1vC2Trials = C1MPVNormVariables(:,'C1MPVNorm_1');

However when trying to use the * wildcard to call all trials for that variable I get an error.

Error: Unrecognized variable name 'C1MPVNorm_*'.

Is this possible to do with tables or is there a easy work around to find the number of VariableNames in the table which correspond to the ones I wish to extract?

Thanks in advance!

Answer 1

[Cedric]

I am not using tables, so you should probably wait for a better answer, but here is one way that I could see. Say we have the table:

 T =
  5×4 table
                Age    Height    Weight    BloodPressure
                ___    ______    ______    _____________

    Smith       38     71        176       124     93   
    Johnson     43     69        163       109     77   
    Williams    38     64        131       125     83   
    Jones       40     67        133       117     75   
    Brown       49     64        119       122     80

and we want all variables that contain the string 'ei' (pattern):

 >> hasMatch = ~cellfun('isempty', regexp(T.Properties.VariableNames, 'ei', 'once')) ;
 >> T(:, T.Properties.VariableNames(hasMatch))
 ans =
  5×2 table

                Height    Weight
                ______    ______

    Smith       71        176   
    Johnson     69        163   
    Williams    64        131   
    Jones       67        133   
    Brown       64        119

Here we are using regular expressions, so you must use regexp types of "wildcards".

[Jamie Hetherington]

Cedric = Epic! Worked perfectly so i'm happy to accept and use this answer. Interested to still see if there are other ways as well! Thats what I love about MATLAB many solutions to the problem!

[Cedric]

Awesome! I'll have a look at other answers as well if any, because I will have to make the step of using tables once.

[BenRaanan]

I believe the preferred syntax is:

>> T(:, contains(T.Properties.VariableNames, 'ei'))

[Peter Perkins]

Yes, in recent versions of MATLAB you should be able to avoid using regexp in most cases, by using things like contains and startsWith instead. In older versions, you can often use strfind, depending on how complicated the pattern is.

Even with regexp, I think you can skip using cellfun and just make a vectorized call to regexp.

Answer 2

[Roman Romanovski]

T(:,ismember(T.Properties.VariableNames, {""list of variables""}))

This will get you there for the variable names part. Above already answered wild card.

> T(:, contains(T.Properties.VariableNames, 'C1MPVNorm_'))

[Roman Romanovski]

Although. I wish there was syntax like

T.{'''list of variables'''}

instead of

T(:,ismember(T.Properties.VariableNames, {""list of variables""}))

[Peter Perkins]

I may be misunderatanding what {""list of variables""} is intended to prepresent, but if it works as the 2nd input to ismember, it will work as a table variables subscript. So T(:,ListOfVars) or T{:,ListOfVars}, depending on whether you want a subtable or the contents.

T.{'''list of variables'''} doesn't make sense, because dot subscripting extracts exactly one variable. Dot, one variable. Braces, any number, including none or one.

[Roman Romanovski]

Both would work.

My method protects from an error if the variable in the list doesn't exist in the table.

I'm not sold on your dot = one explanation. What if T is an array of Table? T.x returns array of x. Besides that misses my point. What I am trying to say is it would be nice to be able to select multiple columns by name without requiring a row index.

[Peter Perkins]

Tables have variables, not columns. "Variables", because the things in a table can themselves have multiple columns. So yes, dot subscripting extracts exactly one variable.

The syntax to extract multiple variables is T{:,ListOfVars}.

[Zzz]

The correct syntax is T(:, {'col_name_1', 'col_name_2'}) to get the data like a table.

Answer 3

[Chenye Shen]

You can refer to this。

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-table.html

[Peter Perkins]

To expand on the link that Chenye has provided: new features are added all the time. In particular, tables have supported patters for variable names since ... I actualy don't recall exactly when. The doc in that link show an example, but for the record:

t = table([1;2;3],[4;5;6],[7;8;9],VariableNames=["x1" "x2" "y"])

t = 3×3 table
    x1    x2    y
    __    __    _

    1     4     7
    2     5     8
    3     6     9

t(:,"x"+digitsPattern)

ans = 3×2 table
    x1    x2
    __    __

    1     4 
    2     5 
    3     6 

Also, of course, use "strings", not 'cell arrays of char vectors'!

[Steven Lord]

The ability to use pattern objects to index into table rows, variables, and properties was introduced in release R2022a.