Indirectly addressing a tall datastore -- MATLAB Golf

1 view (last 30 days)
Michael
Michael on 2 Sep 2019
Commented: Michael on 3 Sep 2019
Hello,
I'm hoping to clean up a bit of ugly code.
I have a tall array in this format with DATE and TICKER being the indexes:
>> head(tds)
ans = 8×4 tall table
DATE TICKER FIELD VALUE
____________ _____________ _____________________________ __________
'09/30/1997' ARBABAL INDEX ACTUAL_RELEASE '-581'
'09/30/1997' ARBABAL INDEX BN_SURVEY_NUMBER_OBSERVATIONS '0'
'09/30/1997' ARBABAL INDEX ECO_RELEASE_DT '19971106'
'10/31/1997' ARBABAL INDEX ACTUAL_RELEASE '-585'
'10/31/1997' ARBABAL INDEX BN_SURVEY_MEDIAN '-553'
'10/31/1997' ARBABAL INDEX BN_SURVEY_NUMBER_OBSERVATIONS '0'
'10/31/1997' ARBABAL INDEX ECO_RELEASE_DT '19971205'
'11/30/1997' ARBABAL INDEX ACTUAL_RELEASE '-907'
Keeping the column names in the FIELD column allows for a lot of fexibility but it's really not that useful unless it is pivoted using unstack.
>> unstack(tds,'VALUE','FIELD')
ans = 3×6 table
DATE TICKER ACTUAL_RELEASE BN_SURVEY_MEDIAN BN_SURVEY_NUMBER_OBSERVATIONS ECO_RELEASE_DT
____________ _____________ ______________ ________________ _____________________________ ______________
'09/30/1997' ARBABAL INDEX '-581' '' '0' '19971106'
'10/31/1997' ARBABAL INDEX '-585' '-553' '0' '19971205'
'11/30/1997' ARBABAL INDEX '-907' '' '' ''
Unfortunately, the tall array is very tall, much taller than 8, and cannot be held in memory so I'm using a datastore. Also the pivoted table would be very wide, much wider than 6.
I need to do something like this:
select DATE, TICKER, ACTUAL_RELEASE, BN_SURVEY_MEDIAN from tds where str2double(BN_SURVEY_NUMBER_OBSERVATIONS) > 0
It would be easy in the pivoted table, but is pretty ugly in the tall table.
Could someone find an elegant soultion, or at least less ugly?
Thanks,
Michael
PS, this is what I have:
ds = datastore('talltable.csv'); % CREATE A DATASTORE
ds.SelectedFormats={'%q','%C','%C','%s'}; % DEFINE COLUMN FORMATS
ds.VariableNames = {'DATE','TICKER','FIELD','VALUE'}; % DEFINE COLUMN NAMES
tds = tall(ds); % CREATE A TALL ARRAY
d.summary = summary(tds); % GET SUMMARY STATISTICS
i = find(tds.FIELD=='BN_SURVEY_NUMBER_OBSERVATIONS'); % WHICH ROWS CONTAIN THE NUMBER OF OBSERVATIONS?
j = i(str2double(tds.VALUE( i)) >10); % WHICH OF THOSE ROWS HAVE MORE THAN 10 OBSERVATIONS?
J = gather(j);
idx = [(tds.DATE) cellstr(tds.TICKER)]; % CREATE AN INDEX OF DATE AND TICKER COMBINATIONS
IDX = gather(idx);
I = strcat(IDX(:,1),IDX(:,2));
ten = unique(I(J)); % WHICH OF THESE CORRESPOND TO J
[I, ten] =gather(I,ten);
big = ismember(I, ten); % WHICH ELEMENTS IN THE TALL ARRAY CORRESPOND TO TEN
ss = tds(big, :); % EXTRACT THE TABLE
SS=gather(ss);
t = unstack(SS,'VALUE','FIELD'); % UNSTACK

Answers (2)

Guillaume
Guillaume on 2 Sep 2019
Edited: Guillaume on 2 Sep 2019
Is your FIELD column imported as a string array (as opposed to cellstr)? As a cellstr the == 'BN_SURVEY...' wouldn't work, you'd have to use strcmp.
What's very ugly in my opinion is that you store numbers as strings/char vectors, which is going to slow things down significantly.
Anyway, this sounds like a job for findgroups and splitapply.
First, the selection function:
function [release, surveymed, observations] = getselectedfield(fields, values)
release = str2double(values(strcmp(fields, 'ACTUAL_RELEASE')));
median = str2dobule(values(strcmp(fields, 'BN_SURVEY_MEDIAN')));
observations = str2double(values(strcmp(fields, 'BN_SURVEY_NUMBER_OBSERVATIONS'))
end
then:
[group, groupdate, groupticker] = findgroups(tds.DATE, tds.TICKER);
[releases, surveymed, observations] = splitapply(@getselectedfields, tds.FIELD, tds.VALUE, group);
selected = table(groupdate, groupticker, releases, surveymed, observations);
selected = gather(selected(selected.observations > 0, :));
Untested since you haven't provided example data as a mat file. It may be that some of these functions don't work with a tall array.
  4 Comments
Michael
Michael on 3 Sep 2019
Sorry, I accidentally answered instead of commented.

Sign in to comment.


Michael
Michael on 3 Sep 2019
Dear Guillaume,
Thank you.
1) It would be more than optimal because the data set is huge, but I think I can manage this way. Right now, I'm breaking up the data into several smaller files but I'd prefer to have one large file for simplicity.
2) I am attaching a dummy data set but I'd like to use this technique on different files with different field names and diferent queries as well. This file format is very flexible and convenient if only I could make the queries nicer. I'm also working on a database solution but I'd like to do it all in Matlab if I can.
3) Yes, there are very many repetitions for FIELD but each has row a unique FIELD, TICKER and DATE combination. It will be apparent in the dummy data.
Thanks for the help,
Michael

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!