Locating rows which share same strings and other elements in a cell array in MATLAB

Question

Suppose I have a cell array in MATLAB of the following type:

Station_Name  Node_Number  Generation_type  Generated_Power (MW)

Barry              5        Gas             120  
Brigg              3        Nuclear         135
Brixton            1        Gas             110 
Shoreham           2        Solar           90 
Kelsey             5        Gas             85 
Jinnie             4        Nuclear         65
Pollock            2        Gas             150
Surret             2        Gas             160 

(The actual problem contains much more stations, nodes, and generation types though I am writing this here for simplification).

I want to sort the data to an array of the following type:

Node_Number    Generation_type      Total_Generated_Power

1              Solar                 
1              Gas  
2              Hydro 
2              Gas
.
.
.
3        
4
5

So I want to create a script that will automatically produce an array with a different row for each generation type, for each one of the nodes of the cell array (Actually more than 5 nodes and more than 3 generation types).

My initial thought is that I need to make a for loop that will initially check for the elements that are same at the second column, and for those elements the strings at the third column will be compared to see which are the same. Then for those stations that share the same generation type at the same node their generated power will be added together. Then a new variable will need to be created as the new array which will hold the total generated power for each type in each node.

The for loop would be initialised from 1 till the max number of nodes, and would check each one of the rows of the array. The use of the strcmp for finding the stations that share the same string i.e same generation type should also be used, from what I understand.

Answer 1

This should do the trick:

% Sample data...
C = {
    'Barry'     5   'Gas'       120;
    'Brigg'     3   'Nuclear'   135;
    'Brixton'   1   'Gas'       110;
    'Shoreham'  2   'Solar'     90;
    'Kelsey'    5   'Gas'       85;
    'Jinnie'    4   'Nuclear'   65;
    'Pollock'   2   'Gas'       150;
    'Surret'    2   'Gas'       160
};

% Create a table from the cell matrix...
T = cell2table(C,'VariableNames',{'StationName' 'NodeNumber' 'GenerationType' 'GeneratedPower'});

% Generate a grouping for the table based on the 2nd and 3rd columns...
[G,NodeNumber,GenerationType] = findgroups(T.NodeNumber,T.GenerationType);

% Apply the sum function using a group-wise approach...
TotalGeneratedPower = splitapply(@sum,T.GeneratedPower,G);

% Create a new table with the result...
result = table(NodeNumber,GenerationType,TotalGeneratedPower);

% Sort the new table according to the first two columns...
result = sortrows(result,[1 1])

Output:

NodeNumber    GenerationType    TotalGeneratedPower
__________    ______________    ___________________

1             'Gas'             110                
2             'Gas'             310                
2             'Solar'            90                
3             'Nuclear'         135                
4             'Nuclear'          65                
5             'Gas'             205                

For more information about the functions used in the code, refer to the following pages of the official Matlab documentation:

• findgroups
• splitapply