Loop through a table using a specific condition and generate new table satisfying the particular condition

Example table

ID Value

ID1 5

ID1 7

ID2 3

ID1 1

Condition: If IDs are the same add the corresponding values and generate new table. The new table should be:

ID Value

ID1 12

ID2 3

ID3 1

Answer 1

[Rahul]

Hi George,

I understand that you intend to loop through an existing MATLAB Table and generate a new table with unique IDsalong with values as that of the sum of values associated with this ID, in the previous table.

To generate the desired table, you can use ‘varfun’ function that applies a function logic, using its specified handle, separately to each variable of the input table.

Here’s a brief code overview of a possible solution to this issue:

• Create the Original Table: Define the original table with your data.

% Step 1: Create the original table
T = table({'ID1'; 'ID1'; 'ID2'; 'ID1'}, [5; 7; 3; 1], 'VariableNames', {'ID', 'Value'})

T = 4x2 table
      ID       Value
    _______    _____

    {'ID1'}      5  
    {'ID1'}      7  
    {'ID2'}      3  
    {'ID1'}      1  

• Group By ID and Sum Values: Use MATLAB function ‘varfun’ to group the data by ID and sum the Value for each ID.

% Step 2: Group by ID and sum the values
% Convert the table into a grouped table where each group is identified by the ID
G = varfun(@sum, T, 'GroupingVariables', 'ID', 'InputVariables', 'Value');

varfun(@sum, T, 'GroupingVariables', 'ID', 'InputVariables', 'Value'): groups the table by ID and computes the sum of the Value for each group. This function returns a new table where each row corresponds to a unique ID, and the Value column contains the sum of the values for that ID.

• Create the New Table: Construct the new table from the results.

% Step 3: Create the new table     
% Rename the 'GroupCount' variable to 'Value' to match the new table's variable names     
NewTable = renamevars(G, 'sum_Value', 'Value');
   
% Display the new table
disp(NewTable);

      ID       GroupCount    Value
    _______    __________    _____

    {'ID1'}        3          13  
    {'ID2'}        1           3  

‘renamevars’ is used to rename the ‘sum_Value’ variable to Value in the resulting table for consistency with the desired output.

For more information regarding usage of ‘varfun’ function in MATLAB, refer to the documentation link mentioned below:

https://www.mathworks.com/help/matlab/ref/table.varfun.html

Answer 2

[Stephen23]

ID = ["ID1";"ID1";"ID2";"ID3"];
Value = [5;7;3;1];
T = table(ID,Value)

T = 4x2 table
     ID      Value
    _____    _____

    "ID1"      5  
    "ID1"      7  
    "ID2"      3  
    "ID3"      1  

G = groupsummary(T,'ID','sum')

G = 3x3 table
     ID      GroupCount    sum_Value
    _____    __________    _________

    "ID1"        2            12    
    "ID2"        1             3    
    "ID3"        1             1    

Answer 3

[Lei Hou]

rowfilter is used to select table rows meeting specific condition. It might be helpful to you, too.

https://www.mathworks.com/help/matlab/ref/matlab.io.rowfilter.html#mw_3c39fb64-020a-4cba-9343-7667662df166