How to batch convert table columns to categorical

I have a large table (large number of columns and very large number of rows), many of these columns contain simple cell arrays with a very limited number of different options (such as 'ON" and 'OFF' or similar).

I would like to convert all these columns to categorical in an automated way, i.e. extracting column names from

Table.Properties.VariableNames

and not explicitly writing the (large) number of column names in dot notation.

I have seen that

>> ddd2 = categorical(MyTable{:,MyTable.Properties.VariableNames(1)});

works fine (i.e. ddd2 is a correct categorical array), but, at this point, I cannot put this categorical array back in my table. I tried:

>> MyTable{:,MyTable.Properties.VariableNames{1}}= ddd2;
The following error occurred converting from categorical to cell:
Conversion to cell from categorical is not possible.

but it returns the error you see above... On the other hand, if I use dot notation it works fine, i.e.

MyTable.Column1 = ddd2;

yields the desired results. I found a workaround, that works perfectly, but I would like to know if there is a more direct and elegant way to solve this problem.... this is the workaround:

sz0=size(MyTable);
kk=0;
for( i=1:sz0(2) )
    if( iscell(MyTable{:,i}) )
        ddd2 = categorical(MyTable{:,MyTable.Properties.VariableNames(i)});
        eval(sprintf('MyTable.%s = ddd2;',MyTable.Properties.VariableNames{i}));
        kk = kk+1;
    end
end
fprintf( ' %d columns transformed to categorical\n', kk );

it works...

[Giuseppe Degan Di Dieco]

Dear Giancarlo,

thanks for your tip on including the Table.Properties.VariableNames in a for loop. You know, it was the easy to create a cell array for the 'vars' input of the change variable types built-in function.

Best.

Answer 1

[Sean de Wolski]

In 18b there is a convenience function to do exactly this:

https://www.mathworks.com/help/releases/R2018b/matlab/ref/convertvars.html

Note, that it may be possible to just create your table with categoricals. How are you creating the table?

[Giancarlo Storti Gajani]

Wow, thank you for the immediate answer (I will upgrade, then asap).

The table is created by reading a Mysql db using a select() command, I am not sure, but it does not seem I have many options to preprocess the arriving data ?

[Sean de Wolski]

In 18a/b there is!

Use sqlread() which accepts import options that allow you to specify variable types.

https://www.mathworks.com/help/releases/R2018b/database/ug/database.odbc.connection.sqlread.html#mw_be9c1804-6c62-4916-a421-961620731a56_sep_mw_c5cd033e-a425-4e83-817b-341618f23237

[Giancarlo Storti Gajani]

Thank you again, did not see that function !

[Alexis]

I'm running MATLAB r2019a and I get an error.

Undefined function or variable 'convertvars'.

[Steven Lord]

Aldema, is the first input of your convertvars call a table or timetable array? That function is only defined when the first input is a table or timetable array.

[Gwendolyn Williams]

I have the same error, and I'm inputting a table into convertvars.

[Giuseppe Degan Di Dieco]

Dear Sean,

thank you. You reminded me of the built-in function, best!