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}}
$\endgroup$
  • 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$ – Karsten 7. Jun 22 '15 at 19:32
  • $\begingroup$ Related: (83507) $\endgroup$ – Mr.Wizard Jun 23 '15 at 11:45
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|>|>|>
$\endgroup$
  • $\begingroup$ Should have thought about that ! Terse and much more readable. $\endgroup$ – SquareOne Jun 23 '15 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$ – Gordon Coale Jun 23 '15 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$ – Mr.Wizard Jun 23 '15 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$ – Gordon Coale Jun 23 '15 at 14:53
  • $\begingroup$ @Gordon Okay. Thanks for the Accept! $\endgroup$ – Mr.Wizard Jun 23 '15 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|>|>}
$\endgroup$
  • $\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$ – Gordon Coale Jun 22 '15 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|>|>|>

$\endgroup$
  • $\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$ – Mr.Wizard Jun 23 '15 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$ – SquareOne Jun 23 '15 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.

$\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.