use xlread to read csv file with mixed type data is very slow

Hi

I have a csv file with mixed data type like below, first column is string, 2nd column is numerical, 3rd column tothe end columns is date string. Total row number is 25000.

I used xlread() shown below to read the two csv files. it tooks me 10 minutes to read two csv file. The first csv file has only 10 columns while 2nd file has 400 columns. I am wondering if it is possible to speed it up using different approaches? Thanks

[num_steamdate,txt_steamdate,raw_steamdate] =xlsread(filename_WellDate);
[num_fracdate,txt_fracdate,raw_fracdate] =xlsread(filename_WellFracDate);

Answer 1

[Cris LaPierre]

I would suggest using readtable instead. It creates a table, which supports mutiple data types. If you need help, consider sharing your data files. I'm not feeling motivated enough to transcribe your screenshot for testing.

[roudan]

Thank Cris. I appreciate it. Yes I do need help. I believed I tried readtable before and it didn't suceed. I am uploading one of small files since it didn't allow file to be more than 5MB. I appreciate your help. Thanks Cris!

[Walter Roberson]

I had no problem with

t = readtable('test.csv');

in R2020a.

I did get a warning about a datetime format month/day having been used but that the column also potentially matched day/month, but the guess it took in using month/day was correct so there was no actual problem.

[Cris LaPierre]

The autodetect options get better each release. It looks like you are using R2017a. I had R2017b, and had to specify the input format for readtable to work. Here's the code I would use.

opts = detectImportOptions('test.csv');
opts = setvaropts(opts,4:13,'InputFormat','M/d/yyyy');
opts = setvartype(opts,1,'categorical');
test = readtable('test.csv',opts)

[roudan]

Thanks Cris and Walter.

I have three questions:

1. use readtable() , the speed is simiar to xlread()

2. I did just use readtable and then I tried index(:) and I got an error

t=readtable('test.csv')

t1=t(:,4:end);

t2=datenum(t1(:),'mm/dd/yyyy');

The error is:

You cannot subscript a table using linear indexing (one subscript) or multidimensional indexing (three or more

subscripts). Use a row subscript and a variable subscript.

3. use opts = setvaropts(opts,4:13,'InputFormat','M/d/yyyy');

- How do you know columns order of 4:13? I only know 4, I don't know 13?

Thank you so much. I appreciate your help.

[Walter Roberson]

t2 = datenum(t1{:,4:end});

However, using datenum is not recommended.

NumberOfColumns = length(opts.VariableNames);

When I tested in R2017a on Mac, it read the table without problem except for the warning about datetime format. I would have to dig through the code to see whether reading a csv in R2017a would have tried to use Excel under Windows.

[Cris LaPierre]

Why are you using datenum? What are you trying to do?

#1 - I don't know. The output - a table - has many benefits over how xlsread returns results, especially mixed data types.

#2 - See above. Best is not to use datenum. If you can explain what is needed, we can help.

For #3, I knew because I looked at the table. Walter's reply shows how to determine the number if the width is variable. Use that variable instead of 13.

NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy'); 

[roudan]

Thank you so much Cris and Walter. Sorry for not being clear.

Here is my code, after readtable(), I am trying to conver it to array so I can use normal array indexing, also i was using datenum trying to convert string into datanum for my original code using xlsread(). Not I think I don't need it anymore. But I still got an error again in the last line when using table2array():

The error is: Cell contents reference from a non-cell array. Thanks for your help. I appreciate it.

opts = detectImportOptions('test.csv');
NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy');
opts = setvartype(opts,1,'categorical');
t = readtable('test.csv',opts);
t_array=table2array(t{:,4:end});

[Cris LaPierre]

When it comes to indexing tables, I find this page the most helpful.

The issue here is the input to table2array is supposed to be, well, a table. Otherwise, the function name wouldn't make much sense. When you use curly braces, the result is an array. Knowing that, you may not even need to create t_array any more. If you due, use parentheses instead.

t_array=table2array(t(:,4:end));

[roudan]

Thanks Cris for still helping me. I am almost there. Last error I have:

Here is my complete code: The error occurs for the last line, it says

K>> wellsteamdate_array>=wellfracdate_array(:,1)

Data inputs must be the same size, or any of them can be a scalar.

%Apporach 2 to use readtable()
opts = detectImportOptions(filename_WellDate);
NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy HH:mm');  % be careful
opts = setvartype(opts,1,'categorical');
wellsteamdate = readtable(filename_WellDate,opts);
wellsteamdate_array=table2array(wellsteamdate(:,4:end));   % use (:,4:end) instead of {:,4:end}

