applying time range to multiple txt files very slow

Hi there,

I have a large set of ".txt" data files. I then apply timerange to extract data between specific dates and times. My script looks something like this:

warning off
ds_loc = 'Z:\data\*.txt';
ds = datastore(ds_loc);
ds.ReadSize = 1000000;
ds.Delimiter = ' ';
ds.MultipleDelimitersAsOne = 1;
ds.SelectedFormats(1) = {'%{dd/MM/yyyy HH:mm:ss}D'}; 
warning on

% create time table 
tt = tall(ds); 
ttab = table2timetable(tt) 
strt_time = '03/24/2018 10:00:00'
end_time = '03/25/2018 00:00:00'

warning off 
S1 = timerange(strt_time,end_time);
warning on 
ttab(S1,:)

The above script takes a long time to execute depending on the number of files in the datastore location i.e. "Z:\data". Is there a better way do this?

[dpb]

Well, you've got both datastore and tall so it's not terribly surprising to me if that is really needed it's going to take some time.

I'm curious as to why the warning off--if ML is generating warnings mayhaps fixing the cause of those might improve performance, depending upon just what they are warning about.

It probably isn't the bottleneck, but I'd think you could just use direct access into the table returned by the creation of the tall table with isbetween and not take the extra step of then creating a timetable besides.

[minomi]

Are datastore and tall arrays not meant to be used together?

the warning is due to the input format of the date time values. the error reads:

Warning: Successfully read the date/time text using the format 'MM/dd/uuuu', but their format is ambiguous and could also be 'dd/MM/uuuu'. Specify a format character
vector to avoid ambiguity.

I'm not quite sure how to use InputFormat with datastore and solve this problem.

I had a look at isbetween. will try implementing. Can isbetween be applied to data placed in multiple files within a specific time and date or does it only work for data in one file?

[jonas]

Can't you just fix the inputformat to avoid those warnings?

ds.SelectedFormats(1) = {'%{MM/dd/yyyy HH:mm:SS}D'};

[minomi]

No that doesn't seem to fix that issue.

[dpb]

There doesn't seem to be a way to tell datastore on creation about the format; that seems to be a major shortcoming--I created an ambiguous data set here as a test and it read it backwards from what was written by guessing MM/dd instead of dd/MM.

I presume after one then corrects the format to be correct that then dates will be interpreted correctly and there won't be the issue of each element being scanned individually when a specific format string wasn't given in datenum (and I think in datetime as well). That would be a major performance bottleneck.

[minomi]

I'm sorry I don't quite understand what you've written. What are you suggesting is the way to do this?

[dpb]

I was just commenting on the problem with sequence of defining the date format...it seems as though datastore reads data (how much I've no idea) to infer format on creation of the object but you can't tell it a priori what the date format is but have to do that with a property internal to the object. That means, it would seem, that if it gets it wrong it has to recompute or reread all that information that's a waste of time; if it did get it right at least that part is ok but historically when a format wasn't given processing was significantly longer than when one was; I don't know if that effect is true here or not.

As far as speeding up the retrieval, I don't have any real suggestions as I've not had opportunity to try to use any of the large data tools "in anger" so don't know their idiosyncracies at all.

Just how big are the files and how many are there? Might it possibly turn out to be faster to simply loop through them explicitly rather than using the overhead of the magic behind the scenes datastore object?

Are they all the same form or does the index vector have to be updated for each file? It appears that timerange makes the assumption of a fixed index across the population.