Any simple ideas to improve performance of reading long (10 GB scale) .csv file?
22 views (last 30 days)
I am trying to improve the performance of reading .cvs files containing fiber photometry data. As stated, the files are around a few 10 GB in size and have around 10e7 rows and 11 columns, long story short: they fit in the memory of my machine ().
I've done some benchmarking using textscan, fread, readtable and readmatrix. Please look at the attached file, that generates some sample data and runs the benchmark. This data is smaller, than the original data, but it can be made longer easily.
I've read other questions on the topic, but did not found any solution. Does anyone have a good idea to speed up the file reading? Can I for example try paralell computing?
Another thing, maybe I should open another question for this. In the example I attach, textscanning "linewise" (using a line format) results in an error if the file contains empty values. This is the error message I get
Error using textscan
Mismatch between file and format character vector.
Trouble reading 'Numeric' field from file (row number 4, field
number 7) ==> ,0.643668,0.396115,0.987143,1,1,1,1,\n
Is there a way, I can get NaN values for missing data just like the readtable function does? I tried all the settings, I could find in the textscan documentation without any success. I attached a sample file with missing values.
Thank you for your time in advance
Yair Altman on 2 Feb 2022
If you need to read the entire file for some reason, then try to use parallelization with multiple workers. Each worker would read a different file section, starting at a different offset. At the end, you'd need to merge the sections read by the different workers. You can test different number of workers, but I assume you'd get better results by using more workers than your CPU cores (i.e. more than the default number of pool workers), because in most likelihood you would be limited by I/O throughput more than CPU processing.
But if you do not need to read the entire file into memory, just a certain section, this would make the problem much easier (and faster) to resolve.
Walter Roberson on 5 Feb 2022
Ah, I managed to find it. See https://www.mathworks.com/matlabcentral/answers/801591-improving-speed-of-readtable#comment_1458376 where I did some timing tests.
fileread() followed by str2num() was the fastest, by a small margin. However, you have the difficulty that you have empty fields. Neither str2num() nor sscanf() can deal with empty fields.
As your files are quite big, it is difficult at the moment to predict what the fastest approach would be that takes into account empty fields. It might turn out to be readmatrix() . But if you have enough memory, then there is a possibility that maybe it would be more efficient to fileread() and then regexprep() to replace empty fields with NaN and then str2num()