Matlab text file opts varying for similar files

Hi, I have 2 text files with the same amount of columns/headers, when a measurement is not completed it fills in the field with an "UND" - which can be "UND. -60001" or "UND. -62011". I have a script which usually has no problems but when it does it has been very difficult to pin down the cause, I have noticed by reading the opts that it is treating the two files differently, mfile and 2 data files attached.I don't see why the files should be treated any differently, any ideas?

The file that reads in ok has this in its 'opts'.

opts = 

    DelimitedTextImportOptions with properties:

     Format Properties:
                      Delimiter: {'\t'}
                     Whitespace: '\b '
                     LineEnding: {'\n'  '\r'  '\r\n'}
                   CommentStyle: {}
      ConsecutiveDelimitersRule: 'split'
          LeadingDelimitersRule: 'keep'
                  EmptyLineRule: 'skip'
                       Encoding: 'ISO-8859-1'
  Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Nozzle_number', 'Frequency_khz', 'Velocity_ms' ... and 4 more}
                VariableTypes: {'char', 'double', 'double' ... and 4 more}
        SelectedVariableNames: {'Nozzle_number', 'Frequency_khz', 'Velocity_ms' ... and 4 more}
              VariableOptions: Show all 7 VariableOptions

Whereas the file which does not load properly has this in its opts

opts = 

    DelimitedTextImportOptions with properties:

     Format Properties:
                      Delimiter: {'\t'  ' '}
                     Whitespace: '\b'
                     LineEnding: {'\n'  '\r'  '\r\n'}
                   CommentStyle: {}
      ConsecutiveDelimitersRule: 'join'
          LeadingDelimitersRule: 'ignore'
                  EmptyLineRule: 'skip'
                       Encoding: 'ISO-8859-1'
   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'

   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'Var1', 'Var2', 'Var3' ... and 6 more}
                VariableTypes: {'char', 'double', 'char' ... and 6 more}
        SelectedVariableNames: {'Var1', 'Var2', 'Var3' ... and 6 more}
              VariableOptions: Show all 9 VariableOptions

Answer 1

[dpb]

The difference is that the second file has the UND indicator in the first data line whereas the first file has a completed record. It is that record that the options routine uses to try to parse the file and so for that file there are what appear to be nine variables in the data record but there are only six column names. That mismatch creates confusion.

In this case I would suggest to not call detectImportOptions(files(jj).name) but to use a specific hand-built options object for these files or dispense with it entirely and pass everything needed as named parameter pairs in the readtable call.

ADDENDUM

After looking at your files, I think I'd go at this somewhat differently; I'd just let readtable bring in the file as cell array, do the substitution on the bad data and convert. Is it likely there's ever a file that doesn't have at least one UND in the numeric data fields?

I don't know just what your other code after reading a file does, but I'd so that portion more nearly as:

d=dir('/Users/imagexpertinc/Desktop/odds/freq_sweeps/*.txt');
for i=1:length(d)
  t=readtable(d(i).name,opts);          % table as cellstr variables
  v=cellfun(@str2num,regexprep(table2cell(t(:,3:end)),'UND.*','NaN')); % convert the UND to NaN on cell array of all variables, convert to doubles
  for j=1:5                             % put back into existing table
    t.(j+2)=v(:,j);
  end
  ...    
  % Now do what needs done with this table here before going on to next...
end

The opts table was created from an artificial RECORD.txt file that looks like a single record:

Nozzle_number  Frequency_khz  Velocity_ms  Volume_pl  Trajectory_deg  X_coordinate_mm  Y_coordinate_mm
-  4  UND. -60001  UND. -62011  UND. -60001  UND. -2011  UND. -2011

so the variables would all be recognized and imported as text; this makes the conversion performed the same on every column for every file whereas if there were a given file in which a specific variable was ok for every observation, by default that would be imported as numeric and logic would have to be written to handle it.

Unless, of course, the substituted missing value itself has significance for some reason; then would need to convert it, but your solution seems to not discern that difference, either.

[Stephen Devlin]

Thank you DB

[dpb]

I don't suppose it's possible to change the file definition owing to either inability to actually effect a change or for backwards compatibility with existing files, but to define the missing values as simply the numeric value would regularize the format of the file. Although if the built-in missing value indicator is working as intended then there probably is no overriding reason to change.

Does the working version treat the UND records as desired or is it ignoring them entirely (or is that what is intended)?

[Stephen Devlin]

Hi dpb, it works for most of the problems now, but still has an issue with some text files (have added BBBB.txt). What it does on most files is it converts each UND... to a NaN and then all the data in the table is double, at the moment it is handling some of the UNDs as text and then I have cell columns and double columns, what is odd is that I cannot see a difference between these files, they all came off the same machine using the same data format.

[dpb]

One way if the files aren't terribly humonqous would be to read full file into memory as stream and do character substitution to remove the UND string leaving just the aforementioned MV as numeric.

That should regularize all of them for consistency in parsing; if something still shows up, then there's another difference besides you've not noticed so far.

[Stephen Devlin]

Hi dpb,

I will try the idea in the addendum, I used to use the readable proper TreatAsEmpty and had a cell array of all the known error UND messages and was advised to try opts when that produced this same issue, but your approach is different and I will give this ago. Very much appreciated.

Best regards

Steve

[dpb]

The big thing is to get the file format to be consistent before trying to convert; as it is now, each file may have a different column recognized as numeric/text by the self-formatting routines either via the specific use of detectImportOptions or the internal self-formatting routines that are essentially the same thing except behind the scenes--as your spelunking discovered, depending on the characteristics of the first data record the inferred format of the file changes which makes the difficulty in subsequently being able to write reliable routines to read it--fixing one issue in one case may well cause difficulty in another because the location of which column is detected as which variable type is more-or-less random.

[Stephen Devlin]

I have an idea, but it may be crazy ;-) I am going to combine the two approaches, first I will read the file in and add a row of integers to the table, and then save it. Then open it back up, let detect options do its thing and then remove the row I have added from any calculations on the data. No idea if it will work but should know tomorrow if I have time to finish it off.

[Stephen Devlin]

Ive added the row so that it is the first row detectimportoptions sees.

[dpb]

Hmmm...you could achieve the same effect more easily by creating and using a fixed import options object excepting using the alternate variable encoding.

Turns out that it appears (somewhat to my surprise) that that actually works cleanly; while creating the opts file on the fly like that is excessively complex, using the opts file with all variables defined to be numeric except for the first actually seems to work to replace the non-convertible fields with NaN. If this proves true on the various previous problem files, it's by far the better implementation.

Some testing shows that it does, however, take a full-blown 'opts' object to set all the myriad of options; trying to use just a minimal number of named parameters fails miserably. Possibly one could eventually figure out how to set enough parameters to make that work but I'm not absolutely positive one has sufficient control that way and it is surely far more effort that just munging a little on the self-derived object.