# Create Nested List from tabular data

Im trying to create a nested list from tabular data, then onward into a nested association. I can get part way with the help of this answer but it only works for the first level. Repeated use of this approach doesn't seem to work.

list=  {{"Category", "Detail", "Value"}, {"Cat1", "detail1", 23},
{"Cat1", "detail2", 27}, {"Cat1", "detail3", 13}, {"Cat2", "detail4",15},
{"Cat2", "detail5", 35}, {"Cat3", "Detail6", 56}};

list // TableForm

(*  tabular data result *)


First level of data.

Thread[list[[2 ;;, 2]] -> #] & /@ {list[[2 ;;, 3]]} // ToAssociations

{<|detail1->23,detail2->27,detail3->13,detail4->15,detail5->35,Detail6->56|>}


Required result

{<| Cat1-> <|detail1->23,detail2->27,detail3->13|>|>,
<| Cat2-> <|detail4->15,detail5->35|>|>,
<| Cat3-> <|Detail6->56|>|>}


Update is it possible to come up with something that works for N levels of nesting?

Eg 3 levels of nesting

list = {{"BigCat", "Category", "Detail", "Value"}, {"BigCat", "Cat1",
"detail1", 23}, {"BigCat", "Cat1", "detail2", 27}, {"BigCat",
"Cat1", "detail3", 13}, {"BigCat", "Cat2", "detail4",
15}, {"BigCat", "Cat2", "detail5", 35}, {"LittleCat", "Cat3",
"Detail6", 56}}

• For your new updated list you can use GroupBy[Rest@list, First -> Rest, GroupBy[#, First -> Rest, Association[Rule @@@ (#)] &] &]. This could be extended to an arbitrary number of levels by using Nest. Jun 22, 2015 at 19:32
• Related: (83507) Jun 23, 2015 at 11:45

From my answer to Handy / flexible directory tree operations with minor modification:

fn[x_List] := GroupBy[x, First -> Rest, fn]

fn[{{x_}}] := x


Tested on your two lists, named list1 and list2 respectively:

fn[Rest @ list1]

<|"Cat1" -> <|"detail1" -> 23, "detail2" -> 27, "detail3" -> 13|>,
"Cat2" -> <|"detail4" -> 15, "detail5" -> 35|>, "Cat3" -> <|"Detail6" -> 56|>|>

fn[Rest @ list2]

<|"BigCat" -> <|"Cat1" -> <|"detail1" -> 23, "detail2" -> 27, "detail3" -> 13|>,
"Cat2" -> <|"detail4" -> 15, "detail5" -> 35|>|>,
"LittleCat" -> <|"Cat3" -> <|"Detail6" -> 56|>|>|>

• Should have thought about that ! Terse and much more readable. Jun 23, 2015 at 12:46
• could you just give me a hint on what the overloading of fn enables? Does it enable the recursion for list2 or is it just a dummy reduction function for GroupBy? Jun 23, 2015 at 13:43
• @Gordon I may mistake what you mean by "overloading" -- the use of fn within GroupBy indeed enables recursion. The definition fn[{{x_}}] := x provides a termination parameter for the recursion. Jun 23, 2015 at 13:55
• @Mr.Wizard I think you understood me first time. :) Essentially overloading in this instance is providing multiple definitions of the same function, that differ by type. Jun 23, 2015 at 14:53
• @Gordon Okay. Thanks for the Accept! Jun 23, 2015 at 22:44

I guess this is what you want. Using GroupBy and then combining them as Association will do the trick.

GroupBy[Rest@list, First -> Rest, Association[Rule @@@ #] &]


Resulting in

{<| Cat1-> <|detail1->23,detail2->27,detail3->13|>|>,
<| Cat2-> <|detail4->15,detail5->35|>|>,
<| Cat3-> <|Detail6->56|>|>}

• This is spot on however I've moved the goal posts slightly (sorry). Is it possible to do it for N levels of nesting? Jun 22, 2015 at 18:57

If you need a method for any levels of nesting (as mentionned in your update), maybe try something like this:

groupbyNest[x_List] :=
GroupBy[x, First -> Rest, groupbyNest] /; Last@Dimensions@x > 1

groupbyNest[x_List] := First@Flatten@x /; Last@Dimensions@x == 1


Then for example, given

list = {{"BigCat", "Category", "Detail", "Value"}, {"BigCat", "Cat1",
"detail1", 23}, {"BigCat", "Cat1", "detail2", 27}, {"BigCat",
"Cat1", "detail3", 13}, {"BigCat", "Cat2", "detail4",
15}, {"BigCat", "Cat2", "detail5", 35}, {"LittleCat", "Cat3",
"Detail6", 56}}


just

groupbyNest@Rest@list


returns

<|"BigCat" -> <|"Cat1" -> <|"detail1" -> 23, "detail2" -> 27, "detail3" -> 13|>, "Cat2" -> <|"detail4" -> 15, "detail5" -> 35|>|>, "LittleCat" -> <|"Cat3" -> <|"Detail6" -> 56|>|>|>

• You have my vote for using the same method I did. I hope you don't mind that I also posted an answer. I feel justified in doing so as I used the method before and I also chose a slightly more terse definition. Jun 23, 2015 at 12:17
• @Mr.Wizard That's fine ! Did not know about your previous post about that. Actually here, I was wondering how to make the answer as general as possible, in particular concerning the last step when there is no need to GroupBy anymore. In the OP's example the last elements are just numbers (23, 56, ...) but it could be anything like a list ({23,56,...}), ... or a list of lists of lists ...(which is not handled by my approach because of the Flatten). Jun 23, 2015 at 12:45

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.