Import table from row oriented excel sheet

43 次查看（过去 30 天）
Bruce MacWilliams
Bruce MacWilliams 2019-5-22
评论： Jerome Baranger 2022-4-22
I have Excel worksheets oriented such that variable names are in the first column and variables in rows. e.g.:
Var1 1 2 3 ...
Var2 4 5 6 ...
Var3 7 8 9 ...
etc.
This would be "row oriented" data to use the Matlab description of the readtable command which is expecting "column oriented" data. There does not seem to be a way to use readtable to transpose the data so it is read correctly. I can read the array and then transpose the data but there does not seem to be a way to read in variable names from a column with this command/approach. Seems like this should just be a simple orientation option that you can set in the function call or table options. Am I missing something? I looked at a bunch of similar themed questions but did not find a satisfactory answer. My guess is that I have two choices if I wnat to end up with table data:
1) Transpose the data in Excel into a new worksheet and use readtable()
2) Use xlsread() or some other variant to read data and varialbe names into separate arrays, do the transpose on the data and cobble together the table using opts.VariableNames
  1 个评论
Bob Thompson
Bob Thompson 2019-5-22
Does the option to 'ReadRowNames' not work for your situation? This is for the readtable command.

请先登录，再进行评论。

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

采纳的回答

Bruce MacWilliams
Bruce MacWilliams 2019-5-22
编辑：Bruce MacWilliams 2019-5-23
Thanks. That got me going in the right direction. It was at least a way to read the variable names. Here's the complete solution I came up with ...
% Get row-oriented table A options, columns of interest
Aopts = detectImportOptions(FileName,'Sheet','Worksheet Name','Range','J:CI');
% Set the variable range for the column containing variable names
Aopts.RowNamesRange = 'B3:B291';
% Now read the table with these options and ReadRowNames set to true
A = readtable(FileName,Aopts,'ReadRowNames',true);
% Now parse out numerical array that will be transposed
AData = table2array(A(:,10:end));
% and store the row names in a separate vector
VarNames = matlab.lang.makeValidName(A.Properties.RowNames);
% Now build the new transposed table
A2 = array2table(AData','VariableNames',VarNames);
Still more kludge than it needs to be but it works.

更多回答（0 个）

类别

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

标签

产品


版本

R2019a

Community Treasure Hunt

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

Start Hunting!

Translated by