9
$\begingroup$

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}}
Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ 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. $\endgroup$ Commented Jun 22, 2015 at 19:32
  • $\begingroup$ Related: (83507) $\endgroup$ Commented Jun 23, 2015 at 11:45

4 Answers 4

Reset to default
7
$\begingroup$

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|>|>|>
Improve this answer
$\endgroup$
5
  • $\begingroup$ Should have thought about that ! Terse and much more readable. $\endgroup$ Commented Jun 23, 2015 at 12:46
  • $\begingroup$ 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? $\endgroup$ Commented Jun 23, 2015 at 13:43
  • $\begingroup$ @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. $\endgroup$ Commented Jun 23, 2015 at 13:55
  • $\begingroup$ @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. $\endgroup$ Commented Jun 23, 2015 at 14:53
  • $\begingroup$ @Gordon Okay. Thanks for the Accept! $\endgroup$ Commented Jun 23, 2015 at 22:44
6
$\begingroup$

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|>|>}
Improve this answer
$\endgroup$
1
  • $\begingroup$ This is spot on however I've moved the goal posts slightly (sorry). Is it possible to do it for N levels of nesting? $\endgroup$ Commented Jun 22, 2015 at 18:57
4
$\begingroup$

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|>|>|>

Improve this answer
$\endgroup$
2
  • $\begingroup$ 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. $\endgroup$ Commented Jun 23, 2015 at 12:17
  • $\begingroup$ @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). $\endgroup$ Commented Jun 23, 2015 at 12:45
3
$\begingroup$

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.

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.