Create tree association using table

Question

Suppose I have a table like the following (This example is simplified, actual table is much larger), it is read from external files:

table = {{1, A, x, y},
         {1, B, z, w},
         {2, A, v, u},
         {2, B, t, s}};

header = {i, j, g, h};

The third and fourth column are the actual content of my table, say their headers are g and h, and the first and second column are like indices for each row.

The output I am looking for is

tree = <|1 -> <|A -> <|g -> x, h -> y|>,
                B -> <|g -> z, h -> w|>|>,
         2 -> <|A -> <|g -> v, h -> u|>,
                B -> <|g -> t, h -> s|>|>|>;

The reason behind this is that a regular table of association (give the first and second column header i and j) takes too long to find the rows using Select (8800 sec for the actual table). This might speed up things quite a bit because it is a tree structure in nature. So if I want to access one particular row, I just use the command tree[1][A] or tree[1][A][g] to get the content of the first row.

So how would you code this into Mathematica? I am open to completely different approaches as well. Thanks!

Answer 1

1. less general

   # -> <|#2 -> <|g -> #3, h -> #4|>|> & @@@ table // Merge[Association]
   

      <|
        1 -> <|A -> <|g -> 5, h -> y|>, B -> <|g -> z, h -> w|>|>, 
        2 -> <|A -> <|g -> v, h -> u|>, B -> <|g -> t, h -> s|>|>
   |>
   

2. more general

   newAsso = AssociationThread[header, #] & /@ table
   

    {<|i -> 1, j -> A, g -> x, h -> y|>, 
     <|i -> 1, j -> B, g -> z, h -> w|>, 
     <|i -> 2, j -> A, g -> v, h -> u|>, 
      <|i -> 2, j -> B,  g -> t, h -> s|>}
   

    nested = GroupBy[newAsso, {Key@i, Key@j}, Map[KeyDrop[{i, j}]@*First]]
   

   <|
      1 -> <|A -> <|g -> x, h -> y|>, B -> <|g -> z, h -> w|>|>, 
      2 -> <|A -> <|g -> v, h -> u|>, B -> <|g -> t, h -> s|>|>
   |>
   

   nested[2, A]

   <|g -> v, h -> u|>
   

3. alternatively

   GroupBy[
      table, 
      {#[[1]] &, #[[2]] & -> (AssociationThread[{g, h}, #[[3 ;;]]] &)}, 
      Map[First]
   ]
   

4. or

    GroupBy[
       table, 
       {First -> Rest, First -> (AssociationThread[{g, h}, Rest[#]] &)}, 
       Map[First]
    ]