Dealing with Duration format

I have a file containing the values of the position of the body's center of pressure, calculated during the trial. I would like to plot the time elapsed (which has the format '0:0:0:064') vs the position. When I open the file (.xlsx) with "readtable", it reads me all the values as "string" and not numbers. I tried to solve this with "str2double", and while this works for the position column, is useless for the time elapsed. When I change the extension (i.e. .csv orb .txt) it converts all the durations into NaNs, even though in the "Import Data" mask it is written at the top of the time elapsed column "duration". As a result, I cannot plot the time on the x-axis.

If I run this piece of code for example:

data = readtable("AdaptationTestProva.xlsx");

x = str2double(data.x_StaticVR_RawMediolateralAxis);

y = str2double(data.TimeElapsed);

plot(y,x)

I get an empty graph.

What can I do to get a standard graph with a usable elapsed time?

If this can be helpful, I'll leave attached an example of the file.

[dpb]

"the time elapsed (which has the format '0:0:0:064') "

How is this supposed to be interpreted? It uses the colon as a field separator for what appears to be fractional seconds.

It is an invalid duration format that can't be parsed as written if it is intended to represent hh:mm:ss.SSS.

You would have to read as a string field and do an internal conversion to replace the last colon with a period in order to parse the field as a MATLAB duration.

The better solution would be to fix the broken code that created the bad formatting for any future use. Perhaps the simplest here would be to open the Excel file and do a character substitution there.

Answer 1

[Steven Lord]

Use detectImportOptions to see how MATLAB would decide to import the data by default based on the contents of the file. Once you have the options object, you can change properties of the import options object to change how MATLAB imports the data.

opts = detectImportOptions("patients.xls")

opts = 
  SpreadsheetImportOptions with properties:

   Sheet Properties:
                        Sheet: ''

   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
         MergedCellColumnRule: 'placeleft'
            MergedCellRowRule: 'placetop'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
                VariableTypes: {'char', 'char', 'double' ... and 7 more}
        SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more}
              VariableOptions: [1-by-10 matlab.io.VariableImportOptions] 
	Access VariableOptions sub-properties using setvaropts/getvaropts
           VariableNamingRule: 'modify'

   Range Properties:
                    DataRange: 'A2' (Start Cell)
           VariableNamesRange: 'A1'
                RowNamesRange: ''
           VariableUnitsRange: ''
    VariableDescriptionsRange: '' 
	To display a preview of the table, use preview

For example, let's say you wanted to read in this spreadsheet but have Gender specified as a categorical array rather than a char vector. Since it's the second variable in the data, use setvartype with the options object, the variable number 2, and the desired type as inputs.

opts = setvartype(opts, 2, 'categorical');

Now when we import the data, the Gender variable is a categorical array.

