Sorting Column Variable to a Row

I have been given my data in 3 Columns of a table as depicted in the below screenshot (I have had to try and replicate a similar scenario using excel as I can't share the actual data):

and I wish to have it in this format for easier analysis/plots such as surface plots, with NaN is cells where there is no value for that date and category:

Any help would be appreciated.

Thanks,

Huw

Answer 1

[Stephen23]

Do NOT use loops for this, the inbuilt tools are much better! First lets create some fake data in a table:

C = randi(6,30,1);
D = datetime(2021,1,randi(3,30,1));
T = unique(table(D,C),'rows');
T.V = randi([10,100],height(T),1)/10

T = 15×3 table
         D         C     V 
    ___________    _    ___

    01-Jan-2021    1    8.5
    01-Jan-2021    2    6.6
    01-Jan-2021    3    4.2
    01-Jan-2021    4    1.6
    01-Jan-2021    5    8.5
    01-Jan-2021    6    8.9
    02-Jan-2021    2    7.7
    02-Jan-2021    3    3.7
    02-Jan-2021    5    3.7
    02-Jan-2021    6    1.6
    03-Jan-2021    1    7.8
    03-Jan-2021    2    5.8
    03-Jan-2021    3    7.3
    03-Jan-2021    4    3.7
    03-Jan-2021    6    4.8

Rearranging how you requested is very easy with just one unstack command:

U = unstack(T,'V','C', 'VariableNamingRule','preserve')

U = 3×7 table
         D          1      2      3      4      5      6 
    ___________    ___    ___    ___    ___    ___    ___

    01-Jan-2021    8.5    6.6    4.2    1.6    8.5    8.9
    02-Jan-2021    NaN    7.7    3.7    NaN    3.7    1.6
    03-Jan-2021    7.8    5.8    7.3    3.7    NaN    4.8

[SALAH ALRABEEI]

Wow!, awesome,much more better.

[Huw Wadkin]

Thank you. I get an error for 'VariableNamingRule' saying invalid parameter name when I run your script? If I just do unstack(T,'V', 'C') it works. What do these options do?

[Stephen23]

@Huw Wadkin: what options you have available depends on what MATLAB version you are using, which so far you have not told us. Use the local help of your installed MATLAB to know what options you can use.

"What do these options do?"

https://www.mathworks.com/help/matlab/ref/unstack.html#mw_36cb1afc-08c6-4dd3-af76-9107977f696e

Answer 2

[SALAH ALRABEEI]

Assume your table ( without labels) is of size nx3; where the 1st col is your dates (in numbers), 2nd is your cat, and the 3rd is the values. See this example

x=[ 1 1 1 2 2 2 2 3 3];
y=[0:8];
z=round(100*rand(1,9));
D = [x;y;z]';
a=unique(D(:,1));
B=nan*ones(length(a),size(D,1));
for i = 1:length(a)
    B(i,a(i)==D(:,1))=D(a(i)==D(:,1),3);
end