Converting Categorical Array/Table to Numerical

Hello!

I have a 23000x4 set of data that is a table (called temp). It is all numbers except for the times data was missing it was filled in with 'NA', therefore the table is categorial. I am looking to change it to a numerical and have all the 'NA' changed to 'NaN' so I can run max and min and those kinds of things on it without running into issues. Thank you for the help!

[Steven Lord]

Can you show us a representative example of your data? Say show us 3-4 rows of your data, at least one with NA and at least one with non-missing times data.

I suspect standardizeMissing can help you, but depending on how the data is stored converting your times data to datetime or duration may be of more use.

[Claire Hollow]

Here is an example. Nothing is missing in it, it is all either a number or NA.

5 5 6

4 NA 42

NA 14 5

6 15 NA

[Steven Lord]

Can you show us the output of whos on the variable that contains this data, and perhaps show those four lines from your variable as MATLAB displays them? This would help us ensure our suggestions take the class of the variable in which this data is stored and the classes of the data stored in that variable itself into account.

I'm thinking something like:

 % Create sample data
 load patients
 patients = table(LastName,Gender,Age,Height,Weight,Smoker,Systolic,Diastolic);
 
 % Here's the information I'm interested in
 whos patients
 patients(1:4, :)

[Claire Hollow]

Heres the whos

Name Size Bytes Class Attributes

data01 2657115x14 170145275 table

It is actually x14 but im only looking for 1-4.

[Adam Danz]

FYI; "NA" or "NaN" is a missing value.

[Adam Danz]

Of course the categorical values that are numeric can be converted to numeric values but that approach is just a bandaid. The better approach would be avoid using categorical classes on numeric values in the first place.

How did these values end up to be categorical?

[Claire Hollow]

Im working with climate data and this is how it was recorded. Really just working on how to get max to work so I could find the maximum value in the column.

[Adam Danz]

That's a completely different question.

If you look at the documentation for max() you'll see that one of the inputs specifies whether to compute max() across columns, rows, or any other dimension.

[Claire Hollow]

Im aware of that I just cannot use max at all on a categorical. Which is why I'm trying to change it to numerical.

[Adam Danz]

As I mentioned above, the best solution is to not convert the values to categorical in the first place. How did they end up to be categorical?

[Claire Hollow]

The data I need to use came from a climate station in a csv file, when I imported it it was a categorial because of its contents.

[Adam Danz]

What function did you use to import it? You can control the class of the data you're importing. A csv file doesn't control that for numeric values.

Answer 1

[Adam Danz]

By far the best solution is to avoid representing numeric values as categorical values in the first place. If you can un-do that, that's the best solution.

If that cannot be done, here's how to convert categorical values that contain numeric values in a table.

Table T can contain mixed classes (some classes may cause errors).

This demo detects which columns of T contains values that can be converted to numers. It then creates an output table T_converted that contains the num-categorical-number columns of T and the categorical-number columns converted to numbers.

% Create demo table with a mix of stings, categorical numerals, and numbers
T = table(["A";"B";"C";"D";"E"], ...
    categorical(randi(10,5,1)), ...
    randi(10,5,1), ...
    categorical(randi(10,5,1)), ...
    'VariableNames', {'A','B','C','D'})

T = 5×4 table
     A     B     C    D
    ___    __    _    _

    "A"    2     3    4
    "B"    3     4    8
    "C"    9     6    8
    "D"    9     4    5
    "E"    10    5    6

varfun(@class, T)

ans = 1×4 table
    class_A      class_B      class_C      class_D  
    _______    ___________    _______    ___________

    string     categorical    double     categorical

% Determine which columns are categoricals
% NOTE: This assumes you want to convert all categorical table variables
% to numeric.  Otherwise, additional column indexing will be needed.  
iscat = varfun(@iscategorical, T,'OutputFormat','Uniform');

% Convert the categorical table variables to numeric
Tnum = array2table(str2double(string(T{:,iscat})), ...
    'VariableNames', T.Properties.VariableNames(iscat));

% Create an updated table with the converted data and maintain
% original column order
T_converted = [T(:, ~iscat), Tnum];
[~,colorder] = ismember(T_converted.Properties.VariableNames, T.Properties.VariableNames);
T_converted(:,colorder)

ans = 5×4 table
     A     B     C    D
    ___    __    _    _

    "A"     2    3    4
    "B"     3    4    8
    "C"     9    6    8
    "D"     9    4    5
    "E"    10    5    6

varfun(@class, T_converted)

ans = 1×4 table
    class_A    class_C    class_B    class_D
    _______    _______    _______    _______

    string     double     double     double 

This answer was corrected on 9/23/22; thanks to Ahmed Rady for pointing out the problem

[Ahmed Rady]

Hi Adam

Thanks for the code

But it seems that the numeric values in the original table were transformed.

I thought the aim is to only change the catergorical variables.

[Adam Danz]

Thanks @Ahmed Rady, I've updated the answer to correct the mistake.

My previous answer involved applying double() to the categorical-numbers which converts the categoricals into a grouping number rather than the numbers represented within the categories.