title: DataFrame to nested JSON
body: <p><strong>I am new to JSON formatted files.</strong> </p>
  
  <p>I have a Pandas DataFrame:</p>
  
  <pre><code>import pandas as pd
  
  df = pd.DataFrame([["A", "2014/01/01", "2014/01/02", "A", -0.0061, "A"],
                     ["A", "2015/07/11", "2015/08/21", "A", 1.50, "A"],
                     ["C", "2016/01/01", "2016/01/05", "U", 2.75, "R"],
                     ["D", "2013/05/19", "2014/09/30", "Q", -100.0, "N"],
                     ["B", "2015/08/22", "2015/09/01", "T", 10.0, "R"]],
                     columns=["P", "Start", "End", "Category", "Value", "Group"]
                   ) 
  </code></pre>
  
  <p>That looks like this</p>
  
  <pre><code>   P       Start         End Category     Value Group
  0  A  2014/01/01  2014/01/02        A   -0.0061     A
  1  A  2015/07/11  2015/08/21        A    1.5000     A
  2  C  2016/01/01  2016/01/05        U    2.7500     R
  3  D  2013/05/19  2014/09/30        Q -100.0000     N
  4  B  2015/08/22  2015/09/01        T   10.0000     R
  </code></pre>
  
  <p>I know that I could convert this to JSON via:</p>
  
  <pre><code>df.to_json("output.json")
  </code></pre>
  
  <p>But I need to convert it to a nested JSON format like this:</p>
  
  <pre><code>{
    "group_list": [
      {
        "category_list": [
          {
            "category": "A",
            "p_list": [
              {
                "p": "A",
                "date_list": [
                  {
                    "start": "2014/01/01",
                    "end": "2014/01/02",
                    "value": "-0.0061"
                  }
                ]
              },
              {
                "p": "A",
                "date_list": [
                  {
                    "start": "2015/07/11",
                    "end": "2015/08/21",
                    "value": "1.5000"
                  }
                ]
              }
            ]
          }
        ],
        "group": "A"
      },
      {
        "category_list": [
          {
            "category": "U",
            "p_list": [
              {
                "p": "C",
                "date_list": [
                  {
                    "start": "2016/01/01",
                    "end": "2016/01/05",
                    "value": "2.7500"
                  }
                ]
              }
            ]
          },
          {
            "category": "T",
            "p_list": [
              {
                "p": "B",
                "date_list": [
                  {
                    "start": "2015/08/22",
                    "end": "2015/09/01",
                    "value": "10.000"
                  }
                ]
              }
            ]
          }
        ],
        "group": "R"
      },
      {
        "category_list": [
          {
            "category": "Q",
            "p_list": [
              {
                "p": "D",
                "date_list": [
                  {
                    "start": "2013/05/19",
                    "end": "2014/09/30",
                    "value": "-100.0000"
                  }
                ]
              }
            ]
          }
        ],
        "group": "N"
      }
    ]
  }
  </code></pre>
  
  <p>I've considered using Pandas' <code>groupby</code> functionality but I can't quite figure out how I could then get it into the final JSON format. Essentially, the nesting begins with grouping together rows with the same "group" and "category" columns. Afterwards, it is a matter of listing out the rows. I could write some code with nested for-loops but I'm hoping that there is a more efficient way to accomplish this.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I can also manipulate my DataFrame via:</p>
  
  <pre><code>df2 = df.set_index(['Group', 'Category', 'P']).stack()
  
  Group  Category  P       
      A      A         A  Start    2014/01/01
                          End      2014/01/02
                          Value       -0.0061
                          Start    2015/07/11
                          End      2015/08/21
                          Value           1.5
      R      U         C  Start    2016/01/01
                          End      2016/01/05
                          Value          2.75
      N      Q         D  Start    2013/05/19
                          End      2014/09/30
                          Value          -100
      R      T         B  Start    2015/08/22
                          End      2015/09/01
                          Value            10
  </code></pre>
  
  <p>which is close to where I need to be but I don't think one could call <code>df2.to_json()</code> in this case.</p>