title: How to generate n-level hierarchical JSON from pandas DataFrame?
body: <p>Is there an efficient way to create hierarchical JSON (n-levels deep) where the parent values are the keys and not the variable label? i.e:</p>
  
  <pre><code>{"2017-12-31":
      {"Junior":
          {"Electronics":
              {"A":
                  {"sales": 0.440755
                  }
              },
              {"B":
                  {"sales": -3.230951
                  }
              }
          }, ...etc...
      }, ...etc...
  }, ...etc... 
  </code></pre>
  
  <hr>
  
  <p><strong>1. My testing DataFrame:</strong></p>
  
  <pre><code>colIndex=pd.MultiIndex.from_product([['New York','Paris'],
                                       ['Electronics','Household'],
                                       ['A','B','C'],
                                       ['Junior','Senior']],
                                 names=['City','Department','Team','Job Role'])
  
  rowIndex=pd.date_range('25-12-2017',periods=12,freq='D')
  
  df1=pd.DataFrame(np.random.randn(12, 24), index=rowIndex, columns=colIndex)
  df1.index.name='Date'
  df2=df1.resample('M').sum()
  df3=df2.stack(level=0).groupby('Date').sum()
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/F8CSK.png" rel="noreferrer"><img src="https://i.stack.imgur.com/F8CSK.png" alt="Source DataFrame"></a></p>
  
  <hr>
  
  <p><strong>2. Transformation I'm making as it seems to be the most logical structure to build the JSON from:</strong></p>
  
  <pre><code>df4=df3.stack(level=[0,1,2]).reset_index() \
      .set_index(['Date','Job Role','Department','Team']) \
      .sort_index()
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/is3RK.png" rel="noreferrer"><img src="https://i.stack.imgur.com/is3RK.png" alt="Transformed DataFrame"></a></p>
  
  <hr>
  
  <p><strong>3. My attempts-so-far</strong></p>
  
  <p>I came across this <a href="https://stackoverflow.com/a/40490276/3972769">very helpful SO question</a> which solves the problem for one level of nesting using code along the lines of:</p>
  
  <pre><code>j =(df.groupby(['ID','Location','Country','Latitude','Longitude'],as_index=False) \
      .apply(lambda x: x[['timestamp','tide']].to_dict('r'))\
      .reset_index()\
      .rename(columns={0:'Tide-Data'})\
      .to_json(orient='records'))
  </code></pre>
  
  <p>...but I can't find a way to get nested <code>.groupby()</code>s working:</p>
  
  <pre><code>j=(df.groupby('date', as_index=True).apply(
      lambda x: x.groupby('Job Role', as_index=True).apply(
          lambda x: x.groupby('Department', as_index=True).apply(
              lambda x: x.groupby('Team', as_index=True).to_dict())))  \
                  .reset_index().rename(columns={0:'sales'}).to_json(orient='records'))
  </code></pre>