How can I remove quotes from a table?

Hi,

I have a large csv file which I import to MATLAB by an user-prompt and store the information in a table. After storing the information in the table, I see that each value in the table gets appended inside a single quotation mark. How to remove this quotation mark from each element or more specifically, how can I avoid storing the information from the csv file without appending a quotation mark with every element? Screenshot & code attached.

BaseDir = 'C:\Users\mhaque7\Documents\MATLAB';  % Set as you need
[FileName, FilePath] = uigetfile('*.csv', ...
                  'Please choose an Excel file', BaseDir);
if isequal(FileName, 0)
   disp('User aborted file choosing.');
   return;  % Assuming this is a function
end
File = fullfile(FilePath, FileName);
data_src=readtable(File) %importing data from the csv file into a table

Answer 1

[dpb]

Because you used default conditions to readtable and didn't give it enough help to parse the data correctly; hence it read it all as cellstr--what the quotes are showing. They're not part of the data; they're just a visual cue ML uses to inform of what the data types are.

I built a sample of your file and read it as follows:

>> opt=detectImportOptions('haque.csv')
opt = 
  DelimitedTextImportOptions with properties:
   Format Properties:
                    Delimiter: {','}
   ...
   Variable Import Properties:   Set types by name using setvartype
                VariableNames: {'Time', 'x_DB_SEC', 'x_DB_USEC' ... and 1 more}
                VariableTypes: {'double', 'double', 'double' ... and 1 more}
        SelectedVariableNames: {'Time', 'x_DB_SEC', 'x_DB_USEC' ... and 1 more}
              VariableOptions: Show all 4 VariableOptions 
  Access VariableOptions sub-properties using setvaropts/getvaropts
   Location Properties:
                     DataLine: 3
            VariableNamesLine: 1
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0
opt.VariableOptions = 

      1x4 VariableImportOptions array with properties:

     Variable Options:
                         (1) |        (2) |         (3) |       (4)  
              Name:   'Time' | 'x_DB_SEC' | 'x_DB_USEC' | 'HRSTEST'
              Type: 'double' |   'double' |    'double' |  'double'
         FillValue:      NaN |        NaN |         NaN |       NaN
    TreatAsMissing:       {} |         {} |          {} |        {}
         QuoteRule: 'remove' |   'remove' |    'remove' |  'remove'

NB: the scan routine did figure out the variable names are in record 1 and the data starts at 3 and so recognized it as numeric. That's a big start. It did not, however, assign the second row to be the variable units. We can fix that pretty easily, however--

>> opt.VariableUnitsLine=2;               % use 2nd record for units
>> t=readtable('haque.csv',opt)           % _now_ we can read with this helper object
t =
  3×4 table
    Time     x_DB_SEC    x_DB_USEC    HRSTEST
    _____    ________    _________    _______
        0    0              0         0      
    0.001    0           1000         0      
    0.002    0           2000         0      
>> t.Properties.VariableUnits
ans =
  1×4 cell array
    {'sec'}    {'sec'}    {'usec'}    {'hour'}
>>

and Voila! with the extra help you have both the units available and the numeric values as native doubles.

[Mohammad Haque]

My actual dataset contains more than 4 columns. The snippet was part of a large dataset containing near about 213 columns. How can I workout that situation? Or in some cases, my dataset would contain more columns & I may not know the names of all the column headers.

[dpb]

Totally generic solution; all that's needed is the format for the file is regular.

Did you try it????

[Mohammad Haque]

What's the final executable code to run in MATLAB?

[dpb]

The lines above with the command prompt...I just echoed the output to show what was being done...

opt=detectImportOptions('haque.csv');  % scan, build opt object
opt.VariableUnitsLine=2;               % use 2nd record for units
t=readtable('haque.csv',opt);          % read using the object

NB: If you have so many columns, depending upon the data structure you might not want to use a table with a variable for every column but some variables to be handled as arrays -- say there is a 50-column response variable. We don't have sufficient background to know but something to consider in choosing how to read the file. This solution handles the Q? raised as posed, not whether it's the best choice to use table this way for the specific problem.

[Mohammad Haque]

Sorry for the late response, I tried to execute the below code but it's throwing me an error: Undefined function or variable 'detectImportOptions'. Seems like there is no such function built-in.

BaseDir = 'C:\Users\Imtiaz\Documents\MATLAB';  % Set as you need
[FileName, FilePath] = uigetfile('*.csv', ...
                  'Please choose an Excel file', BaseDir);
if isequal(FileName, 0)
   disp('User aborted file choosing.');
   return;  % Assuming this is a function
end
File = fullfile(FilePath, FileName);
opt=detectImportOptions(File)
data_src=readtable(File); %importing data from the csv file into a table

[dpb]

What release of ML are you using? (What does 'version' at the command line return?)

I thought it was introduced same time as table and readtable but perhaps not until somewhat later...well, let's see, new doc should help, actually--

doc detectImportOptions
  ...
Introduced in R2016b

So, if you're using release earlier than R2016b, I'd suggest an upgrade if at all possible would be easiest fix for this.

The difficulty is that readtable doesn't have the facility to tell it to read the 2nd line of text as units; you can 'ReadVariableNames' and skip 'HeaderLines' but only the import options object has the extra parameters...

If you can't upgrade, best you can do is fake what readtable does with the options object...

fid=fopen('haque.csv','r');
l=fgetl(fid);
vars=textscan(l,'%s','delimiter',',');
l=fgetl(fid);
units=textscan(l,'%s','delimiter',',');
data=textscan(fid,'','delimiter',',');
t=table(data{:},'VariableNames',vars{:});
t.Properties.VariableUnits=units{:};

