Using groupsummary with Floating Number of Columns to Analyze

Hello,

I'm planning on using the groupsummary function to analyze different groups of data at various timesteps (columns).

The number of timesteps will vary, so I don't want to hard code in a set number.

My first issue that I'm having came whenever I changed the name of the columns that contain the data I want to analyze.

After importing into MATLAB, the column headers were originally just numbers (0, 1, 2, and 3), so MATLAB gave it generic 'VarName3', 'VarName4', 'VarName5', and 'VarName6'. (Columns 1 and 2 aren't needed)

I changed these to be the original numbers by extracting data from the Excel file and converting the numbers to strings (using num2str).

When the column headers were the VarName, the groupsummary function worked (code below):

G = groupsummary(table,'groupnames',"sum",{'VarName3','VarName4'});

But now that I changed it to be reflective of the timestep in quesiton, it gives the error 'Invalid data variable'.

G = groupsummary(table,'groupnames',"sum",{'0','1'});

I'm not sure why just changing the column headers would cause an error.

If anyone has any insight, I would appreciate it.

My second question is how to have the number of columns to analyze be dependent on the imported table?

I was thinking something like this, where the first column to analyze is always the 3rd column, and the very last column is never analyzed, and N is the total number of columns.

for k=3:N-1
    G = groupsummary(table,'groupnames',"sum",table(:,k));
end

As I understand it, the part in the function where you state which columns you want to analyze must be written as a string value, so something in the apostrophe.

With that in mind, I'm not sure how to make it float, since, at least as far as I'm aware, you can't put variable (k) in apostrophes.

Any help is appreciate, let me know if you'd like more detail, or I can split this question up into two separate posts.

Thanks.

[Cris LaPierre]

Please attach your file, or save your variables to a mat file and attach that to your post using the paperclip icon.

[Jon]

Sure thing, I will attach it shortly after I post this.

This is a simplified version of my full table.

Basically the Last column is a copy of the first column but without the number attached to it (done in MATLAB usually).

The 2nd column is not imported (I put in an arbitrary Area).

Columns 3-6 are the ones in quesiton.

Here there are four columns, but what I want to do is have my code work for any number of columns.

So if N is the width of the table, I will want to analyze via groupsummary columns 3 through N-1 always based on the groupnames column.

Answer 1

[Cris LaPierre]

There is nothing wrong with your first code snippet. The error is simply telling you that your table does not have a variable (column) named '0' or '1'. Check that you have properly renamed the table variables.

Health = {'Fair';'Poor';'Excellent';'Good';'Fair'};
Height = [71;69;64;67;64];
Weight = [176;163;131;133;119];
BloodPressure = [124 93; 109 77; 125 83; 117 75; 122 80];
T1 = table(Health,Height,Weight,BloodPressure,'VariableNames',{'groupnames','0','1','2'});

G = groupsummary(T1,'groupnames',"sum",{'0','1'})

G = 4×4 table
     groupnames      GroupCount    sum_0    sum_1
    _____________    __________    _____    _____

    {'Excellent'}        1           64      131 
    {'Fair'     }        2          135      295 
    {'Good'     }        1           67      133 
    {'Poor'     }        1           69      163 

For your second question, the code you are proposing won't work well. You'd only ever end up with the final results, as each loop will overwrite the previous one. There is also no need to put this in a loop. Just use the colon operator and width as shown here:

G = groupsummary(T1,'groupnames',"sum",2:width(T1)-1)

G = 4×4 table
     groupnames      GroupCount    sum_0    sum_1
    _____________    __________    _____    _____

    {'Excellent'}        1           64      131 
    {'Fair'     }        2          135      295 
    {'Good'     }        1           67      133 
    {'Poor'     }        1           69      163 

[Jon]

Thank you. For question 1, I verified the column headers were correct, but it still wouldn't work. I think it had to do with the way I was calling the values from an Excel sheet. Regardless, I found a way around it and don't really need to do it that way anymore (I just built a structure and had the first column correspond to the time).

For Question 2, I didn't realize you could have a function in that part of the groupsummary function. All the documents and forum posts I read always had the column header as a string. That's good to know, I appreciate it.

[Cris LaPierre]

Here's how I would do it with your data file. I would recommend avoiding using table as a variable name, as that is the name of the MATLAB function for creating tables.

data = readtable('table.xlsx','ReadVariableNames',true,'PreserveVariableNames',true)

data = 556×7 table
        Nodes         Area     0        1      2       3       groupnames 
    ______________    ____    ____    _____    __    ______    ___________

    {'BLOCK_A.1' }     3        18        7    20         0    {'BLOCK_A'}
    {'BLOCK_A.2' }     3      18.1     9.45    21    5.4343    {'BLOCK_A'}
    {'BLOCK_A.3' }     3      18.2     11.9    22    10.869    {'BLOCK_A'}
    {'BLOCK_A.4' }     3      18.3    14.35    23    16.303    {'BLOCK_A'}
    {'BLOCK_A.5' }     3      18.4     16.8    24    21.737    {'BLOCK_A'}
    {'BLOCK_A.6' }     3      18.5    19.25    25    27.172    {'BLOCK_A'}
    {'BLOCK_A.7' }     3      18.6     21.7    26    32.606    {'BLOCK_A'}
    {'BLOCK_A.8' }     3      18.7    24.15    27     38.04    {'BLOCK_A'}
    {'BLOCK_A.9' }     3      18.8     26.6    28    43.474    {'BLOCK_A'}
    {'BLOCK_A.10'}     3      18.9    29.05    29    48.909    {'BLOCK_A'}
    {'BLOCK_A.11'}     3        19     31.5    30    54.343    {'BLOCK_A'}
    {'BLOCK_A.12'}     3      19.1    33.95    31    59.777    {'BLOCK_A'}
    {'BLOCK_A.13'}     3      19.2     36.4    32    65.212    {'BLOCK_A'}
    {'BLOCK_A.14'}     3      19.3    38.85    33    70.646    {'BLOCK_A'}
    {'BLOCK_A.15'}     3      19.4     41.3    34     76.08    {'BLOCK_A'}
    {'BLOCK_A.16'}     3      19.5    43.75    35    81.515    {'BLOCK_A'}

G1 = groupsummary(data,'groupnames',"sum",{'0','1'})

G1 = 4×4 table
     groupnames     GroupCount    sum_0      sum_1   
    ____________    __________    _____    __________

    {'BLOCK_A' }        61         1281        4910.5
    {'BLOCK_B' }        33        848.1        6456.4
    {'SPHERE'  }       261        10544    1.4506e+05
    {'TRIANGLE'}       201        12764    2.2547e+05

G2 = groupsummary(data,'groupnames',"sum",3:width(data)-1)

G2 = 4×6 table
     groupnames     GroupCount    sum_0      sum_1       sum_2      sum_3   
    ____________    __________    _____    __________    _____    __________

    {'BLOCK_A' }        61         1281        4910.5     3050        9944.8
    {'BLOCK_B' }        33        848.1        6456.4     3201         13809
    {'SPHERE'  }       261        10544    1.4506e+05    63684    3.1771e+05
    {'TRIANGLE'}       201        12764    2.2547e+05    95475    4.9699e+05

[Jon]

Very nice, thanks. I'm assuming that 'PreserveVariableNames' function keeps the name of the headers regardless of the form? I think that was the issue I was running into.

[Cris LaPierre]

It looks like that is an undocumented syntax. Use this instead: "VariableNamingRule","preserve".

You can read more here: https://www.mathworks.com/help/matlab/ref/readtable.html#mw_47bd1f02-32a6-44af-aedd-70395ba4c587

I also prefer strings over character arrays, so I would ultimately do this.

data = readtable('table.xlsx','ReadVariableNames',true,'VariableNamingRule','preserve',...
    'TextType','string')
G1 = groupsummary(data,'groupnames',"sum",{'0','1'})
G2 = groupsummary(data,'groupnames',"sum",3:width(data)-1)