Trouble pivoting large table using unstack function

I have a large table (6307840x42) that I need to pivot. To scale down the problem, let's assume the table looks something like this:

Measurement    Value     DUT #     DUT Barcode
___________    _____    _______    ___________
Temperature     50         1          ABC123 
Humidity        15         1          ABC123
Voltage         1.2        1          ABC123
Temperature     52         2          DEF456
Humidity        12         2          DEF456
Voltage         1.1        2          DEF456
Temperature     48         3          GHI789
Humidity        17         3          GHI789
Voltage         0.8        3          GHI789

I want to pivot the above table such that the values in the Measurement column become new column titles which contain the data stored in the Value column:

Temperature    Humidity    Voltage    DUT #    DUT Barcode
___________    ________    _______    _____    ___________
    50            15         1.2        1        ABC123
    52            12         1.1        2        DEF456
    48            17         0.8        3        GHI789     

I have tried using the unstack function to achieve this, but the result spits out a 0x62 sized table containing only the pivoted variable names, but no data. My code looks something like this:

    ds = datastore('myTable.csv')
    myTable = readall(ds);
    myPivotedTable = unstack(myTable,'Value','Measurement');

Am I using the unstack function incorrectly? Am I approaching the problem compeltely the wrong way? Any help would be greatly appreciated.

Answer 1

[Guillaume]

Works fine for me with your little example:

Measurement = repmat({'Temperature'; 'Humidity'; 'Voltage'}, 3, 1);
Value = [50; 15; 1.2; 52; 12; 1.1; 48; 17; 0.8];
DUT = repelem([1; 2; 3], 3);
Barcode = repelem({'ABC123'; 'DEF456'; 'GHI789'}, 3);
myTable = table(Measurement, Value, DUT, Barcode)
unstack(myTable, 'Value', 'Measurement')

myTable =
  9×4 table
     Measurement     Value    DUT    Barcode 
    _____________    _____    ___    ________
    'Temperature'      50      1     'ABC123'
    'Humidity'         15      1     'ABC123'
    'Voltage'         1.2      1     'ABC123'
    'Temperature'      52      2     'DEF456'
    'Humidity'         12      2     'DEF456'
    'Voltage'         1.1      2     'DEF456'
    'Temperature'      48      3     'GHI789'
    'Humidity'         17      3     'GHI789'
    'Voltage'         0.8      3     'GHI789'
    
    
ans =
  3×5 table
    DUT    Barcode     Humidity    Temperature    Voltage
    ___    ________    ________    ___________    _______
     1     'ABC123'       15           50           1.2  
     2     'DEF456'       12           52           1.1  
     3     'GHI789'       17           48           0.8  

so, I'm not sure why you end up with an empty result. However, not that if unique DUT don't match unique Barcode you're going to end up with a lot of NaN in your output (but it should never be empty). It may be better to consider either DUT or Barcode as a constant:

>> unstack(myTable, 'Value', 'Measurement', 'ConstantVariables', 'Barcode')
ans =
  3×5 table
    DUT    Barcode     Humidity    Temperature    Voltage
    ___    ________    ________    ___________    _______
     1     'ABC123'       15           50           1.2  
     2     'DEF456'       12           52           1.1  
     3     'GHI789'       17           48           0.8  

[Justin Dubin]

That little example works for me, as well. The only difference between the example and my actual table are the number of values in the Measurement column (22, I believe) and the number of "extra" columns aside from Measurement and Value (columns like DUT and Barcode). I've tried assigning different columns as constant variables, but with no luck.

I understand I am working with a very large table. Could the sheer size of the table have anything to do with it?

[Guillaume]

No, the size of the table shouldn't have an effect on the output. If you can attach or share the actual table, we can have a look at why it doesn't work.

[Justin Dubin]

Unfortunately both raw data file and table object are too large to be shared locally as an attachment. I'm not certain of the rules in this forum regarding file sharing, but I've made the raw data file available on Google Drive for download here. My actual code is as follows:

% Clear Workspace
clc
clear
close all

% Load Data
ds = datastore('EmmonsL18-1_THD.csv');
myTable = readall(ds);
myPivotTable = unstack(myTable,'Value','Measurement');

[Guillaume]

The problem has nothing to do with the size of the table indeed. It's due to the NaN columns. You could either not include them in the call to unstack, or tell unstack to consider them constant:

ds = datastore('EmmonsL18-1_THD.csv');
myTable = readall(ds);   %might take a while!

%find NaN variables
toignore = varfun(@(var) isnumeric(var) && any(isnan(var)), mytable, 'OutputFormat', 'Uniform');

%option 1: don't include the NaN variables
myPivotTable = unstack(myTable(:, ~toignore), 'Value', 'Measurement');

%option 2: consider them constant
myPivotTable = unstack(myTable, 'Value', 'Measurement', 'ConstantVariables', toignore);

[Justin Dubin]

Well that sure solved it! Thanks for the help, Guillaume. I didn't realize you could not pivot NaNs. I learned something today!