Sorting list on multiple criteria based on means of subgroups

Question

I have a set of records where each record is for a particular item. An item is a member of a particular subgroup and a subgroup is a member of a particular group. One item is always a member of a single subgroup and a single subgroup can only be a member of a single group. The sample data can look like this (note that the last column of each record can be any number, in the test code below i just replace it with a random integer.

testdata = {{"Item 1", "Subgroup 1", "Group 1", 1}, {"Item 2", 
    "Subgroup 1", "Group 1", 1}, {"Item 3", "Subgroup 1", "Group 1", 
    1}, {"Item 4", "Subgroup 2", "Group 1", 1}, {"Item 5", 
    "Subgroup 3", "Group 1", 1}, {"Item 6", "Subgroup 3", "Group 1", 
    1}, {"Item 7", "Subgroup 4", "Group 2", 1}, {"Item 8", 
    "Subgroup 5", "Group 2", 1}, {"Item 9", "Subgroup 6", "Group 3", 
    1}, {"Item 10", "Subgroup 7", "Group 4", 1}, {"Item 11", 
    "Subgroup 7", "Group 4", 1}, {"Item 12", "Subgroup 8", "Group 4", 
    1}};

I would like to sort the testdata list by the average of the group and within each group by the average of each subgroup.

I have created the code below to do that. What it does is to first gather the data by group name and then within each gathered group, gather by subgroup name. Then I generate means of each subgroup within the group and get its proper order, generate means accross the groups and get ordering and then apply that ordering using MapThread. The code seems to work but I am not sure if it is correct, for example, i assume that GatherBy will always generate the same order of generated groups from the same data. Moreover i guess that there might be a much more elegant and simple solution.

Any suggestions to make this better, more elegant and simpler? (Ideally easily extensible to more levels of grouping)

testdata = {{"Item 1", "Subgroup 1", "Group 1", 1}, {"Item 2", 
    "Subgroup 1", "Group 1", 1}, {"Item 3", "Subgroup 1", "Group 1", 
    1}, {"Item 4", "Subgroup 2", "Group 1", 1}, {"Item 5", 
    "Subgroup 3", "Group 1", 1}, {"Item 6", "Subgroup 3", "Group 1", 
    1}, {"Item 7", "Subgroup 4", "Group 2", 1}, {"Item 8", 
    "Subgroup 5", "Group 2", 1}, {"Item 9", "Subgroup 6", "Group 3", 
    1}, {"Item 10", "Subgroup 7", "Group 4", 1}, {"Item 11", 
    "Subgroup 7", "Group 4", 1}, {"Item 12", "Subgroup 8", "Group 4", 
    1}};
testdata[[All, -1]] = RandomInteger[5, Length@testdata[[All, -1]]]
testdatag = (GatherBy[#, #[[2]] &] & /@ 
    GatherBy[testdata, #[[3]] &]);
subgrouporder = 
  Ordering /@ Map[Mean, testdatag[[All, All, All, 4]], {2}];
grouporder = 
  Ordering@Map[Mean[Flatten[#]] &, testdatag[[All, All, All, 4]], {1}];
MapThread[
  Function[{u, v}, u[[v]]], {testdatag, subgrouporder}][[grouporder]]
Flatten[%,2]

Answer 1

Convert to association:

adata = AssociationThread[{"Item", "Subgroup", "Group", "Value"} -> #] & /@ testdata

Means of subgroups within groups:

set = 
 KeyMap[Values]@Sort@Query[All, Mean]@
        GroupBy[KeyTake[{"Group", "Subgroup"}] -> Key["Value"]]@adata;

set // Dataset

Answer 2

testdata = {{"Item 1", "Subgroup 1", "Group 1", 1}, {"Item 2", 
    "Subgroup 1", "Group 1", 1}, {"Item 3", "Subgroup 1", "Group 1", 
    1}, {"Item 4", "Subgroup 2", "Group 1", 1}, {"Item 5", 
    "Subgroup 3", "Group 1", 1}, {"Item 6", "Subgroup 3", "Group 1", 
    1}, {"Item 7", "Subgroup 4", "Group 2", 1}, {"Item 8", 
    "Subgroup 5", "Group 2", 1}, {"Item 9", "Subgroup 6", "Group 3", 
    1}, {"Item 10", "Subgroup 7", "Group 4", 1}, {"Item 11", 
    "Subgroup 7", "Group 4", 1}, {"Item 12", "Subgroup 8", "Group 4", 
    1}};

SeedRandom[777]
testdata[[All, -1]] = RandomInteger[5, Length@testdata[[All, -1]]];

Construct a dataset from testdata:

ds = Dataset[AssociationThread[{"Item", "Subgroup", "Group", "Value"} -> #] & /@
     testdata];

Use GroupBy to construct a query:

query = GroupBy[KeyTake[{"Subgroup", "Group"}] -> 
    KeyTake@{"Subgroup", "Group", "Value"}] /* Map[Mean];

Use query with ds and take Values:

ds2 = Values @ ds[query];

Row[{ds, ds2}, Spacer[10]]

Answer 3

You may use Query with GroupBy and SortBy,

After adding random values.

SeedRandom[123]
testdata[[All, -1]] = RandomInteger[10, Length@testdata];

then

Query[
   GroupBy[#[[3]] &] /* SortBy[Mean@#[[All, -1]] &] /* Values,
   GroupBy[#[[2]] &] /* SortBy[Mean@#[[All, -1]] &] /* Values
   ]@testdata // Flatten[#, 2] &

{{"Item 5", "Subgroup 3", "Group 1", 0}, 
 {"Item 6", "Subgroup 3", "Group 1", 2}, 
 {"Item 1", "Subgroup 1", "Group 1", 7}, 
 {"Item 2", "Subgroup 1", "Group 1", 4}, 
 {"Item 3", "Subgroup 1", "Group 1", 10}, 
 {"Item 4", "Subgroup 2", "Group 1", 10}, 
 {"Item 7", "Subgroup 4", "Group 2", 6}, 
 {"Item 8", "Subgroup 5", "Group 2", 7}, 
 {"Item 10", "Subgroup 7", "Group 4", 8}, 
 {"Item 11", "Subgroup 7", "Group 4", 3}, 
 {"Item 12", "Subgroup 8", "Group 4", 9}, 
 {"Item 9", "Subgroup 6", "Group 3", 9}}

testdata is GroupBy'ed position 3 that contains the group id, these groups are SortBy'ed the Mean of the values in the last position (-1), and finally the group Values (the groups of test data) are returned. The same is performed on the subgroups of the groups of test data using position 2 which holds the subgroup id. Then to unravel the nested list from grouping the result is Flattened two levels; due to two levels of grouping.

Hope this helps.