Tall array - How can I perform conditional search and extract information

Hi, I am in much need of help here.

I have a tall array (40million rows)

Column1 (Customer ID) - there are 2000 customers for each Date sample

Column 2 (Date sample, based on half hour) - i.e. 2019-12-31 00:30:00, 2019-12-31 01:30:00,

Column 5 (Price)

I need to find the highest price for each Customer within 1 day (i.e. within 48 date samples)

then I need to write the selected columns to csv file.

I have been trying loops without luck :(

The code below seems to accumulate into bins based on sample

ds = datastore('File.csv');
tt = tall(ds);
tt = rmmissing(tt);
g=findgroups(tt.datesample); %this is selecting based on sample not per day ?
bin = splitapply(@(v, d) {sum(v), d(1)}, tt.output, tt.datesample, g); %can sum be replaced with max?
gatherbin = gather(bin);
tablebin = cell2table(gatherbin);
writetable(tablebin,'newfile.csv');

[dpb]

I've not worked with tall arrays "in anger" so not too sure about what does/doesn't work correctly with ordinary syntax.

But, I'd try to create a time table then group by customer. Then retime with aggregation function max should be what you're looking for...

Answer 1

[Guillaume]

It should be as simple as:

highestprice = gather(groupsummary(tt, [1, 2], {'none', 'day'}, 'max', 5))  %you can replace 1, 2, and 5 by actual variable names. This would make the code clearer

[George Kharoufeh]

I have tried this however I am getting the following error, to do with date. any thoughts ?

Thank you so much for your help,

Error using tall/horzcat (line 21)
Invalid combination of data types for concatenation: datetime double.

Error in test2 (line 11)
highestprice = gather(groupsummary(tt, [tt.A_ID, tt.B_TI], {'none', 'day'}, 'max',
tt.E_GE))

[Guillaume]

Probably:

highestprice = gather(groupsummary(tt, {'A_ID', 'B_TI'}, {'none', 'day'}, 'max',
'E_GE'))

You pass the names of the variables, not the variables themselves.

[George Kharoufeh]

Thank you however the new error is:

Error using Groupsummary

First Argument must be a table or a timetable. (i have a tall table)

Would really appreciate your response.

I also tried (without luck)

highestprice = gather(groupsummary(tt, {'tt.A_ID', 'tt.B_TI'}, {'none', 'day'}, 'max',
'tt.E_GE'))

[Guillaume]

There's only one release where groupsummary doesn't work with tall arrays: R2018a. Before that groupsummary didn't exist, and in R2018b the support for tall array was added. Note that there's a field next to your question for you to put the version.

Again, for groupsummary you have to pass table variable names or indices, so neither 'tt.E_GE' nor tt.E_GE would work. 'E_GE' or 5 (if E_GE is the 5th variable) would.

In R2018a, you will have to use splitapply indeed. You have to create the day bins first:

daybin = discretize(tt.B_TI, 'day');
[group, day, customer] = findgroups(daybin, tt.A_ID);
highestprice = splitapply(@max, tt.E_GE, group);
result =  gather(table(day, customer, highestprice))

Answer 2

[George Kharoufeh]

Thank you so much