Importing table from text file and matching header to column

Hi everyone,

Here is an example of my data:

#    r              CAC-CAC                       CAC-OAC                  
  0.2700000E+01  0.0000000E+00  0.0000000E+00  0.3860216E-03  0.2814651E-01
  0.2730000E+01  0.0000000E+00  0.0000000E+00  0.3775852E-03  0.2753156E-01
  0.2760000E+01  0.0000000E+00  0.0000000E+00  0.5541359E-02  0.1041859E+00
  0.2790000E+01  0.0000000E+00  0.0000000E+00  0.7230470E-02  0.1176749E+00
  0.2820000E+01  0.0000000E+00  0.0000000E+00  0.1096994E-01  0.1432550E+00
  0.2850000E+01  0.0000000E+00  0.0000000E+00  0.1870899E-01  0.1847078E+00

I would like to import my data into a table, with the text in the first row used as a variable for each column that it lines up with. The unwanted "#" character and the inconsistent spacing at the start of the first row means that readtable(example_data.txt); does not does not produce the correct headers for the table and instead leads to Var1, Var2,..., ect.

For example, in Excel the import data option slices up the data and the headers end up in the correct location in the imported table:

Note that some of the columns do not have headers and I do not require this data in my imported table.

I am very new to MATLAB, so any help is very appreciated!

Cheers!

Answer 1

[AndresVar]

I tried the import tool: Import data from file - MATLAB (mathworks.com)

The code it generated for your data did not look as pretty as it usually does, but seems to do the job.

Edit: noticed the import tool detected 'fixedwidth' filetype, but readtable seems to detect 'delimitedtext'. So you can specify fixedwidth in the options. The variablenamingrule option doesn't seem necessary but there is a warning without it.

opts = detectImportOptions('example_data.txt',FileType='fixedwidth',VariableNamingRule='preserve');
t1 = readtable("example_data.txt",opts)

t1 = 6×6 table
        #          r      CAC-CAC    Var4     CAC-OAC        Var6  
    __________    ____    _______    ____    __________    ________

    {0×0 char}     2.7       0        0      0.00038602    0.028147
    {0×0 char}    2.73       0        0      0.00037759    0.027532
    {0×0 char}    2.76       0        0       0.0055414     0.10419
    {0×0 char}    2.79       0        0       0.0072305     0.11767
    {0×0 char}    2.82       0        0         0.01097     0.14325
    {0×0 char}    2.85       0        0        0.018709     0.18471

t1.("#")=[] % remove the # column

t1 = 6×5 table
     r      CAC-CAC    Var4     CAC-OAC        Var6  
    ____    _______    ____    __________    ________

     2.7       0        0      0.00038602    0.028147
    2.73       0        0      0.00037759    0.027532
    2.76       0        0       0.0055414     0.10419
    2.79       0        0       0.0072305     0.11767
    2.82       0        0         0.01097     0.14325
    2.85       0        0        0.018709     0.18471

[Lucas Wong]

Thanks AndresVar! This method seems to work fine - I didn't realise the import tool was a thing. I see what you mean about the generated script being a bit messy... but it works for what I need. I would still be interested if there is a more direct method.

[AndresVar]

@Lucas Wong updated the answer, if you specify fixedwidth file type it works OK.

[Lucas Wong]

Works great! Thanks for your help.

[Lucas Wong]

@AndresVar Sorry to be a pain, but I tried this script with one of my actual data files and there is an issue with the header variables. For some reason the table headers remain as Var1, Var2,..., ect.

I have copied your code as follows:

opts = detectImportOptions('example_data2.txt',FileType='fixedwidth',VariableNamingRule='preserve');
t1 = readtable("example_data2.txt",opts);

Like I said before, the code worked beautifully for the first example data, however, not the second.

Please let me know if you have any other ideas why this may be the case. Thanks again for your help so far.

[AndresVar]

@Lucas Wong there is an extra space at the end of the first row that messes it up. BUT looks like just specificying VariableNamesLine=1 sorta solves the problem.

opts = detectImportOptions('example_data2.txt', ...
    FileType='fixedwidth',VariableNamingRule='preserve', ...
    VariableNamesLine=1 );

% optional: sanitize the variable names by removing white spaces and
% replacing unwanted characters.
opts.VariableNames = strtrim(strrep(strrep(opts.VariableNames,...
    '-','_'), '#','')); 

t1 = readtable("example_data2.txt",opts)

figure;
plot(t1.r,t1.CAC_CAC);

[Lucas Wong]

You're the man! Thanks for this.