The end result of all this is--

>> t
  t =
    3×4 table
      Time     x_DB_SEC    x_DB_USEC    HRSTEST
      _____    ________    _________    _______
          0    0              0         0      
      0.001    0           1000         0      
      0.002    0           2000         0      
>> t.Properties.VariableUnits
ans =
  1×4 cell array
    {'sec'}    {'sec'}    {'usec'}    {'hour'}
>>

[Mohammad Haque]

The command "detectImportOptions" worked fine in my workstation where I use MATLAB R2018a whereas in my personal laptop, I use R2016a probably that's why this command didn't work. I appreciate your help and the time & efforts you made to give a fruitful solution to my problem. Thanks dpb. Have a blast.

[dpb]

Ayup, you're one release too early...

[Mohammad Haque]

I am having another issue now. My data import function works. Now when I try to plot my data on a same x-axis(time axis) with multiple y-axes, I am getting error. I can plot different columns on the same plot with just one y-axis. Also, is there way a to make the x-axis adjust the limit dynamically as in it will adjust the limit from the source file? below is the code:

clear all;
close all;
  BaseDir = 'C:\Users\mhaque7\Documents\MATLAB';  % Set as you need : C:\Users\mhaque7\Documents\MATLAB
  [FileName, FilePath] = uigetfile('*.csv', ...
                    'Please choose an Excel file', BaseDir);
  if isequal(FileName, 0)
     disp('User aborted file choosing.');
     return;  % Assuming this is a function
  end
  File = fullfile(FilePath, FileName);
  opt=detectImportOptions(FileName);
  % opt.VariableUnitsLine=2;                % use 2nd record for units
  t=readtable(FileName,opt);              % read using the object
  t.Properties.VariableUnits;             % read with this helper object
  % plotyyy(t.x_TIME, t.D2TQSH, t.x_TIME, t.TMPSUMP, t.x_TIME, t.D2SPDFR)
  %%%tried to plot using 3 y-axis, didn't work 

    plot(t.x_TIME,t.D2TQSH,t.x_TIME,t.D1TQSH,t.x_TIME,t.D3TQSH,t.x_TIME,t.SPDOUTL,t.x_TIME,t.SPDOUTR,t.x_TIME,t.TMPSUMP,t.x_TIME,t.VIB1)
    %%%This one works fine but with just one y-axis, I have to plot with at
    %%%least 6 y-axes

    xlabel('Total Time (in seconds)')
    grid on
    grid minor
    set(gca, 'FontName', 'Purisa' , 'FontSize', 14)
    xlim([0 65000]) %%Is there a way to make the x-axis adjust the scale dynamically?

[dpb]

% plotyyy(t.x_TIME, t.D2TQSH, t.x_TIME, t.TMPSUMP, t.x_TIME, t.D2SPDFR)
%%%tried to plot using 3 y-axis, didn't work 

>> which plotyyy
'plotyyy' not found.
>>

There is no builtin 3-yaxis plot function; there may be something on FileExchange that does more than the two.

BTW, plotyy has been deprecated; use yyaxis instead for new code.

" I have to plot with at %%%least 6 y-axes"

That's going to get awfully crowded it would seem; you positive you can't put multiple y-var's on only one or two actual axes?

If you can't find something on FEX, you'll have to "roll your own"...it's not that difficult; basically you retrieve the 'position' property of the first axes and plop a second on top of it for two...

ax(1)=axes;
% plot into it here...
...
ax(2)= axes('Position',get(ax(1),'Position'), ...
            'Parent',get(ax(1),'Parent'));
set(ax(2),'YAxisLocation','right','Color','none', ...
          'XGrid','off','YGrid','off','Box','off');
% plot into second here
...

is the shorthand version.

Now, to do more axes you've got quite a lot more work to do; you've got to keep reducing the 'width' position to make room for the addtional ticks/labels and if put more than one on LH and RH, then have to adjust the 'left' position as well.

But, it's at least theoretically possible...

As for the x-axis limits; it will expand for the range on a given axis; if you have competing axes, then you need to compute the overall range of all them and then probably linkaxes to keep them all in synch. If they are on different xlim ranges and/or tick spacing then you'll have conflicting tick locations.

[Mohammad Haque]

Thanks for your feedback. I guess I have to use 'subplot' function then instead of crowding the plot with multiple y-axes. In fact it would be more understandable to plot individual variables on a separate plot and with their respective values on a single y-axis. For comparison, I could probably select two variables on the same x-axis and two separate y-axes. For the x-axis values however, my concern is to see the values in the non-scientific form. Right now, the x-axis scale appears to be in exponent form of 10 such as 2X10^4. Which is why I specifically set the x-axis limit as the whole number since I have looked at the raw file. I just wanted to avoid it without opening the raw file & let MATLAB plot show me in a whole number form if that makes any sense. By the way, somewhere on the forum, I saw someone mention a toolbox called 'plt' in MATLAB which enables you to select multiple channels on the plot but I couldn't find any. Is there such a plotting toolbox in MATLAB which allows you to view multiple channels on the same x axis?

[dpb]

hAx=gca;
hAx.XAxis.Exponent=0;

I don't know what you envision by " view multiple channels on the same x axis". You can plot as many lines on a given axis as you wish.

There are many plotting submissions on FEX for various things; perhaps something there may have been what was being referred to; I've no klew...