Improving speed of readtable

I have a large array stored in a .dat file (see Example.dat attached) and I need to import the array into MATLAB.

At the moment I am using the following approach to load the table and convert it to an array.

Example_Table = readtable("Example.dat");
Example_Array = table2array(Example_Table);

This process is, however taking much longer than I would expect since I have a reasonably powerful PC.

I suspect that the issue is related to the array having a large number of zero entries.

The results of Run & Time are shown below

It is clear that pretty much all of the time is involved in reading the table and not in converting it to an array.

The timing profile of table.readTextFile>textscanReadData is shown below

Where all of the time is spent on the TreatAsEmpty command (because of having many zero entries?).

Below is a snapshot of the CPU and RAM usage during the reading of table.

Here it is clear that there is a lot of computational power not being used so this process should be able to be sped up some way or another.

How can I make this process run faster?

I have to read in lots of data like this and it is a very frustrating process.

Thanks in advance!

Answer 1

[Walter Roberson]

Where all of the time is spent on the TreatAsEmpty command (because of having many zero entries?)

No, that is not what is happening.

What is happening is that Mathworks coded the internal call to textscan() split across two lines

data = textscan(fid, format, a bunch of stuff here, ...
                'TreatAsEmpty', treatasempty, a bunch more stuff here)

The time for the overall call is accounted to the last line of the call, as the textscan() call itself cannot start until all of the parameters have been executed.

For this purpose, "executed" for something like

treatasempty = [];
textscan('TreatAsEmpty', treatasempty)

would consist of parsing the character vector 'TreatAsEmpty' and created a temporary (unnamed) expression block for it and pushing that into the parameters; and then parsing the variable named treatasempty and locating the variable in scope and pushing its (named) expression block into the parameters. Those operations might not take long but they take some time, and that time is time spent preparing to call textscan() but not yet having called textscan(). The time to parse the parameters and get ready for the call is being shown in the 554 line, the data = textscan( part.

'TreatAsEmpty' is not a command in this context: it is just a literal constant to be prepared and passed in to the function.

The timing you are seeing for line 555 is the time spent executing the textscan()

[Walter Roberson]

'TreatAsEmpty' has little or no bearing on your code. TreatAsEmpty does not deal with zeros: it is for cases where the file might have something like

3 8 2 N/A 7

and you want to tell readtable() or textscan() to treat the N/A (or whatever appropriate string) as if the field existed but is empty, with typically it having been further configured that in numeric fields empty should be treated as NaN .

[Walter Roberson]

If you are processing the same files multiple times, it might be worth converting the data to sparse representation.

[Walter Roberson]

On my system:

• time to read with readtable(): about 180 to 200 seconds (assume there is a file cache effect)
• time to read with load(): about 186 seconds
• time to read with textscan(fid, '', 'collectOutput', true): about 156 seconds
• time to read with readmatrix(): about 87 seconds
• time to read with fileread() followed by sscanf(): about 43 seconds (includes time for transpose after)
• time to read with fileread() followed by str2num(): about 42 seconds

readtable() would want to parse the file first to verify that the variables are all numeric.

The str2num code:

tic; S = fileread('Example.dat'); t = str2num(S); time_str2num = toc

[Daniel van Huyssteen]

Thanks so much for all of this!

I'm having trouble writing a sparse matrix to a dat file using either dlmwrite or fwrite.

Is there maybe another format I can write to that MATLAB prefers? Would it be better to save the data as .mat files? I think this should be possible

[Bjorn Gustavsson]

If you convert your data to sparse arrays it might be easiest to simply save them in .mat-files (especially if they will only be used with matlab, but other analysis-environments like python also supports reading .mat-files).

[Walter Roberson]

If you have a matrix of data, D, then

tic
[r, c, s] = find(D);
as_rows = [r, c, s].';
dlmwrite('Example_sparse.csv', as_rows, 'precision', 16);
toc

About 1.3 seconds to write

Restore with

tic
S = fileread('Example_sparse.csv'); rcs = str2num(S); Drestored = sparse(rcs(1,:), rcs(2,:), rcs(3,:));
time_via_sparse = toc

about 1.5 seconds to read.

[Daniel van Huyssteen]

This works great! Thanks! :D

Answer 2

[Bjorn Gustavsson]

It is a rather large data-file to read. You might reduce the read-time if you use load instead of readtable - that should reduce all sorts of overhead associated with the capacity to handle all sorts of data-formats of readtable.

If you have the capacity to modify the data-format of your files that might be a far more successful way forward if you have very sparse data - then you might be better off saving the non-zero components together with their row and column indices and handle that when reading data instead of saving large number of zeros. But maybe you're given the data and have to shovel zeros and zeros around...

HTH

[Daniel van Huyssteen]

Thanks for the answer.

Unfortunately I'm not able to manipulate the writing/storage of the original data.

Curiously, using load instead of readtable takes even longer!

[Bjorn Gustavsson]

That's a double bummer. I'm really surprised that load takes longer time, I would've bet good money that the more general capacity of readtable would cost time. Then perhaps you can save overall processing-time by following Walter's suggestion of converting the data-files to a sparse format. You might be able to bulk-process all data-files over-night when it doesn't test your patience...