Dataset: best practices for unordered hierarchical data

Question

Note: the editor warns that this is a "subjective" post, but I am looking for an objective answer (e.g. in relation to easy of use of the many built-in functions, speed, etc).

Suppose you have a dataset which contains some hierarchical form and many other values which could be grouped by for analysis (copy paste into a notebook):

ds=Dataset[
 <|"Nest1_1" -> <|"Nest2_1" ->
     <|
      "Nest3_1" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>
        |>,
      "Nest3_2" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, 
           "c" -> 7|>|>
        |>
      |>,
    "Nest2_2" ->
     <|
      "Nest3_1" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>
        |>,
      "Nest3_2" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, 
           "c" -> 7|>|>
        |>
      |>

    |>,
  "Nest1_2" -> <|"Nest2_1" ->
     <|
      "Nest3_1" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>
        |>,
      "Nest3_2" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, 
           "c" -> 7|>|>
        |>
      |>,
    "Nest2_2" ->
     <|
      "Nest3_1" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>
        |>,
      "Nest3_2" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>
        |>,
      "Nest3_3" ->
       <|
        <|"Nest4_1" -> <|"a" -> 1, "b" -> 4, "c" -> 7|>|>,
        <|"Nest4_2" -> <|"a" -> 1, "b" -> 4, 
           "c" -> 7|>|>
        |>
      |>

    |>
  |>]

which could also be flattened like:

Dataset[{
  <|"Nest1" -> 1, "Nest2" -> 1, "Nest3" -> 1, "Nest4" -> 1, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 1, "Nest2" -> 1, "Nest3" -> 1, "Nest4" -> 2, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 1, "Nest2" -> 1, "Nest3" -> 2, "Nest4" -> 1, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 1, "Nest2" -> 1, "Nest3" -> 2, "Nest4" -> 2, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 1, "Nest2" -> 2, "Nest3" -> 2, "Nest4" -> 1, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 1, "Nest2" -> 2, "Nest3" -> 2, "Nest4" -> 2, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 2, "Nest2" -> 2, "Nest3" -> 2, "Nest4" -> 1, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 2, "Nest2" -> 2, "Nest3" -> 2, "Nest4" -> 2, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 2, "Nest2" -> 2, "Nest3" -> 3, "Nest4" -> 1, "a" -> 1, 
   "b" -> 4, "c" -> 7|>,
  <|"Nest1" -> 2, "Nest2" -> 2, "Nest3" -> 3, "Nest4" -> 2, "a" -> 1, 
   "b" -> 4, "c" -> 7|>
  }]

Further lets pretend:

• "Nest1" is some sort of condition (e.g. plant species)
• "Nest2" is some sort of variable (e.g. given sunlight or not)
• "Nest3" is some other sort of variable (e.g. amount watered)
• "Nest4" is replicates of some measurement
• "a","b","c" are some measurements (e.g. height, flowers, etc)

Which structure and why would be objectively better (in regards to leveraging the mechanics of the dataset object) for say comparing the average "height" ("a") across the two species ("Nest1") (without considering nests 2 and 3), as well as plotting the average number of flowers ("b") for each plant ("nest1") across each amount of water given ("nest3")?

Can example syntax be provided demonstrating the most native way to do this.

Answer 1

I support @alancalvitti observations. I've done loads of work with Al and will follow up his specific comments with the following. Al and I have worked on very large datasets (500G RAM), the kind and size one doesn't want to query SQL for many reasons. The first problem is never the 'shape' of the dataset it's the wrangling required of source data into a dataset one can understand and subsequently use efficiently for the intended purposes. Over the last couple years Al and I have zeroed in on a method of 'stepping' from loading source data that is not trusted and not knowable to a dataset that can be trusted and knowable.

When one has a dataset of hundreds of keys, sometimes 12 to 15 levels deep, with nearly 1B values, jagged, and missing keys and values all over the place, and one wants to generate a model... well... The long and short of my experience of using Dataset and Association on those scales is, once you get the wrangling right, a large query on a large trusted dataset is shockingly efficient. I think it's all in the data preparation for the purpose of analysis. What does the 'shape' the data need to be to accomplish the analysis? I guess this is a long way of saying, I think there is no optimal dataset 'shape' , but there is an optimal 'shape' an analysis wants the data to be. So, going back to my first comments, wrangle the data to a state of trustworthiness, then shape the data to a specific analysis. Along the way you will have several iterations 'steps' the data will go through to accomplish your need.