opts = detectImportOptions(filename_WellFracDate);
NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy');
opts = setvartype(opts,1,'categorical');
wellfracdate = readtable(filename_WellFracDate,opts);
wellfracdate_array=table2array(wellfracdate(:,4:end));

filter_bfFrac_c1=and(wellsteamdate_array>=wellfracdate_array(:,1), wellsteamdate_array<wellfracdate_array(:,2));

[Cris LaPierre]

First, you don't need table2array.

wellfracdate_array=wellfracdate{:,4:end};

Without your data, I can't be sure, but I suspect your two datasets do not have the same number of rows (wellsteamdate_array and wellfracdate_array).

[roudan]

Thanks Cris. Row number are the same. That was my original code when using xlsread(), no problem at all. Here are the output to double check and the error in the last line.

K>> size(wellsteamdate_array)

ans =

24568 391

K>> size(wellfracdate_array(:,1))

ans =

24568 1

K>> size(wellfracdate_array(:,2))

ans =

24568 1

K>> and(wellsteamdate_array>=wellfracdate_array(:,1), wellsteamdate_array<wellfracdate_array(:,2));

Data inputs must be the same size, or any of them can be a scalar.

if I do row by row comparison, there is no problem, for example, I am stuck, thanks for your help.

wellsteamdate_array(2,:)>=wellfracdate_array(2,1)

[Cris LaPierre]

Without your data, I'm not able to provide any more guidance. When I test with madeup data of the same size, your code works for me. Can you attach your two tables?

[roudan]

Thansk Cris. I really appreciate your help. sorry for not letting you go. Here is my two csv files and the codes.

filename_WellDate='WellDate.csv';
filename_WellFracDate='WellFracDate.csv';  

opts = detectImportOptions(filename_WellDate);
NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy HH:mm');  % be careful
opts = setvartype(opts,1,'categorical');
wellsteamdate = readtable(filename_WellDate,opts);
wellsteamdate_array=table2array(wellsteamdate(:,4:end));   % use (:,4:end) instead of {:,4:end}

opts = detectImportOptions(filename_WellFracDate);
NumberOfColumns = length(opts.VariableNames);
opts = setvaropts(opts,4:NumberOfColumns,'InputFormat','M/d/yyyy');
opts = setvartype(opts,1,'categorical');
wellfracdate = readtable(filename_WellFracDate,opts);
wellfracdate_array=table2array(wellfracdate(:,4:end));

filter_bfFrac_c1=and(wellsteamdate_array>=wellfracdate_array(:,1), wellsteamdate_array<wellfracdate_array(:,2));
filter_bfFrac_c2=and(wellsteamdate_array>=wellfracdate_array(:,3), wellsteamdate_array<wellfracdate_array(:,4));
filter_bfFrac_c3=and(wellsteamdate_array>=wellfracdate_array(:,5), wellsteamdate_array<wellfracdate_array(:,6));
filter_bfFrac_c4=and(wellsteamdate_array>=wellfracdate_array(:,7), wellsteamdate_array<wellfracdate_array(:,8));
filter_bfFrac_c5=and(wellsteamdate_array>=wellfracdate_array(:,9), wellsteamdate_array<wellfracdate_array(:,10));

filter_onFrac_c1=(wellsteamdate_array==wellfracdate_array(:,2));
filter_onFrac_c2=(wellsteamdate_array==wellfracdate_array(:,4));
filter_onFrac_c3=(wellsteamdate_array==wellfracdate_array(:,6));
filter_onFrac_c4=(wellsteamdate_array==wellfracdate_array(:,8));
filter_onFrac_c5=(wellsteamdate_array==wellfracdate_array(:,10));

[Walter Roberson]

I seem to recall encountering an issue where implicit expansion was not supported for datetime arrays.

Instead of

wellsteamdate_array>=wellfracdate_array(:,1)

try

bsxfun(@ge, wellsteamdate_array, wellfracdate_array(:,1))

and similar changes for other expressions.

[Cris LaPierre]

Ok, it appears you cannot use implicit expansion on datetime arrays. You must make sure both arrays have the same number of rows AND columns. You need to repeat the column of wellfracdate_array to match the width of wellsteamdate_array (391 columns). You can do this with repmat.

