using spreadsheet column headers as variable

2020 5 20
2 个回答
回答已采纳
更新时间：2020 5 22
22 次查看（30 天）

0 个投票

I am writing a function to import an Excel table. I am using 'opts' commands in order to add some versatility to the function. However, when I use readtable(... , opts),the variables for my imported table say 'Var1, Var2, Var3..'. I want my variable names to be the variables contained in the first row of my spreadsheet. How am I able to fix this? Obviously, I don't want to use opts.'VariableNames' and type out each variable that I have. That would take way too long.
P.S. this does not happen when I use juse readtable(..), it only occurs when I have the opts with it.

2 个评论
显示 无 隐藏 无

Sai Gudlur
Sai Gudlur 2020-5-20
Try this or maybe you sharing your code might give a little more information.
[filename,pathname] = uigetfile('*.xlsx');
fullfilename = fullfile(pathname,filename);
A = importdata(fullfilename);
headers = A.textdata(1,end);
Forrest Ward
Forrest Ward 2020-5-20
I'll try that! And here is what my code currently looks like.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6,'PreserveVariableNames',true);
opts = setvaropts(opts,'FillValue','0');
DataTable = readtable(filename,opts,'ReadRowNames',true);
end

请先登录，再进行评论。

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

 采纳的回答

Cris LaPierre
Cris LaPierre 2020-5-20

0 个投票

To read the tabular data while preserving variable names, set the 'PreserveVariableNames' parameter to true.
T_preserve = readtable('sampletable.txt','PreserveVariableNames',true)
You must be using MATLAB R2019b or newer to use this.
If you are using opts, try this.
opts = detectImportOptions;
...
opts.ReadVariableNames = true;
opts.PreserveVariableNames=true;
T_preserve = readtable('sampletable.txt',opts);

17 个评论
显示 15更早的评论 隐藏 15更早的评论

Forrest Ward
Forrest Ward 2020-5-20
When I used your code I got an error message saying
Unrecognized property 'ReadVariableNames' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'.
Error in ReadDataTable (line 4)
opts.ReadVariableNames = true;
Forrest Ward
Forrest Ward 2020-5-20
Here is what my code looks like currently.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
DataTable = readtable(filename,opts,'ReadVariableNames',true,'ReadRowNames',true);
end
And for some reason I'm still getting 'Var1, Var2, Var3..." as my headers for each column.
Walter Roberson
Walter Roberson 2020-5-20
do not mix name/value pairs with passing the options object.
Forrest Ward
Forrest Ward 2020-5-21
Could you elaborate a little more on that? Sorry, I'm still very new to MatLab
Cris LaPierre
Cris LaPierre 2020-5-21
See my post above. You can either use Name-Value pairs with readtable (like the first code example), or you can use readtable with options specified, like the second code example. You cannot combine the two techniques.
Update your code to be something like this. Note that we don't have your file, so this code is untested.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
opts.ReadVariableNames = true;
opts.ReadRowNames = true;
DataTable = readtable(filename,opts);
end
Forrest Ward
Forrest Ward 2020-5-21
What you're saying makes sense to me, but I copied and pasted your code above into matlab (just to test it with my file) but I'm still getting the error message
Unrecognized property 'ReadVariableNames' for class 'matlab.io.spreadsheet.SpreadsheetImportOptions'.
Error in ReadDataTable (line 5)
opts.ReadVariableNames = true;
I'm getting that for the 'ReadVariableNames' and the 'ReadRowNames'
Forrest Ward
Forrest Ward 2020-5-21
Also, my file is an .xlsx and not a .txt. file. Not sure if that makes much difference, but just letting you know
Cris LaPierre
Cris LaPierre 2020-5-21
I think we've gotten as far as we can with hypotheticals. Please attach your file, or at least something matching the format and layout for us to compare against. Also, what version of MATLAB are you using.
Forrest Ward
Forrest Ward 2020-5-21
I am using the newest version of matlab (only bought it only a couple weeks ago). I am attaching the spreadsheet that I've been using to test my function. I hope to use this function for a much much larger spreadsheet in the future, but for now I've found it easier to just test it on a smaller file with less variables.
Forrest Ward
Forrest Ward 2020-5-21
And I've been calling my function with
ReadDataTable('MyTable.xlsx')
Walter Roberson
Walter Roberson 2020-5-21
function DataTable = ReadDataTable(filename);
opts = detectImportOptions(filename);
opts.PreserveVariableNames = true;
opts = setvaropts(opts, strcmp(opts.VariableTypes,'double'), 'FillValue', 0);
opts = setvaropts(opts, strcmp(opts.VariableTypes,'char'), 'FillValue', '0');
opts.RowNamesRange = 'A2';
DataTable = readtable(filename, opts);
end
Are you sure you want empty text areas to be filled with '0' ?? Filling double with numeric 0 makes sense, but rarely filling text with '0' (instead of something like 'N/A' or 'NA' or 'missing')
Forrest Ward
Forrest Ward 2020-5-21
Your code worked perfectly for setting my column headers in my spreadsheet as the variables! Thank you so much for that!! And I was just using the test '0' as a placeholder. And was going to change it to 'missing' or 'N/A' later on. Although, the FillValue doesn't seem to work with my imported spreadsheet. The missing information in my table is coming out as '0x0char'. Kind of like what would happen if I didn't even use FillValue.
Forrest Ward
Forrest Ward 2020-5-21
Also, do you always have to state the type of variable that your using to fill the missing information with (i.e. char, double, str)?
Cris LaPierre
Cris LaPierre 2020-5-21
And here is a minimalist approach. This lets detectImportOptions do most of the work.
function DataTable = ReadDataTable(filename)
opts = detectImportOptions(filename);
opts.VariableTypes = ["string", "double", "categorical", "categorical", "categorical", "categorical"];
DataTable = readtable(filename,opts);
end
Cris LaPierre
Cris LaPierre 2020-5-21
编辑：Cris LaPierre 2020-5-21
To answer your last question, if you want to specify the fill value, then yes, your fill value must much the variable data type. If you want the default 'missing' value, then specifying the varible datatype will suffice.
Walter Roberson
Walter Roberson 2020-5-21
fillmissing can fill '' to a preferred text.
Forrest Ward
Forrest Ward 2020-5-22
Thank you guys very much for the help! I think I'm getting it now!!

请先登录，再进行评论。

更多回答（1 个）

Jeremy Hughes
Jeremy Hughes 2020-5-21

0 个投票

When you create import options from scratch, you need to specify all the information, including where the variablenames and data are.
function DataTable = ReadDataTable(filename)
opts = spreadsheetImportOptions('NumVariables',6);
opts = setvaropts(opts,'FillValue','0');
opts.PreserveVariableNames = true;
% You need to specify where to read the variable names from.
opts.VariableNameRange = 'B1';
opts.DataRange = 'B2';
opts.RowNamesRange = 'A2'
DataTable = readtable(filename,opts,'ReadVariableNames',true,'ReadRowNames',true);
end

类别

在 帮助中心 和 File Exchange 中查找有关 Text Data Preparation 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by