6
$\begingroup$

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]
$\endgroup$
4
$\begingroup$

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

enter image description here

| improve this answer | |
$\endgroup$
2
$\begingroup$
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]]

enter image description here

| improve this answer | |
$\endgroup$
0
$\begingroup$

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.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.