what is the fastest way to convert a cell array of delimited numbers into a matrix

I have a cell array called 'out', which has 1 column and 500k rows and looks the following way:

1,5012,0,35,6

2,395,1,35,8

...

That is, each cell contains a string of numbers delimited by a comma. I'd like to obtain a matrix of numbers, D. I used the following script:

 for i=1:size(out,1)
      singline = (textscan(out{i},'%s','delimiter',','));      
      D(i,:) = str2double(singline{1});          
 end

It takes 206 seconds, which seems long, as this is just a small part of a big script to be repeated many times.

I also found out that I can use a function dlmcell taken from here: http://www.mathworks.com/matlabcentral/fileexchange/25387-write-cell-array-to-text-file/content/dlmcell.m

So if I use it in the script:

 File = 'E:\result.csv';
 dlmcell(File,out,',');  
 D = dlmread(File);

it takes only 31 seconds, and I get what I wanted, i.e. matrix D made of numbers

But it seems weird to me that a script with writing a file to a hard-drive and reading it back works faster than the one without that. So I'd like to ask what would be actually the fastest way to do this (using MATLAB 2010).

Thanks.

[Stephen23]

For a fast and robust solution that does not use eval (as str2num does), see Jos's answer.

Answer 1

[Jos (10584)]

A = {'1,5012,0,35,6' ; '2,395,1,35,8'} ;
A = repmat(A,250000,1) ; % big array!
tic ;
A2 = strcat(A,',') ;
V = sscanf([A2{:}],'%f,') ;
V = reshape(V,5,[]).' ;
toc
% Elapsed time is 2.397291 seconds.

[Olga Petrik]

thanks a lot! this is the fastest out of the three methods for the cell array I am using. Just tested all three one more time:

char(): Elapsed time is 43.355122 seconds.

dlmcell(): Elapsed time is 29.993901 seconds.

strcat/sscanf/reshape: Elapsed time is 4.408727 seconds.

[Fangjun Jiang]

Nice! The lat line could be V = reshape(V,[],5), right?

[Jos (10584)]

Nope.

[Sandeep Chaudhuri]

Hi Jos,

I have a few questions:

