Trouble pivoting large table using unstack function

3 次查看（过去 30 天）
Justin Dubin
Justin Dubin 2019-6-26
评论： Justin Dubin 2019-6-26
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.

请先登录，再回答此问题。

采纳的回答

Guillaume
Guillaume 2019-6-26
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
  5 个评论
显示 3更早的评论
Guillaume
Guillaume 2019-6-26
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
Justin Dubin 2019-6-26
Well that sure solved it! Thanks for the help, Guillaume. I didn't realize you could not pivot NaNs. I learned something today!

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Descriptive Statistics 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by