Formatting data from an excel sheet (two criteria) in MATLAB

Question

I have a question related to another one recently asked. I download my data as a csv.file. I then format it in MATLAB. I obtan formatted data such as:

I would like to format the data so that I obtain:

In other words, the date should be in the first column, while the two identifier should be in the first two rows, respectively.

I tried the code provided by @gnovice, yet I have problems to adjust for the second identifier. The code is:

A = accumarray([rowIndex colIndex], data(:, 4), [], @(x) x(1));
A = [NaN colVals; rowVals A];

where data is equal to picture (1).

Therefore, I obtain a matrix A like:

 A = 
      NaN     1    2     3;
 20160101   100   80    90;
 20170101   150   90   200;

How can I adjust my code in a way that the second identifier is taken care of and A becomes:

 A = 
      NaN     1    2     3;
      NaN    10   10    15;
 20160101   100   80    90;
 20170101   150   90   200;

Answer 1

You can use this code

C = unique([identifier_1,identifier_2],'rows')

And format your data accordingly

Answer 2

The answer from albusSimba points in the right direction. You'll want to collect the columns containing your identifiers (i.e. data(:, [1 3])), pass that to unique with the 'rows' option to find the unique row combinations, then capture the third output to use as the index for aggregation by accumarray. Your final matrix formatting will then just have to be changed to account for the second identifier:

[rowVals, ~, rowIndex] = unique(data(:, 2));
[colVals, ~, colIndex] = unique(data(:, [1 3]), 'rows');
A = accumarray([rowIndex colIndex], data(:, 4), [], @(x) x(1));
A = [NaN colVals(:, 1).'; NaN colVals(:, 2).'; rowVals A];

And the result for your sample data:

A =

         NaN           1           2           3
         NaN          10          10          15
    20160101         100          80         200
    20170101         150          90         200