setvaropts
Set variable import options
Description
opts = setvaropts(opts,Name,Value)opts object based on the
specifications in the Name,Value arguments and returns the
opts object.
opts = setvaropts(opts,selection,Name,Value)opts for the variables specified in the
selection argument, based on the specifications in the
Name,Value arguments.
Examples
Create an import options object, set the options for selected variables, and import the data using the tailored options and the readtable function.
Create an options object for the spreadsheet patients.xls.
opts = detectImportOptions('patients.xls');Set the FillValue property for the Smoker, Diastolic, and Systolic variables.
opts = setvaropts(opts,'Smoker','FillValue',false); opts = setvaropts(opts,{'Diastolic','Systolic'},'FillValue',0);
Select the variables you want to import.
opts.SelectedVariableNames = {'Smoker','Diastolic','Systolic'};Import the variables and display a summary.
T = readtable('patients.xls',opts);
summary(T) T: 100×3 table
Variables:
Smoker: logical (34 true)
Diastolic: double
Systolic: double
Statistics for applicable variables:
NumMissing Min Median Max Mean Std
Diastolic 0 68 81.5000 99 82.9600 6.9325
Systolic 0 109 122 138 122.7800 6.7128
Importing data that has missing or incomplete fields requires recognizing the missing instances and deciding how the missing instances will be imported. Use importOptions to capture both these decisions and fetch the data using readtable.
Create an import options object for the file, update properties that control the import of missing data, and then use readtable to import the data. Note that the dataset airlinesmall_subset.csv has two numeric variables ArrDelay and DepDelay, that contain missing data indicated by NA.
Create an import options object from the file.
opts = detectImportOptions("airlinesmall_subset.csv");Use the TreatAsMissing property to specify the characters in the data that are place holders for missing instances. In this example, the two numeric variables ArrDelay and DepDelay contain missing fields that contain the text NA.
opts = setvaropts(opts,["ArrDelay","DepDelay"],"TreatAsMissing","NA");
Specify the action for the importing function to take when importing missing instances. See ImportOptions properties page for more options.
opts.MissingRule = "fill";Specify the value to use when the importing function finds a missing instance. Here the missing instances in variables ArrDelay and DepDelay are replaced by 0.
opts = setvaropts(opts,["ArrDelay","DepDelay"],"FillValue",0);
Select the variables you want to work with and import them using readtable.
opts.SelectedVariableNames = ["ArrDelay","DepDelay"]; T = readtable("airlinesmall_subset.csv",opts);
Examine the values in ArrDelay and DepDelay. Verify that the importing function replaced the missing values denoted by NA.
T(42:55,:)
ans=14×2 table
ArrDelay DepDelay
________ ________
3 -4
0 -1
11 11
0 0
0 0
0 0
-9 5
-9 -3
2 6
0 0
1 1
0 4
9 0
-2 4
The readtable function automatically detects hexadecimal and binary numbers with the 0x and 0b prefixes. To import such numbers when they do not have prefixes, use an import options object.
Create an import options object for the file hexAndBinary.txt. Its third column has hexadecimal numbers without the 0x prefix.
opts = detectImportOptions('hexAndBinary.txt')opts =
DelimitedTextImportOptions with properties:
Format Properties:
Delimiter: {','}
Whitespace: '\b\t '
LineEnding: {'\n' '\r' '\r\n'}
CommentStyle: {}
ConsecutiveDelimitersRule: 'split'
LeadingDelimitersRule: 'keep'
TrailingDelimitersRule: 'ignore'
EmptyLineRule: 'skip'
Encoding: 'UTF-8'
Replacement Properties:
MissingRule: 'fill'
ImportErrorRule: 'fill'
ExtraColumnsRule: 'addvars'
Variable Import Properties: Set types by name using setvartype
VariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
VariableTypes: {'auto', 'auto', 'char' ... and 1 more}
SelectedVariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
VariableOptions: Show all 4 VariableOptions
Access VariableOptions sub-properties using setvaropts/getvaropts
VariableNamingRule: 'modify'
Location Properties:
DataLines: [1 Inf]
VariableNamesLine: 0
RowNamesColumn: 0
VariableUnitsLine: 0
VariableDescriptionsLine: 0
To display a preview of the table, use preview
To specify that the third column should be imported as hexadecimal values, despite the lack of a prefix, use the setvaropts function. Set the variable type of the third variable to int32. Set the number system for importing the third column to hex.
opts = setvaropts(opts,3,'NumberSystem','hex','Type','int32')
opts =
DelimitedTextImportOptions with properties:
Format Properties:
Delimiter: {','}
Whitespace: '\b\t '
LineEnding: {'\n' '\r' '\r\n'}
CommentStyle: {}
ConsecutiveDelimitersRule: 'split'
LeadingDelimitersRule: 'keep'
TrailingDelimitersRule: 'ignore'
EmptyLineRule: 'skip'
Encoding: 'UTF-8'
Replacement Properties:
MissingRule: 'fill'
ImportErrorRule: 'fill'
ExtraColumnsRule: 'addvars'
Variable Import Properties: Set types by name using setvartype
VariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
VariableTypes: {'auto', 'auto', 'int32' ... and 1 more}
SelectedVariableNames: {'Var1', 'Var2', 'Var3' ... and 1 more}
VariableOptions: Show all 4 VariableOptions
Access VariableOptions sub-properties using setvaropts/getvaropts
VariableNamingRule: 'modify'
Location Properties:
DataLines: [1 Inf]
VariableNamesLine: 0
RowNamesColumn: 0
VariableUnitsLine: 0
VariableDescriptionsLine: 0
To display a preview of the table, use preview
Read the file and import the first three columns as numeric values. The readtable function automatically detects that the first and second columns contain hexadecimal and binary values. The import options object specifies that the third column also contains hexadecimal values.
T = readtable('hexAndBinary.txt',opts)T=3×4 table
Var1 Var2 Var3 Var4
_____ ____ _____ ___________
255 255 51193 {'Device1'}
256 4 1471 {'Device2'}
43981 129 61455 {'Device3'}
Use the setvaropts function to update properties that control the import of text data. First, get the import options object for the file. Next, examine and update the options for the text variables. Finally, import the variables using the readtable function.
Preview the data in patients.xls. Notice the text data in the column LastName. Only a preview of the first 10 rows is shown here.
Get the import options object.
opts = detectImportOptions('patients.xls');Get and examine the VariableImportOptions for variable LastName.
getvaropts(opts,'LastName')ans =
TextVariableImportOptions with properties:
Variable Properties:
Name: 'LastName'
Type: 'char'
FillValue: ''
TreatAsMissing: {}
QuoteRule: 'remove'
Prefixes: {}
Suffixes: {}
EmptyFieldRule: 'missing'
String Options:
WhitespaceRule: 'trim'
Set the data type of the variable to string.
opts = setvartype(opts,'LastName','string');
Set the FillValue property of the variable to replace missing values with 'NoName'.
opts = setvaropts(opts,'LastName','FillValue','NoName');
Select, read, and display a preview of the first 10 rows of the variable.
opts.SelectedVariableNames = 'LastName'; T = readtable('patients.xls',opts); T.LastName(1:10)
ans = 10×1 string
"Smith"
"Johnson"
"Williams"
"Jones"
"Brown"
"Davis"
"Miller"
"Wilson"
"Moore"
"Taylor"
Use the setvaropts function to update properties that control the import of logical data. First, get the import options object for the file. Next, examine and update the options for the logical variables. Finally, import the variables using the readtable function.
Preview the data in airlinesmall_subset.xlsx. Notice the logical data in the column Cancelled. Only a preview of rows 30 to 40 is shown here.
Get the import options object.
opts = detectImportOptions('airlinesmall_subset.xlsx');Get and examine the VariableImportOptions for variable Cancelled.
getvaropts(opts,'Cancelled')ans =
NumericVariableImportOptions with properties:
Variable Properties:
Name: 'Cancelled'
Type: 'double'
FillValue: NaN
TreatAsMissing: {}
QuoteRule: 'remove'
Prefixes: {}
Suffixes: {}
EmptyFieldRule: 'missing'
Numeric Options:
ExponentCharacter: 'eEdD'
DecimalSeparator: '.'
ThousandsSeparator: ''
TrimNonNumeric: 0
NumberSystem: 'decimal'
Set the data type of the variable to logical.
opts = setvartype(opts,'Cancelled','logical');
Set the FillValue property of the variable to replace missing values with true.
opts = setvaropts(opts,'Cancelled','FillValue',true);
Select, read, and display a summary of the variable.
opts.SelectedVariableNames = 'Cancelled'; T = readtable('airlinesmall_subset.xlsx',opts); summary(T)
T: 1338×1 table
Variables:
Cancelled: logical (29 true)
Use DatetimeVariableImportOptions properties to control the import of datetime data. First, get the ImportOptions object for the file. Next, examine and update the VariableImportOptions for the datetime variables. Finally, import the variables using readtable.
Preview of data in outages.csv. Notice the date and time data in the columns OutageTime and RestorationTime. Only the first 10 rows are shown here.
Get the import options object.
opts = detectImportOptions('outages.csv');Get and examine the VariableImportOptions for datetime variables OutageTime and RestorationTime.
varOpts = getvaropts(opts,{'OutageTime','RestorationTime'})varOpts =
1×2 DatetimeVariableImportOptions array with properties:
Name
Type
FillValue
TreatAsMissing
QuoteRule
Prefixes
Suffixes
EmptyFieldRule
DatetimeFormat
DatetimeLocale
TimeZone
InputFormat
Set the FillValue property of the variables to replace missing values with current date and time.
opts = setvaropts(opts,{'OutageTime','RestorationTime'},...
'FillValue','now');Select, read, and preview the two variables. Notice the missing value in the second row of RestorationTime has been filled with current date and time.
opts.SelectedVariableNames = {'OutageTime','RestorationTime'};
T = readtable('outages.csv',opts);
T(1:10,:)ans=10×2 table
OutageTime RestorationTime
________________ ________________
2002-02-01 12:18 2002-02-07 16:50
2003-01-23 00:49 2025-08-09 12:21
2003-02-07 21:15 2003-02-17 08:14
2004-04-06 05:44 2004-04-06 06:10
2002-03-16 06:18 2002-03-18 23:23
2003-06-18 02:49 2003-06-18 10:54
2004-06-20 14:39 2004-06-20 19:16
2002-06-06 19:28 2002-06-07 00:51
2003-07-16 16:23 2003-07-17 01:12
2004-09-27 11:09 2004-09-27 16:37
Use the setvaropts function to update properties that control the import of categorical data. First, get the import options object for the file. Next, examine and update the options for the categorical variables. Finally, import the variables using the readtable function.
Preview the data in outages.csv. Notice the categorical data in the columns Region and Cause. This table shows only the first 10 rows.
Get the import options object.
opts = detectImportOptions('outages.csv');Get and examine the options for the variables Region and Cause.
getvaropts(opts,{'Region','Cause'})ans =
1×2 TextVariableImportOptions array with properties:
Name
Type
FillValue
TreatAsMissing
QuoteRule
Prefixes
Suffixes
EmptyFieldRule
WhitespaceRule
Set the data type of the variables to categorical.
opts = setvartype(opts,{'Region','Cause'},'categorical');Set the FillValue property of the variables to replace missing values with category name 'Miscellaneous'. Set TreatAsMissing property to 'unknown'.
opts = setvaropts(opts,{'Region','Cause'},...
'FillValue','Miscellaneous',...
'TreatAsMissing','unknown');Select, read, and display a summary of the two variables.
opts.SelectedVariableNames = {'Region','Cause'};
T = readtable('outages.csv',opts);
summary(T)T: 1468×2 table
Variables:
Region: categorical (5 categories)
Cause: categorical (10 categories)
Statistics for applicable variables:
NumMissing
Region 0
Cause 0
Import tabular data containing variables that have unwanted prefix and suffix characters. First, create an import options object and preview the data. Then, select the variables of interest and set their variable types and properties to remove the unwanted characters. Finally, import the data of interest.
Create import options for the file and preview the table.
filename = 'pref_suff_trim.csv';
opts = detectImportOptions(filename);
preview(filename,opts)Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property. Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.
ans=8×5 table
Time DayOfWeek Power Total_Fees Temperature
_________________________ _________________ ______________ ___________ _____________
{'Timestamp:1/1/06 0:00'} {'& Sun %20'} {'54.5448 MW'} {'$1.23' } {'-7.2222 C'}
{'Timestamp:1/2/06 1:00'} {'& Thu %20'} {'.3898 MW' } {'$300.00'} {'-7.3056 C'}
{'Timestamp:1/3/06 2:00'} {'& Sun %20'} {'51.6344 MW'} {'£2.50' } {'-7.8528 C'}
{'Timestamp:1/4/06 3:00'} {'& Sun %20'} {'51.5597 MW'} {'$0.00' } {'-8.1778 C'}
{'Timestamp:1/5/06 4:00'} {'& Wed %20'} {'51.7148 MW'} {'¥4.00' } {'-8.9343 C'}
{'Timestamp:1/6/06 5:00'} {'& Sun %20'} {'52.6898 MW'} {'$0.00' } {'-8.7556 C'}
{'Timestamp:1/7/06 6:00'} {'& Mon %20'} {'55.341 MW' } {'$50.70' } {'-8.0417 C'}
{'Timestamp:1/8/06 7:00'} {'& Sat %20'} {'57.9512 MW'} {'$0.00' } {'-8.2028 C'}
Select variables of interest, specify their types, and examine their variable import options values.
opts.SelectedVariableNames = {'Time','Total_Fees','Temperature'};
opts = setvartype(opts,'Time','datetime');
opts = setvaropts(opts,'Time','InputFormat','MM/dd/uu HH:mm'); % Specify datetime format
opts = setvartype(opts,{'Total_Fees','Temperature'},'double');
getvaropts(opts,{'Time','Total_Fees','Temperature'})ans =
1×3 VariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3)
Name: 'Time' | 'Total_Fees' | 'Temperature'
Type: 'datetime' | 'double' | 'double'
FillValue: NaT | NaN | NaN
TreatAsMissing: {} | {} | {}
EmptyFieldRule: 'missing' | 'missing' | 'missing'
QuoteRule: 'remove' | 'remove' | 'remove'
Prefixes: {} | {} | {}
Suffixes: {} | {} | {}
To access sub-properties of each variable, use getvaropts
Set the Prefixes, Suffixes, and TrimNonNumeric properties of variable import options to remove 'Timestamp:' from the variable Time, remove suffix 'C' from variable Temperature, and remove all nonnumeric characters from variable Total_Fees. Preview the table with the new import options.
opts = setvaropts(opts,'Time','Prefixes','Timestamp:'); opts = setvaropts(opts,'Temperature','Suffixes','C'); opts = setvaropts(opts,'Total_Fees','TrimNonNumeric',true); preview(filename,opts)
ans=8×3 table
Time Total_Fees Temperature
______________ __________ ___________
01/01/06 00:00 1.23 -7.2222
01/02/06 01:00 300 -7.3056
01/03/06 02:00 2.5 -7.8528
01/04/06 03:00 0 -8.1778
01/05/06 04:00 4 -8.9343
01/06/06 05:00 0 -8.7556
01/07/06 06:00 50.7 -8.0417
01/08/06 07:00 0 -8.2028
Import the data using readtable.
T = readtable(filename,opts);
Create an import options object for a file containing empty fields. Use the EmptyFieldRule parameter to manage the import of empty fields in your data. First, preview the data, and then set the EmptyFieldRule parameter for a specific variable. Finally, set EmptyFieldRule for all the variables and import the data.
Create an import options object for a file containing empty fields. Get the first eight rows of the table using the preview function. The default value for EmptyFieldRule is 'missing'. Therefore, the importing function treats empty fields as missing and replaces them with the FillValue value for that variable. Using VariableOptions for the third variable, preview the data. Here, the preview function imports the empty fields in the third variable as NaNs.
filename = 'DataWithEmptyFields.csv'; opts = detectImportOptions(filename); opts.VariableOptions(3) % Display the Variable Options for the 3rd Variable
ans =
NumericVariableImportOptions with properties:
Variable Properties:
Name: 'Double'
Type: 'double'
FillValue: NaN
TreatAsMissing: {}
QuoteRule: 'remove'
Prefixes: {}
Suffixes: {}
EmptyFieldRule: 'missing'
Numeric Options:
ExponentCharacter: 'eEdD'
DecimalSeparator: '.'
ThousandsSeparator: ''
TrimNonNumeric: 0
NumberSystem: 'decimal'
preview(filename,opts)
ans=8×7 table
Text Categorical Double Datetime Logical Duration String
__________ ___________ ______ __________ __________ ________ __________
{'abc' } {'a' } 1 01/14/0018 {'TRUE' } 00:00:01 {'abc' }
{0×0 char} {'b' } 2 01/21/0018 {'FALSE' } 09:00:01 {'def' }
{'ghi' } {0×0 char} 3 01/31/0018 {'TRUE' } 02:00:01 {'ghi' }
{'jkl' } {'a' } NaN 02/23/2018 {'FALSE' } 03:00:01 {'jkl' }
{'mno' } {'a' } 4 NaT {'FALSE' } 04:00:01 {'mno' }
{'pqr' } {'b' } 5 01/23/0018 {0×0 char} 05:00:01 {'pqr' }
{'stu' } {'b' } 5 03/23/0018 {'FALSE' } NaN {'stu' }
{0×0 char} {'a' } 6 03/24/2018 {'TRUE' } 07:00:01 {0×0 char}
Set EmptyFieldRule for the second variable in the table. First, select the variable, and then set EmptyFieldRule to 'auto'. Here, the readtable function imports empty fields of the categorical variable as <undefined>.
opts.SelectedVariableNames = 'Categorical'; opts = setvartype(opts,'Categorical','categorical'); opts = setvaropts(opts,'Categorical','EmptyFieldRule','auto'); T = readtable(filename,opts)
T=10×1 table
Categorical
___________
a
b
<undefined>
a
a
b
b
a
a
<undefined>
Next, set the EmptyFieldRule parameter for all the variables in the table. First, update the data types of the variables appropriately. For this example, set the data type of the fifth and seventh variables to logical and string, respectively. Then, set EmptyFieldRule for all the variables to 'auto'. The importing function imports the empty fields based on data type of the variable. Here, the readtable function imports empty fields of the logical variable as 0 and empty fields of the categorical variable as <undefined>.
VariableNames = opts.VariableNames; opts.SelectedVariableNames = VariableNames; % select all variables opts = setvartype(opts,{'Logical','String'},{'logical','string'}); opts = setvaropts(opts,VariableNames,'EmptyFieldRule','auto'); T = readtable(filename,opts)
T=10×7 table
Text Categorical Double Datetime Logical Duration String
__________ ___________ ______ __________ _______ ________ _________
{'abc' } a 1 01/14/0018 true 00:00:01 "abc"
{0×0 char} b 2 01/21/0018 false 09:00:01 "def"
{'ghi' } <undefined> 3 01/31/0018 true 02:00:01 "ghi"
{'jkl' } a NaN 02/23/2018 false 03:00:01 "jkl"
{'mno' } a 4 NaT false 04:00:01 "mno"
{'pqr' } b 5 01/23/0018 false 05:00:01 "pqr"
{'stu' } b 5 03/23/0018 false NaN "stu"
{0×0 char} a 6 03/24/2018 true 07:00:01 ""
{0×0 char} a 7 03/25/2018 true 08:00:01 <missing>
{'xyz' } <undefined> NaN NaT true 06:00:01 "xyz"
In addition to 'missing' and 'auto', you can also set the EmptyFieldRule parameter to 'error'. When you set it to 'error', the readtable function imports empty fields by following the procedure specified in the ImportErrorRule parameter.
Input Arguments
Name-Value Arguments
Version History
Introduced in R2016b