data = readtable("patients.xls", opts);
head(data) % Show just the first few rows

      LastName      Gender    Age              Location               Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    ____________    ______    ___    _____________________________    ______    ______    ______    ________    _________    ________________________

    {'Smith'   }    Male      38     {'County General Hospital'  }      71       176      true        124          93             {'Excellent'}      
    {'Johnson' }    Male      43     {'VA Hospital'              }      69       163      false       109          77             {'Fair'     }      
    {'Williams'}    Female    38     {'St. Mary's Medical Center'}      64       131      false       125          83             {'Good'     }      
    {'Jones'   }    Female    40     {'VA Hospital'              }      67       133      false       117          75             {'Fair'     }      
    {'Brown'   }    Female    49     {'County General Hospital'  }      64       119      false       122          80             {'Good'     }      
    {'Davis'   }    Female    46     {'St. Mary's Medical Center'}      68       142      false       121          70             {'Good'     }      
    {'Miller'  }    Female    33     {'VA Hospital'              }      64       142      true        130          88             {'Good'     }      
    {'Wilson'  }    Male      40     {'VA Hospital'              }      68       180      false       115          82             {'Good'     }      

class(data.Gender)

ans = 'categorical'

If I'd used the default options, MATLAB would have read Gender as text data (a cell array of char vectors) rather than categorical.

data2 = readtable("patients.xls");
head(data2)

      LastName        Gender      Age              Location               Height    Weight    Smoker    Systolic    Diastolic    SelfAssessedHealthStatus
    ____________    __________    ___    _____________________________    ______    ______    ______    ________    _________    ________________________

    {'Smith'   }    {'Male'  }    38     {'County General Hospital'  }      71       176      true        124          93             {'Excellent'}      
    {'Johnson' }    {'Male'  }    43     {'VA Hospital'              }      69       163      false       109          77             {'Fair'     }      
    {'Williams'}    {'Female'}    38     {'St. Mary's Medical Center'}      64       131      false       125          83             {'Good'     }      
    {'Jones'   }    {'Female'}    40     {'VA Hospital'              }      67       133      false       117          75             {'Fair'     }      
    {'Brown'   }    {'Female'}    49     {'County General Hospital'  }      64       119      false       122          80             {'Good'     }      
    {'Davis'   }    {'Female'}    46     {'St. Mary's Medical Center'}      68       142      false       121          70             {'Good'     }      
    {'Miller'  }    {'Female'}    33     {'VA Hospital'              }      64       142      true        130          88             {'Good'     }      
    {'Wilson'  }    {'Male'  }    40     {'VA Hospital'              }      68       180      false       115          82             {'Good'     }      

class(data2.Gender)

ans = 'cell'

The VariableOptions can give you additional control over how certain inputs are handled. For example, if you had a column in your data that had a fixed set of values and those values had a specified order ('small', 'medium', 'large') you could change the Categories and Ordinal properties of the object to make MATLAB respect that fixed set and the ordering rather than defining the categories in alphabetical order ('large', 'medium', 'small').

optionsForLastName = getvaropts(opts, 1)

optionsForLastName = 
  TextVariableImportOptions with properties:

   Variable Properties:
              Name: 'LastName'
              Type: 'char'
         FillValue: ''
    TreatAsMissing: {}
         QuoteRule: 'remove'
          Prefixes: {}
          Suffixes: {}
    EmptyFieldRule: 'missing'

   String Options:
    WhitespaceRule: 'trim'

optionsForGender = getvaropts(opts, 2)

optionsForGender = 
  CategoricalVariableImportOptions with properties:

   Variable Properties:
              Name: 'Gender'
              Type: 'categorical'
         FillValue: <undefined>
    TreatAsMissing: {}
         QuoteRule: 'remove'
          Prefixes: {}
          Suffixes: {}
    EmptyFieldRule: 'missing'

   Categorical Options:
        Categories: {}
         Protected: 0
           Ordinal: 0

[dpb]

Unfortunately, Steven, there isn't enough flexibility in the input formatting allowed for a duration to handle the malformed case of the OP's data -- I tried to override but one gets

d=dir('*.csv');
opt=detectImportOptions(d.name);
getvaropts(opt,'TimeElapsed')

ans = 
  DurationVariableImportOptions with properties:

   Variable Properties:
                Name: 'TimeElapsed'
                Type: 'duration'
           FillValue: NaN sec
      TreatAsMissing: {}
           QuoteRule: 'remove'
            Prefixes: {}
            Suffixes: {}
      EmptyFieldRule: 'missing'

   Duration Options:
      DurationFormat: 'default'
         InputFormat: ''
    DecimalSeparator: '.'
      FieldSeparator: ':'

opt=setvaropts(opt,'TimeElapsed','InputFormat','hh:mm:ss.SSS','DecimalSeparator',':');
tData=readtable(d.name,opt);

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.

head(tData)

    TimeElapsed    x_StaticVR_RawMediolateralAxis    x_StaticVR_RawAnterioposteriorAxis    x_StaticVR_SmoothedMediolateralAxis    x_StaticVR_SmoothedAnterioposteriorAxis    x_MotionVR_Pitch    x_MotionVR_Roll    x_MotionVR_Heave     Column9  
    ___________    ______________________________    __________________________________    ___________________________________    _______________________________________    ________________    _______________    ________________    __________

        NaN                  0.0083129                            -0.32387                              0.069267                                 -0.38223                           0                   0                  0            {0×0 char}
        NaN                  0.0084821                            -0.32242                              0.063135                                 -0.37889                           0                   0                  0            {0×0 char}
        NaN                  0.0084821                            -0.32242                              0.060928                                 -0.37659                           0                   0                  0            {0×0 char}
        NaN                  0.0092612                             -0.3183                               0.05845                                 -0.37413                           0                   0                  0            {0×0 char}
        NaN                  0.0087869                            -0.31415                              0.055607                                 -0.37141                           0                   0                  0            {0×0 char}
        NaN                  0.0075698                            -0.31103                               0.05236                                  -0.3685                           0                   0                  0            {0×0 char}
        NaN                  0.0062145                             -0.3084                              0.050079                                 -0.36577                           0                   0                  0            {0×0 char}
        NaN                  0.0068518                            -0.30291                              0.047467                                 -0.36278                           0                   0                  0            {0×0 char}

getvaropts(opt,'TimeElapsed')

ans = 
  DurationVariableImportOptions with properties:

   Variable Properties:
                Name: 'TimeElapsed'
                Type: 'duration'
           FillValue: NaN sec
      TreatAsMissing: {}
           QuoteRule: 'remove'
            Prefixes: {}
            Suffixes: {}
      EmptyFieldRule: 'missing'

   Duration Options:
      DurationFormat: 'default'
         InputFormat: 'hh:mm:ss.SSS'
    DecimalSeparator: ':'
      FieldSeparator: ':'

It still can't parse the duration...if one tries to set the colon in the format string in case it's looking to make the substitution from the literal string, then

opt=setvaropts(opt,'TimeElapsed','InputFormat','hh:mm:ss:SSS','DecimalSeparator',':');

Error using matlab.io.ImportOptions/setvaropts (line 12)
Unsupported format 'hh:mm:ss:SSS'. See the documentation of 'InputFormat' for valid formats.

An approach such as @Stephen23 took is the only way one will be able to import this particular (malformed, granted) file.

BTW, simply trying to read with cellmat wasn't successful either, at least without mucking around. I figured that would not have any issues in returning the first column as a cellstr but it also fails...even it wants to import it as a duration.

Answer 2

[Stephen23]

F = 'AdaptationTestProva.csv';
H = readtable(F, 'Delimiter',',', 'Range','1:2');

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.

T = readtable(F, 'Delimiter',{',',':'}, 'HeaderLines',1, 'MissingRule','omitrow');
T = renamevars(T, 5:11, H.Properties.VariableNames(2:8))

T = 6687×11 table
    Var1    Var2    Var3    Var4    x_StaticVR_RawMediolateralAxis    x_StaticVR_RawAnterioposteriorAxis    x_StaticVR_SmoothedMediolateralAxis    x_StaticVR_SmoothedAnterioposteriorAxis    x_MotionVR_Pitch    x_MotionVR_Roll    x_MotionVR_Heave
    ____    ____    ____    ____    ______________________________    __________________________________    ___________________________________    _______________________________________    ________________    _______________    ________________

     0       0       0       64                0.0083129                           -0.32387                              0.069267                                 -0.38223                           0                   0                  0        
     0       0       0       69                0.0084821                           -0.32242                              0.063135                                 -0.37889                           0                   0                  0        
     0       0       0       71                0.0084821                           -0.32242                              0.060928                                 -0.37659                           0                   0                  0        
     0       0       0       86                0.0092612                            -0.3183                               0.05845                                 -0.37413                           0                   0                  0        
     0       0       0      101                0.0087869                           -0.31415                              0.055607                                 -0.37141                           0                   0                  0        
     0       0       0      116                0.0075698                           -0.31103                               0.05236                                  -0.3685                           0                   0                  0        
     0       0       0      132                0.0062145                            -0.3084                              0.050079                                 -0.36577                           0                   0                  0        
     0       0       0      148                0.0068518                           -0.30291                              0.047467                                 -0.36278                           0                   0                  0        
     0       0       0      163                0.0057561                            -0.3011                              0.044898                                 -0.35979                           0                   0                  0        
     0       0       0      178                0.0037245                           -0.29451                              0.042346                                  -0.3566                           0                   0                  0        
     0       0       0      195                0.0028524                            -0.2934                              0.039851                                 -0.35346                           0                   0                  0        
     0       0       0      211               0.00042633                           -0.29119                              0.037342                                  -0.3503                           0                   0                  0        
     0       0       0      228               -0.0041243                           -0.28644                              0.035215                                 -0.34769                           0                   0                  0        
     0       0       0      245               -0.0060033                           -0.28237                              0.032185                                 -0.34369                           0                   0                  0        
     0       0       0      261               -0.0064748                            -0.2806                              0.029692                                 -0.34036                           0                   0                  0        
     0       0       0      278               -0.0089898                            -0.2778                              0.027194                                 -0.33702                           0                   0                  0        

D = duration(T.Var1,T.Var2,T.Var3,T.Var4); % check the units
plot(D, T.x_StaticVR_RawMediolateralAxis)