filter_bfFrac_c1=and(wellsteamdate_array >= repmat(wellfracdate_array(:,1),[1,size(wellsteamdate_array,2)]),...
    wellsteamdate_array < repmat(wellfracdate_array(:,2),[1,size(wellsteamdate_array,2)]));
filter_bfFrac_c2=and(wellsteamdate_array >= repmat(wellfracdate_array(:,3),[1,size(wellsteamdate_array,2)]),...
    wellsteamdate_array < repmat(wellfracdate_array(:,4),[1,size(wellsteamdate_array,2)]));
filter_bfFrac_c3=and(wellsteamdate_array >= repmat(wellfracdate_array(:,5),[1,size(wellsteamdate_array,2)]),...
    wellsteamdate_array < repmat(wellfracdate_array(:,6),[1,size(wellsteamdate_array,2)]));
filter_bfFrac_c4=and(wellsteamdate_array >= repmat(wellfracdate_array(:,7),[1,size(wellsteamdate_array,2)]),...
    wellsteamdate_array < repmat(wellfracdate_array(:,8),[1,size(wellsteamdate_array,2)]));
filter_bfFrac_c5=and(wellsteamdate_array >= repmat(wellfracdate_array(:,9),[1,size(wellsteamdate_array,2)]),...
    wellsteamdate_array < repmat(wellfracdate_array(:,10),[1,size(wellsteamdate_array,2)]));

filter_onFrac_c1=(wellsteamdate_array == repmat(wellfracdate_array(:,2),[1,size(wellsteamdate_array,2)]));
filter_onFrac_c2=(wellsteamdate_array == repmat(wellfracdate_array(:,4),[1,size(wellsteamdate_array,2)]));
filter_onFrac_c3=(wellsteamdate_array == repmat(wellfracdate_array(:,6),[1,size(wellsteamdate_array,2)]));
filter_onFrac_c4=(wellsteamdate_array == repmat(wellfracdate_array(:,8),[1,size(wellsteamdate_array,2)]));
filter_onFrac_c5=(wellsteamdate_array == repmat(wellfracdate_array(:,10),[1,size(wellsteamdate_array,2)]));

[roudan]

Wow, Cris, Yes it works finally. Thank you so much, I really appreciate it!! i l got very goof learning on readtable(). thank you!

But going back to use xlsread(), the speed in my computer is the same, but using xlsread() actually leads to cleaner code. i have the other file with same number of row and column but with numberic data, reading it using readcsv is much faster.

Here is my code, you can give it a try for timing check.

        [num_steamdate,txt_steamdate,raw_steamdate] =xlsread(filename_WellDate);
        [num_fracdate,txt_fracdate,raw_fracdate] =xlsread(filename_WellFracDate);    
        wellsteamdate_str=raw_steamdate(:,4:end);
        wellsteamdate=datenum(wellsteamdate_str(:),'mm/dd/yyyy'); % convert it to 1D column since datenum is expecting column or row not array
        wellsteamdate_array=reshape(wellsteamdate,size(wellsteamdate_str));  % convert 1D back to original shape

        wellfracdate_str=raw_fracdate(:,4:end);
        wellfracdate=datenum(wellfracdate_str(:),'mm/dd/yyyy'); % convert it to 1D column since datenum is expecting column or row not array
        wellfracdate_array=reshape(wellfracdate,size(wellfracdate_str));

        filter_bfFrac_c1=and(wellsteamdate_array>=wellfracdate_array(:,1), wellsteamdate_array<wellfracdate_array(:,2));
        filter_bfFrac_c2=and(wellsteamdate_array>=wellfracdate_array(:,3), wellsteamdate_array<wellfracdate_array(:,4));
        filter_bfFrac_c3=and(wellsteamdate_array>=wellfracdate_array(:,5), wellsteamdate_array<wellfracdate_array(:,6));
        filter_bfFrac_c4=and(wellsteamdate_array>=wellfracdate_array(:,7), wellsteamdate_array<wellfracdate_array(:,8));
        filter_bfFrac_c5=and(wellsteamdate_array>=wellfracdate_array(:,9), wellsteamdate_array<wellfracdate_array(:,10));

        filter_onFrac_c1=(wellsteamdate_array==wellfracdate_array(:,2));
        filter_onFrac_c2=(wellsteamdate_array==wellfracdate_array(:,4));
        filter_onFrac_c3=(wellsteamdate_array==wellfracdate_array(:,6));
        filter_onFrac_c4=(wellsteamdate_array==wellfracdate_array(:,8));
        filter_onFrac_c5=(wellsteamdate_array==wellfracdate_array(:,10));