In general, the transformation you ask for can be done, for example, along the lines I outlined in my answer on Associations (section on example database).
Transforming to a list of associations
To use the solution from tha post verbatim (more or less), we will need an auxiliary function to transform your data to a list of associations first:
toAssocList =
Composition[
Map[Association],
Transpose,
Map[Thread[First[#] -> Rest[#]] &],
Transpose
]
so that
assocs = toAssocList@list
(*
{
<|"BigCat" -> "BigCat", "Category" -> "Cat1", "Detail" -> "detail1", "Value" -> 23|>,
<|"BigCat" -> "BigCat", "Category" -> "Cat1", "Detail" -> "detail2", "Value" -> 27|>,
<|"BigCat" -> "BigCat", "Category" -> "Cat1", "Detail" -> "detail3", "Value" -> 13|>,
<|"BigCat" -> "BigCat", "Category" -> "Cat2", "Detail" -> "detail4", "Value" -> 15|>,
<|"BigCat" -> "BigCat", "Category" -> "Cat2", "Detail" -> "detail5", "Value" -> 35|>,
<|"BigCat" -> "LittleCat", "Category" -> "Cat3", "Detail" -> "Detail6", "Value" -> 56|>
}
*)
Transformation to the hierarchical structure
Using the function pushUpNested
from the mentioned post, we can generate the corresponding tramnsformation:
transform = pushUpNested[{{"BigCat"}, {"Category"}, {"Detail"},{"Value"}}]
(*
Map[
Map[
Map[Map[Identity]@*GroupBy[#1[[Sequence[Key["Value"]]]] &]]@*
GroupBy[#1[[Sequence[Key["Detail"]]]] &]
]@*
GroupBy[#1[[Sequence[Key["Category"]]]] &]
]@*
GroupBy[#1[[Sequence[Key["BigCat"]]]] &]
which we can use:
transform@assocs
(* <|"BigCat" -> <|"Cat1" -> <|"detail1" -> <|23 -> {<|"BigCat" -> "BigCat", "Category" -> "Cat1", "Detail" -> "detail1",
"Value" -> 23|>}|>,
"detail2" -> <|27 -> {<|"BigCat" -> "BigCat",
"Category" -> "Cat1", "Detail" -> "detail2",
"Value" -> 27|>}|>,
"detail3" -> <|13 -> {<|"BigCat" -> "BigCat",
"Category" -> "Cat1", "Detail" -> "detail3",
"Value" -> 13|>}|>|>,
"Cat2" -> <|"detail4" -> <|15 -> {<|"BigCat" -> "BigCat",
"Category" -> "Cat2", "Detail" -> "detail4",
"Value" -> 15|>}|>,
"detail5" -> <|35 -> {<|"BigCat" -> "BigCat",
"Category" -> "Cat2", "Detail" -> "detail5",
"Value" -> 35|>}|>|>|>,
"LittleCat" -> <|"Cat3" -> <|"Detail6" -> <|56 -> {<|"BigCat" ->
"LittleCat", "Category" -> "Cat3", "Detail" -> "Detail6",
"Value" -> 56|>}|>|>|>|>
*)
Cleaning up
It remains now to remove the "leafs" in this construction (the lowest-level values):
Map[First@*Keys, transform@assocs, {3}]
(*
<|"BigCat" ->
<|
"Cat1" -> <|"detail1" -> 23, "detail2" -> 27,"detail3" -> 13|>,
"Cat2" -> <|"detail4" -> 15, "detail5" -> 35|>
|>,
"LittleCat" -> <|"Cat3" -> <|"Detail6" -> 56|>|>
|>
*)
Notes
I don't claim this solution is the most efficient, but I think it illustrates a pretty general scheme, and a different, higher-level way to make such transformations - through transform generators. This can be used for much more complex cases. In fact, as I noted in the linked answer, Dataset
is using a similar (but much more complex) scheme to generate queries.
GroupBy[Rest@list, First -> Rest, GroupBy[#, First -> Rest, Association[Rule @@@ (#)] &] &]
. This could be extended to an arbitrary number of levels by usingNest
. $\endgroup$