Converting a cell array column of strings to a numeric matrix

I am trying to manipulate a .csv file after importing it through a user created file exchange code called specifically: CSVIMPORT. The resulting data is an array, of which I am only concerned with one column. Being recognized as an array of strings, my intention is to convert the column to a simple 1x1 matrix. However, applying the cell2mat command to the column as a whole returns a concatenation error as follows:

??? Error using ==> cat CAT arguments dimensions are not consistent.

Error in ==> cell2mat at 89 m{n} = cat(1,c{:,n});

The column of interest has empty cells interspersed between a data sets of a single cell that would look as follows, for example:

'0|1.75|1|.5|0'

With that being said, the goal of this code would be to automate the process of copy and pasting that column to the workspace and separating the data with the import wizard with some variation of the delimiter.

Can you explain to me the best way to convert the data from the imported cell array form into its final delimited matrix fashion?

All the best.

[Fangjun Jiang]

I would recommend you try csvread(), importdata() or xlsread() first to import your csv file. If neither can get you to the right format, please provide an example of the resulting data and explain what is your final expected output.

[Keith]

In trying to convert the original csv, each of these commands spit out either errors that are unable to be fixed, or entire columns replacing the significant data with 'NaN'.

'speed_1s' '1.375|2.0625|2.75|1.625|1.5' 'm/s'

'speed_1s' '1|0.5625|0.4375|0|0' 'm/s'

'speed_1s' '0|0.5|1.6875|2.1875|2.3125' 'm/s'

'speed_1s' '1.4375|1.75|2.0625|1.75|1.0625' 'm/s'

'speed_1s' '1.0625|1.125|1.0625|1.0625|1.0625' 'm/s'

'speed_1s' '1.0625|1.0625|1.0625|1.0625|1.125' 'm/s'

'speed_1s' '1.0625|1.0625|1.0625|1.0625|1.0625' 'm/s'

'speed_1s' '1.0625|1.0625|0.1875|0|0' 'm/s'

'speed_1s' '0|0.5|1.625|1.1875|0.3125' 'm/s'

'speed_1s' '0.375|0.3125|0.375|0.3125|0.375' 'm/s'

'speed_1s' '0.3125|0.375|0.5625|0.6875|0.6875' 'm/s'

'speed_1s' '0.75|0.6875|0.6875|0.6875|0.6875' 'm/s'

'speed_1s' '0.8125|0.8125|0.8125|0.875|0.8125' 'm/s'

'speed_1s' '0.8125|0.8125|0.8125|0.8125|0.8125' 'm/s'

'speed_1s' '0.8125|0.8125|0.8125|0.8125|0.8125' 'm/s'

'speed_1s' '0.8125|0.875|0.8125|0.875|0.875' 'm/s'

'speed_1s' '0.8125|0.8125|0.8125|0.875|0.8125' 'm/s'

'speed_1s' '0.8125|0.8125|0.875|0.8125|0.8125' 'm/s'

That is an example of the resulting cell array data that is saved as a result of the 'csvimport' m-file I have been using. These are three of the 35 columns that are in my csv files. The only column I am concerned with is the middle column here (otherwise, column 18). These values are eventually to be delimited into separate columns themselves, but I am having difficulties getting the cell array data, because of its many empty spaces, strings, and rectangular references, into a matrix I can work with.

Thanks a lot Fangjun for your help!

-Keith

Answer 1

[Fangjun Jiang]

If CSVIMPORT can't get what you want, I would start with your original .csv file and do proper parsing. Anyway, save your above example text data in test.csv and run the following code.

% the whole file is imported as cell array of strings
a=importdata('test.csv'); 
% read in all the non-whitespace characters, excluding the single quotes
b=regexp([a{:}],'[^''\s]*','match');
% pick the second column only
c=b(2:3:end);
% red the numerical data
d=regexp(c,'\|','split');
% make it numerical matrix
e=str2double(cat(1,d{:}));

>> whos e
  Name       Size            Bytes  Class     Attributes

    e         18x5               720  double              

>> e
e =
    1.3750    2.0625    2.7500    1.6250    1.5000
    1.0000    0.5625    0.4375         0         0
         0    0.5000    1.6875    2.1875    2.3125
    1.4375    1.7500    2.0625    1.7500    1.0625
    1.0625    1.1250    1.0625    1.0625    1.0625
    1.0625    1.0625    1.0625    1.0625    1.1250
    1.0625    1.0625    1.0625    1.0625    1.0625
    1.0625    1.0625    0.1875         0         0
         0    0.5000    1.6250    1.1875    0.3125
    0.3750    0.3125    0.3750    0.3125    0.3750
    0.3125    0.3750    0.5625    0.6875    0.6875
    0.7500    0.6875    0.6875    0.6875    0.6875
    0.8125    0.8125    0.8125    0.8750    0.8125
    0.8125    0.8125    0.8125    0.8125    0.8125
    0.8125    0.8125    0.8125    0.8125    0.8125
    0.8125    0.8750    0.8125    0.8750    0.8750
    0.8125    0.8125    0.8125    0.8750    0.8125
    0.8125    0.8125    0.8750    0.8125    0.8125

[Keith]

Thanks again, Fangjun.

After applying the 'importdata' command to my .csv file, I end up with a 1x1 structure, complete with a single value for the data field, a 2x1 cell for the 'textdata', and a single cell of column headers.

As a result, I cannot use this command to properly manipulate my data.

Do you think there are alternatives going down this avenue?

-Keith

[Fangjun Jiang]

You may use different version of MATLAB than mine. As long as your text data contains similar strings as you show in your post, you can use the code above. Or, create the .csv file as above as I did and understand the code. Then you can apply the technique to your real .csv file with some tweaks.