Convert tablet to matrix

My code:

filename='Excelspreadsheet.xlsx';

Data=readtable(filename)

7×3 table

Classes Hours Grades

{'CHM' } 3 {'C'}

{'Calc' } 4 {'A'}

{'CHM lab' } 2 {'F'}

{'College comp'} 4 {'D'}

{'ECAL' } 1 {'A'}

{'MHCS' } 1 {'B'}

{'EGE' } 1 {'A'}

A=4;B=3;C=2;D=1;F=0;

Problem:

What I'm trying to do is to either convert the characters under grades to something which allows me to convert this table into a matrix. Or my other solution would be to convert the grades to the corresponding numbers which I have written in the code. However, I can't get it to work.

[Nils Hammarsten Brywe]

Here's the spreadsheet

[Cris LaPierre]

Why can't you keep it a table?

[Nils Hammarsten Brywe]

Because I need to multiply the array with hours with the array with grades. The goal is to calculate a gpa

Answer 1

[Cris LaPierre]

There may be an easier way, but here's what I came up with.

1. Read in your grades as categoricals
2. Rename the categories for grades from letters to numbers
3. Convert your categorical grades to doubles
4. Perform your calculation for gpa

% Step 1
opts = detectImportOptions('https://www.mathworks.com/matlabcentral/answers/uploaded_files/790884/Excelspreadsheet.xlsx');
opts = setvartype(opts,[1,3],'categorical');
T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/790884/Excelspreadsheet.xlsx',opts)

T = 7×3 table
      Classes       Hours    Grades
    ____________    _____    ______

    CHM               3        C   
    Calc              4        A   
    CHM lab           2        F   
    College comp      4        D   
    ECAL              1        A   
    MHCS              1        B   
    EGE               1        A   

% Step 2
T.Grades = renamecats(T.Grades,categories(T.Grades),["4","3","2","1","0"])

T = 7×3 table
      Classes       Hours    Grades
    ____________    _____    ______

    CHM               3        2   
    Calc              4        4   
    CHM lab           2        0   
    College comp      4        1   
    ECAL              1        4   
    MHCS              1        3   
    EGE               1        4   

% Step 3
T.Grades = str2double(string(T.Grades))

T = 7×3 table
      Classes       Hours    Grades
    ____________    _____    ______

    CHM               3        2   
    Calc              4        4   
    CHM lab           2        0   
    College comp      4        1   
    ECAL              1        4   
    MHCS              1        3   
    EGE               1        4   

% Step 4 (example calculation)
T.Sum = T.Hours + T.Grades

T = 7×4 table
      Classes       Hours    Grades    Sum
    ____________    _____    ______    ___

    CHM               3        2        5 
    Calc              4        4        8 
    CHM lab           2        0        2 
    College comp      4        1        5 
    ECAL              1        4        5 
    MHCS              1        3        4 
    EGE               1        4        5 

[Nils Hammarsten Brywe]

Thank you so much!!

[Nils Hammarsten Brywe]

I have another question though. This coding is made specific for when the grades are in that specific order. Is there any way to write a code it in such a way that no matter what grades are put into the excel file, you can still calculate the the gpa with the same code? Because, if I would change the grades now, the code wouldn't be able to convert them to their corresponding numbers

[Cris LaPierre]

Categorical variables can be created independent of the values that actually appear in the data. So unless you are going to use a completely different grading scheme. categoricals are the way to go. Just define the categories yourself instead of relying on the dataset to have every one of them.

For example, say I have a categorical with just the grades "C" and "F". I would do this in place of the renamecats code shared previously.

grd = categorical(["C","F"]);
grd = categorical(grd,["A","B","C","D","F"],["4","3","2","1","0"])

grd = 1×2 categorical array
     2      0 

The categorical grd has all 5 categories, but only 2 appear in the variable's data.

categories(grd)

ans = 5×1 cell array
    {'4'}
    {'3'}
    {'2'}
    {'1'}
    {'0'}

[Nils Hammarsten Brywe]

Aha okay, but in this case, I'd have to write a code after the user has put in their grades in the excel sheet, right?

My goal is to write a code that doesn't have to be changed depending on the grades in the excel sheet. That means a code that'd work no matter if the user puts in every different grade (A,B,C,D,F) or if they only have 4 of them, or 3 of them etc.

Thank you for your time.

[Cris LaPierre]

As long as you know the grading system is A-F, you can define that in the code before the sheet is loaded. That is what I was trying to show you how to do. That code will work even when not all the categories appear in the spreadsheet.

Feel free to test it out. That's the only way I would be convinced it works. And it's faster than waiting for replies here.

[Nils Hammarsten Brywe]

Aha okay. Thank you so much!

Answer 2

[Sulaymon Eshkabilov]

You can consider to convert it into cell array and matrix arrays, e.g.:

CLS = table2array(T(:,1))    % Cell Array
HRS = table2array(T(:, 2))   % Matrix Array
GRD = table2array(T(:,3))    % Cell Array

Answer 3

[Nils Hammarsten Brywe]

Thank you! However, I uploaded the wrong excel file. In the actual one, the grades are characters. I'm trying to make a multiplication between the 'Hours' and the 'Grades" but it's unable to do it since the numbers are numeric and the grades are not only characters but they are also in cells.