# Optimize grouping of list of lists by elements

I have a sorted list of lists of size ~200,000 x 3. The data are such that the first element of each triplet loops through i = 1, 1.1, 1.2, ..., 199.9, 200, and for every value of i, the second element loops through all integer values 0-99, inclusive. The third elements are the real "data". I want to group every 100 triplets for each value of i to perform operations like Total and Min on the third elements; i.e. to do:

Total[{{i,0,data0},{i,1,data1},...,{i,99,data99}}][[3]]/100


for every value of i. The problem is that my data set is missing some points, and sometimes there aren't 100 triplets for each i.

I want my final output to look like:

{ {1, data1}, {1.1, data2}, {1.2, data3}, ..., {199.9, data1990}, {200, data1991} }

so I can plot them. If my data set was complete, I would simply use Table and iterate in steps of 100, performing Total and Min on every 100 elements. However, this won't work here so I tried two other approaches. They both get me the desired output, but the problem is that both are abysmally slow (~10min evaluation times). How might I optimize either of these attempts? Or, what is a better way to do this?

My data set is called testVCdata.

# ATTEMPT 1 (Position)

averagesTEST = Table[
{1 + i/10 // N,
Total@joinByCoup[1 + i/10 // N]/Length@positions[1 + i/10 // N]},
{i, 0, 1990}];
minsTEST = Table[
{1 + i/10 // N, Min@joinByCoup[1 + i/10 // N]},
{i, 0, 1990}];

positions[i_] := Position[testVCdata, {i, _, _}]
joinByCoup[i_] :=
testVCdata[[positions[i][[1, 1]] ;; positions[i][[-1, 1]], 3]]


# ATTEMPT 2 (GroupBy)

averagesTEST = Table[
{1 + i/10 // N,
Total@GroupBy[testVCdata, First][[i, All, 3]]/
Length@GroupBy[testVCdata, First][[i]]}
, {i, 0, 1990}];
minsTEST = Table[
{1 + i/10 // N, Min@GroupBy[testVCdata, First][[i, All, 3]]}
, {i, 0, 1990}];


Apologies in advance for clarity or formatting issues. I am quite new to SE so please let me know if there is something I should fix, thanks!

• Welcome to the Mathematica Stack Exchange. A concrete minimal example will get more focused responses. Consider tab = Table[{i, j, RandomChoice[Alphabet[], 3]}, {j, 1, 5}, {i, 1, 2, 0.1}]. Give us a table of this size to work with. Is it flattened as well? Choose other values and rearrange it to make it similar to your problem. Thanks.
– Syed
Nov 1, 2023 at 4:06
• Please verify that Map[Last,Map[Total,Split[testVCdata,#1[[2]]<#2[[2]]&]]] correctly totals each run of items. Please report how long this takes to run. I'm guessing WAY LESS than 10 minutes! That does depend on there not being so many missing items that the test I've written to detect the end of a run does not fail. If the result of this is incorrect then please tell me enough about the test case so that I can reproduce the failure here. Thanks
– Bill
Nov 1, 2023 at 5:59
• @Bill thank you so much! When used in conjunction with Thread and a Table to generate the "i" values, your solution is nearly instantaneous and gives the desired output. In order to use it with Min in place of Total, I changed it to this: Map[Min,Split[testVCdata, #1[[2]]<#2[[2]]&][[All, All, 3]]]. If you would like to post as a solution I will gladly accept it :) Nov 1, 2023 at 16:17
• Only nearly?, I need to do better than that ;} Given testVCdata of the form {i,j,data} are you looking for the mean and min for each value of i or for each value of j? Are your i values decimals like in your description or are they rationals like in your code? Do you want to present the i or j along with the mean or min? or just the mean or min?
– Bill
Nov 1, 2023 at 16:31
• @Bill given testVCdata of form {i,j,data}, I am looking for the Mean and Min of data over all j values that are associated with each i value, i.e. each value of i has a single mean and min value. My i values are decimals - I originally generated them with rationals and then appended //N to match the data set. This question is for the purpose of presenting means/mins alongside i values. I use j values for something else Nov 2, 2023 at 13:37