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.
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: