Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

setvaropts

Set variable import options

Syntax

opts = setvaropts(opts,Name,Value)
opts = setvaropts(opts,selection,Name,Value)

Description

opts = setvaropts(opts,Name,Value) updates all the variables in the opts object based on the specifications in the Name,Value arguments.

example

opts = setvaropts(opts,selection,Name,Value) updates and returns an opts object for the variables specified in the selection argument, based on the specifications in the Name,Value arguments.

Examples

collapse all

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)
Variables:

    Smoker: 100x1 logical

        Values:

            True     34      
            False    66      

    Diastolic: 100x1 double

        Values:

            Min         68       
            Median    81.5       
            Max         99       

    Systolic: 100x1 double

        Values:

            Min       109       
            Median    122       
            Max       138       

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, use readtable to import the data. Note that the dataset airlinesmall.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.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.csv',opts);

Examine the values in ArrDelay and DepDelay. Verify that the importing function replaced the missing values denoted by NA.

T(166:180,:)
ans=15x2 table
    ArrDelay    DepDelay
    ________    ________

     -1           0     
    102         105     
    -11           0     
      0           0     
     -1           0     
      0           0     
      1           0     
      1           0     
     14           0     
    -14           0     
      5           0     
     25          38     
      0          -5     
      0           0     
      6           0     

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'

   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 = 10x1 string array
    "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'

   Numeric Options:
     ExponentCharacter: 'eEdD'
      DecimalSeparator: '.'
    ThousandsSeparator: ''

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)
Variables:

    Cancelled: 1338x1 logical

        Values:

            True       29       
            False    1309       

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 = 
  1x2 DatetimeVariableImportOptions array with properties:

    DatetimeFormat
    DatetimeLocale
    InputFormat
    Type
    FillValue
    TimeZone
    Name
    QuoteRule
    TreatAsMissing

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=10x2 table null
         OutageTime           RestorationTime   
    ____________________    ____________________

    01-Feb-2002 12:18:00    07-Feb-2002 16:50:00
    23-Jan-2003 00:49:00    01-Sep-2017 16:19:12
    07-Feb-2003 21:15:00    17-Feb-2003 08:14:00
    06-Apr-2004 05:44:00    06-Apr-2004 06:10:00
    16-Mar-2002 06:18:00    18-Mar-2002 23:23:00
    18-Jun-2003 02:49:00    18-Jun-2003 10:54:00
    20-Jun-2004 14:39:00    20-Jun-2004 19:16:00
    06-Jun-2002 19:28:00    07-Jun-2002 00:51:00
    16-Jul-2003 16:23:00    17-Jul-2003 01:12:00
    27-Sep-2004 11:09:00    27-Sep-2004 16:37:00

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 variables Region and Cause.

getvaropts(opts,{'Region','Cause'})
ans = 
  1x2 TextVariableImportOptions array with properties:

    WhitespaceRule
    Type
    FillValue
    Name
    QuoteRule
    TreatAsMissing

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)
Variables:

    Region: 1468x1 categorical

        Values:

            MidWest      142     
            NorthEast    557     
            SouthEast    389     
            SouthWest     26     
            West         354     

    Cause: 1468x1 categorical

        Values:

            Miscellaneous        24    
            attack              294    
            earthquake            2    
            energy emergency    188    
            equipment fault     156    
            fire                 25    
            severe storm        338    
            thunder storm       201    
            wind                 95    
            winter storm        145    

Input Arguments

collapse all

File import options, specified as a SpreadsheetImportOptions, DelimitedTextImportOptions, or a FixedWidthImportOptions object created by the detectImportOptions function. The opts object contains properties that control the data import process, such as variable properties, data location properties, replacement rules, and others.

Selected variables, specified as a character vector, cell array of character vectors, or an array of numeric indices.

Variable names (or indices) must be a subset of the names contained in the VariableNames property of the opts object.

Example: 'Height'

Example: {'Height','LastName'}

Example: [5 9]

Data Types: char | cell | uint64

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: opts = setvaropts(opts,'Weight','FillValue',0) sets the FillValue for the variable Weight to 0.

Common Options

collapse all

Replacement value for missing data, depending on the type of the variable, specified as a character vector, a scalar numeric, or a logical value true or false.

Type of VariableDescription
Text

Character vector containing the replacement text

Example: 'not applicable'

Numeric

Scalar numeric to replace missing instances

The importing function converts the input for FillValue to the data type specified by the Type property of the variable. For example, if Type property value is uint8, then the importing function also converts the value of the FillValue property to uint8.

Example: 0

Logical

Character vector containing true or false.

Example: false

Datetime

Character vector or a scalar value representing date and time data. For more information on valid datetime inputs, see the datetime function page.

Example: 'now' sets the missing datetime instances to the current date and time.

Example: [1998 12 1] sets the missing datetime instances to the date December 1st, 1998.

Categorical

Character vector containing the name to use for the replacement category.

Example: 'Miscellaneous' assigns the category name Miscellaneous to missing instances in the categorical data.

To direct the import of data that is missing, unconvertible, or that causes errors, use these four properties together: FillValue, TreatAsMissing, MissingRule, and ErrorRule. The importing function uses the value specified in the FillValue property when:

  • Data is unconvertible or matches a value in TreatAsMissing.

  • MissingRule or the ErrorRule is set to fill.

Text to interpret as missing data, specified as a character vector or a cell array of character vectors.

When the importing function finds missing instances, it uses the specification in the MissingRule property to determine the appropriate action.

Example: 'TreatAsMissing',{'NA','TBD'} instructs the importing function to treat any occurrence of NA or TBD as a missing fields.

Data Types: char | cell

Procedure to manage double quotation marks in the data, specified as one of the values in this table.

Quote RuleProcess
'remove'

If double quotes (") surround characters, then the importing function removes both the opening double quote and the next occurring double quote, which would be interpreted as the closing double quote.

Example: "500" is imported as 500.

If two sets of double quotes ("") surround characters, then the importing function removes the first two occurrences.

Example: ""abc"" is imported as abc"".

If a pair of opening and closing quotes surrounding characters is followed by a single lone unpaired double quotes, then the importing function ignores the lone unpaired double quote.

Example: "abc"" is imported as abc".

'keep'

Retain all quotation marks.

'error'Report an error when converting data which begins with a double quotation mark ("). Use this setting if the field should never be quoted.

Example: If the 'QuoteRule' is set to 'remove', then the importing function imports "abc"def" as abcdef".

Text Only

collapse all

Procedure to manage leading and trailing white spaces when importing text data, specified as one of the values in the table.

White Space RuleProcess
'trim'

Remove any leading or trailing white spaces from the text. Interior white space is unaffected.

Example: ' World Time ' is imported as 'World Time'

'trimleading'

Remove only the leading white spaces.

Example: ' World Time ' is imported as 'World Time '

'trimtrailing'

Remove only the trailing white spaces.

Example: ' World Time ' is imported as 'World Time'

'preserve'

Preserve white spaces.

Example: ' World Time ' is imported as ' World Time '

Numeric Only

collapse all

Characters indicating the exponent, specified as a character vector. The importing function uses the ExponentCharacter property to recognize the characters indicating the exponent for a number expressed in the scientific notation.

Example: If varOpts.ExponentCharacter = 'a', then the importing function imports the text "1.2a3" as the number 1200.

Characters indicating the decimal separator, specified as a character vector. The importing function uses the DecimalSeparator property to distinguish the integer part of a number from the decimal part.

When converting to integer data types, numbers with a decimal part are rounded to the nearest integer.

Example: If varOpts.DecimalSeparator = ',', then the importing function imports the text "3,14159" as the number 3.14159.

Characters that indicate the thousands grouping, specified as a character vector. The thousands grouping characters act as visual separators, grouping the number at every three place values. The importing function uses the characters in the ThousandsSeparator property to interpret the numbers being imported.

Example: If varOpts.ThousandsSeparator = ',', then the importing function imports the text "1,234,000" as 1234000.

Logical Only

collapse all

Text to treat as the logical value true, specified as a character vector or a cell array of character vectors.

Example: If varOpts.TrueSymbols = {'t','TRUE'}, then the importing function imports any fields containing t or TRUE as the logical value true.

Data Types: char | cell

Text to treat as the logical value false, specified as a character vector or a cell array of character vectors.

Example: If varOpts.FalseSymbols = {'f','FALSE'}, then the importing function imports any fields containing f or FALSE as the logical value false.

Data Types: char | cell

Indicator to match case, specified as a logical value true or false.

To interpret the input data as missing, true, or false, the importing function matches the data to values specified in TreatAsMissing, TrueSymbols, and FalseSymbols.

Datetime Only

collapse all

Display format, specified as a character vector. The DatetimeFormat property controls the display format of dates and times in the output. Specify DatetimeFormat as one of these values.

Value of DatetimeFormatDescription

'default'

Use the default display format.

'defaultdate'

Use the default display format for datetime values created without time components.

'preserveinput'

Use the format specified by the input format, InputFormat.

Custom formats

Use the letters A-Z and a-z to construct a custom value for DatetimeFormat. These letters correspond to the Unicode® Locale Data Markup Language (LDML) standard for dates. You can include non-ASCII or nonletter characters such as a hyphen, space, or colon to separate the fields. To include the letters A-Z and a-z as literal characters in the format, enclose them with single quotes.

The factory default format depends on your system locale. To change the default display format, see Default datetime Format.

Format of the input text representing dates and times, specified as a character vector that contains letter identifiers.

This table shows several common input formats and examples of the formatted input for the date, Saturday, April 19, 2014 at 9:41:06 PM in New York City.

Value of InputFormatExample
'yyyy-MM-dd'2014-04-19
'dd/MM/yyyy'19/04/2014
'dd.MM.yyyy'19.04.2014
'yyyy年 MM月 dd日'2014年 04月 19日
'MMMM d, yyyy'April 19, 2014
'eeee, MMMM d, yyyy h:mm a'Saturday, April 19, 2014 9:41 PM
'MMMM d, yyyy HH:mm:ss Z'April 19, 2014 21:41:06 -0400
'yyyy-MM-dd''T''HH:mmXXX' 2014-04-19T21:41-04:00

For a complete list of valid letter identifiers, see the Format property for datetime arrays.

Example: 'InputFormat','eeee, MMMM d, yyyy HH:mm:ss'

Locale to use for interpreting dates, specified as a character vector. The DatetimeLocale value determines how the importing function interprets text that represents dates and times.

Set the DatetimeLocale value to:

  • 'system', to specify your system locale.

  • or a character vector in the form xx_YY, where xx is a lowercase ISO 639-1 two-letter code that specifies a language, and YY is an uppercase ISO 3166-1 alpha-2 code that specifies a country.

This table lists some common values for the locale.

Locale LanguageCountry
'de_DE'GermanGermany
'en_GB'EnglishUnited Kingdom
'en_US'EnglishUnited States
'es_ES'SpanishSpain
'fr_FR'FrenchFrance
'it_IT'ItalianItaly
'ja_JP'JapaneseJapan
'ko_KR'KoreanKorea
'nl_NL'DutchNetherlands
'zh_CN'Chinese (simplified)China

Example: varOpts.DatetimeLocale = 'de_DE' sets the date time locale to German.

Note

The Locale value determines how input values are interpreted. The display format and language is specified by the Locale option in the Datetime format section of the Preferences panel. To change the default datetime locale, see Set Command Window Preferences.

Categorical Only

collapse all

Expected categories, specified as a cell array of character vectors, containing a list of category names.

Names in the input fields must match one of the names specified in the Categories property to avoid a conversion error.

Example: varOpts.Categories = {'BareLand','Forest','Water','Roads','Buildings'};

Category protection indicator, specified as either false, true, 0, or 1. The categories of ordinal categorical arrays are always protected. If the Ordinal property is set to true, then the default value for Protected is true. Otherwise, the value for Protected is false. For more information on categorical arrays, see the categorical function reference page.

Mathematical ordering indicator, specified as either false, true, 0, or 1. For more information on categorical arrays, see the categorical function reference page.

Introduced in R2016b

Was this topic helpful?