1. Instead of 5 (like Olga's) my data file has 1469 elements in the cell. So I cannot write the code starting with a={'1,5012,0,35,6';'2,395,1,35,8'}; Also, the elements are space delimited. The data format is %.3e. There are 20 (or more, upto 10k) rows in the file. I modified your code as below. But it is giving an error message.

Err message: >> test08032023_2

Brace indexing is not supported for variables of this type.

Error in test08032023_2 (line 6)

V = sscanf([A2{:}],'%.3e,', 'Delimiter', ' ') ;

------------------------------

My code:

D = 'D:\test1.txt';

data = fopen(D) ;

A = repmat(data,20,1) ; % big array!

tic ;

A2 = strcat(A,' ') ;

V = sscanf([A2{:}],'%.3e,', 'Delimiter', ' ') ;

V = reshape(V,1469,[]).' ;

toc

------------------------------

I have attached a data file which has 20 rows, in case you want to try.

2. A=repmat(a,250000,1); Why is there 250000 in the repmat function when Olga's data had 500000 rows?

[Stephen23]

@Sandeep Chaudhuri: the first two lines of the code given in this answer are just for creating some fake test data (because code does not run on air, it requires data to run on. If the user does not provide us with data then we have to create some fake data. Otherwise how would we test the code?).

You do not use the first two lines of code, of course you would use your own data.

" So I cannot write the code starting with..."

You don't, you use your data (not the fake test data that Jos created for running their code on).

"There are 20 (or more, upto 10k) rows in the file."

The file you uploaded has exactly one row (with nearly thirty thousand numbers, but that does not changes how many rows it has).

In any case, the file you uploaded (I moved it from the incorrectly posted Answer you created) is very simple to import using e.g. READMATRIX:

format short G
V = readmatrix('testpulses_20.txt')

V = 1×29380

1.0e+00 * 8.876e-06 2.572e-06 5.576e-06 -1.26e-06 3.409e-06 -1.263e-06 -5.398e-06 1.055e-05 1.124e-05 9.266e-06 4.663e-07 6.243e-06 -1.411e-06 -6.503e-06 1.494e-05 -7.689e-06 -3.405e-06 1.173e-05 5.781e-06 -6.02e-06 -3.065e-06 -5.724e-08 5.17e-06 6.491e-06 3.565e-06 1.258e-05 -3.614e-06 8.884e-07 1.131e-05 6.036e-06

Is there a particular requirement you have to make your code more complex than that?

[Sandeep Chaudhuri]

Thank you for your response. Probably I did not make it clear in my question. I need to make a matrix which has 1469 columns and 20 (or whatever the row number is) rows and save it as a .txt file.

[Steven Lord]

Try reversing the order of the steps. Write your comma-delimited text data to a file first then use readmatrix to read the data from the file you just wrote.

[Stephen23]

"I need to make a matrix which has 1469 columns and 20 (or whatever the row number is) rows and save it as a .txt file."

Use WRITEMATRIX to write a matrix to a text file.

"Probably I did not make it clear in my question."

So far nothing is clear to me: the data file you uploaded does not match your description, and the questions and code you tried seem totally inappropriate for whatever file import/export task you have.

[Sandeep Chaudhuri]

I apologize for any confusion. Let me start afresh and please ignore all my previous posts.

I have the attached data set (testpulses_100_08042023.txt) which is probably 1 column and 100 rows. I say 'probably' as I cannot open the file in excel or notepad and view the actual data structure. But the program that generates the data, says so. The column probbaly has 1469 space delimited data points with the format %.3e.

Per your suggestions I am using the code given below. The program works and the output gives a matrix with 100 columns and every column has 1469 rows. This is just the way I need the data to be. So far so good.

.........................

format short G

V = readmatrix('testpulses_100_08042023.txt');

matrixData = reshape(V, 1469, 100);

.........................

The problem arises with the attached file (testpulses_10k_08042023.txt). This file is supposed to have 10000 rows instead of 100 rows as in the first file. Both the files are otherwise generated exactly the same way. But in the latter one, when I run the above program (of course with matrixData = reshape(V, 1469, 10000);), I am receiving the following error message.

Error using reshape

Number of elements must not change. Use [] as one of the size inputs to automatically calculate the appropriate size for that dimension.

Error in community (line 3)

matrixData = reshape(V, 1469, 10000);

[Voss]

@Sandeep Chaudhuri: Please check that you have uploaded the files. I don't see them.

[Sandeep Chaudhuri]

I am sorry, I forgot to attach the files. I can attach the file with 100 rows but the one with 10000 is like 144 MB and there is no way I can attach it..

[Stephen23]

" I can attach the file with 100 rows but the one with 10000 is like 144 MB and there is no way I can attach it.."

The file you attached has exactly one row, not 100 rows. It is very easy to import:

V = readmatrix('testpulses_100_08042023.txt')

V = 1×146900

0.0020 -0.0001 -0.0001 0.0006 -0.0006 0.0022 -0.0011 0.0000 -0.0010 -0.0009 0.0004 0.0007 -0.0005 -0.0015 -0.0024 -0.0005 -0.0006 -0.0021 -0.0012 -0.0022 0.0013 0.0001 -0.0014 0.0022 -0.0006 -0.0021 0.0004 0.0022 0.0029 0.0006

Of course if the number of elements is suitable, there is nothing stopping you from reshaping it:

M = reshape(V,[],100).' % taking a guess about the order

M = 100×1469

0.0020 -0.0001 -0.0001 0.0006 -0.0006 0.0022 -0.0011 0.0000 -0.0010 -0.0009 0.0004 0.0007 -0.0005 -0.0015 -0.0024 -0.0005 -0.0006 -0.0021 -0.0012 -0.0022 0.0013 0.0001 -0.0014 0.0022 -0.0006 -0.0021 0.0004 0.0022 0.0029 0.0006 0.0035 -0.0005 0.0033 -0.0003 0.0017 -0.0015 -0.0010 -0.0015 -0.0027 0.0029 -0.0017 0.0004 0.0015 -0.0024 -0.0001 0.0016 0.0001 -0.0028 0.0032 -0.0016 -0.0008 -0.0003 -0.0008 -0.0004 -0.0007 0.0033 0.0007 -0.0008 -0.0002 0.0001 -0.0025 -0.0020 -0.0012 -0.0020 0.0030 -0.0016 0.0009 0.0012 -0.0016 -0.0015 -0.0014 -0.0017 -0.0008 0.0001 0.0009 -0.0044 -0.0014 0.0002 -0.0008 0.0025 0.0006 -0.0022 0.0005 -0.0002 -0.0008 -0.0007 -0.0003 0.0021 0.0002 -0.0021 -0.0008 -0.0001 -0.0003 -0.0011 -0.0004 0.0013 -0.0008 0.0009 0.0006 0.0005 -0.0013 -0.0007 -0.0003 0.0003 0.0001 -0.0003 -0.0005 -0.0002 -0.0003 0.0001 0.0016 -0.0001 0.0008 0.0002 -0.0013 -0.0001 0.0022 0.0007 -0.0016 -0.0003 0.0005 -0.0007 0.0003 0.0003 -0.0001 -0.0006 -0.0003 -0.0006 -0.0016 0.0012 0.0011 -0.0009 0.0002 -0.0014 -0.0003 0.0000 0.0009 0.0002 -0.0007 -0.0002 -0.0002 -0.0005 0.0005 -0.0014 0.0010 -0.0011 -0.0009 0.0004 0.0002 -0.0002 -0.0009 0.0001 -0.0001 0.0017 0.0008 -0.0008 -0.0006 0.0012 -0.0017 0.0011 0.0002 -0.0001 -0.0009 0.0022 -0.0012 0.0002 0.0012 0.0013 0.0016 0.0005 0.0012 0.0005 0.0006 0.0006 -0.0013 0.0029 -0.0014 -0.0000 -0.0010 -0.0004 -0.0003 -0.0001 0.0001 -0.0002 0.0001 -0.0001 -0.0001 0.0002 0.0000 -0.0001 0.0000 -0.0001 0.0001 0.0001 -0.0001 0.0002 -0.0002 0.0002 -0.0001 0.0000 -0.0000 0.0000 0.0000 0.0001 -0.0000 0.0000 -0.0002 -0.0001 0.0002 -0.0001 0.0015 -0.0008 -0.0005 0.0006 -0.0010 0.0015 0.0003 -0.0018 -0.0018 0.0010 -0.0013 0.0005 0.0015 -0.0009 0.0003 0.0023 0.0006 -0.0000 -0.0010 0.0005 -0.0013 -0.0010 -0.0012 0.0001 -0.0001 0.0003 -0.0009 -0.0017 0.0035 -0.0006 0.0002 0.0000 0.0004 -0.0002 0.0002 0.0001 -0.0002 -0.0001 0.0001 0.0011 0.0005 -0.0001 0.0002 0.0005 0.0002 0.0001 -0.0001 0.0001 -0.0005 0.0002 0.0001 -0.0000 0.0000 0.0001 -0.0003 0.0005 0.0001 -0.0002 0.0001 0.0001 -0.0003 0.0003 -0.0000 -0.0001 -0.0003 -0.0004 0.0004 0.0007 -0.0003 0.0007 0.0000 -0.0003 -0.0000 -0.0001 0.0006 0.0002 -0.0006 0.0003 -0.0010 -0.0003 -0.0007 -0.0009 0.0000 0.0003 0.0006 -0.0005 0.0002 -0.0001 -0.0002 0.0003

If your imported data does not reshape, it is because the number of elements is not suitable.

Show us the outputs from these commands:

V = readmatrix('your huge data file.txt');

size(V)

and tell us the exact size you want to reshape it into.

Answer 2

[Fangjun Jiang]

a={'1,5012,0,35,6';'2,395,1,35,8'};
b=str2num(char(a))
b =

           1        5012           0          35           6
           2         395           1          35           8

a={'1,5012,0,35,6';'2,395,1,35,8'};
aa=repmat(a,250000,1);
tic;
b=str2num(char(aa));
toc

Elapsed time is 19.681015 seconds.

[Olga Petrik]

thank you!

this one took 82 seconds.

[Fangjun Jiang]

You mean it is slower than the dlmcell() approach? My comparison shows it is faster than the dlmcell() approach.

[Olga Petrik]

yes, I have tested both methods with a generated cell array of the same size, and indeed, the dlmcell method was slower:

char(a): Elapsed time is 15.682667 seconds.

dlmcell(): Elapsed time is 23.495048 seconds.

But when I test it with my real cell array the dlmcell is still faster:

char(a): Elapsed time is 59.761913 seconds.

dlmcell(): Elapsed time is 31.591709 seconds.

So it might be something about this particular cell array I have. The real cell of it looks like this: '71,1,4,1,4,16856,538131,5,1,21.25,21.75,1380003891,506080,21.25'

And it was obtained from another cell array, which contained both numbers and words, by substituting the words with some numbers.

But then, when I tried to copy-paste just the above single cell content and repmat it 500k times, and test it one more time, the results were again 15 seconds and 25 seconds respectively, instead of 59/31 observed with the real data.