title: How to convert pandas Series to desired JSON format?
body: <p>I am having the following data on which I need to do apply aggregation function followed by groupby.</p>
  
  <p>My data is as follows: <code>data.csv</code></p>
  
  <pre><code>id,category,sub_category,count
  0,x,sub1,10
  1,x,sub2,20
  2,x,sub2,10
  3,y,sub3,30
  4,y,sub3,5
  5,y,sub4,15
  6,z,sub5,20
  </code></pre>
  
  <p>Here I'm trying to get the count by sub-category wise. After that I need to store the result in JSON format. The following piece of code helps me in achieving that. <code>test.py</code></p>
  
  <pre><code>import pandas as pd
  df = pd.read_csv('data.csv')
  sub_category_total = df['count'].groupby([df['category'], df['sub_category']]).sum()
  print sub_category_total.reset_index().to_json(orient = "records")
  </code></pre>
  
  <p>The above code gives me the following format.</p>
  
  <pre><code>[{"category":"x","sub_category":"sub1","count":10},{"category":"x","sub_category":"sub2","count":30},{"category":"y","sub_category":"sub3","count":35},{"category":"y","sub_category":"sub4","count":15},{"category":"z","sub_category":"sub5","count":20}]
  </code></pre>
  
  <p>But, my desired format is as follows:</p>
  
  <pre><code>{
  "x":[{
       "sub_category":"sub1",
       "count":10
       },
       {
       "sub_category":"sub2",
        "count":30}],
  "y":[{
       "sub_category":"sub3",
       "count":35
       },
       {
       "sub_category":"sub4",
       "count":15}],
  "z":[{
       "sub_category":"sub5",
        "count":20}]
  }
  </code></pre>
  
  <p>By following the discussions @ <a href="https://stackoverflow.com/questions/35473711/how-to-convert-pandas-dataframe-result-to-user-defined-json-format">How to convert pandas DataFrame result to user defined json format</a>, I replaced the last 2 lines of <code>test.py</code> with,</p>
  
  <pre><code>g = df.groupby('category')[["sub_category","count"]].apply(lambda x: x.to_dict(orient='records'))
  print g.to_json()
  </code></pre>
  
  <p>It gives me the following output.</p>
  
  <pre><code>{"x":[{"count":10,"sub_category":"sub1"},{"count":20,"sub_category":"sub2"},{"count":10,"sub_category":"sub2"}],"y":[{"count":30,"sub_category":"sub3"},{"count":5,"sub_category":"sub3"},{"count":15,"sub_category":"sub4"}],"z":[{"count":20,"sub_category":"sub5"}]}
  </code></pre>
  
  <p>Though the above result is somewhat similar to my desired format, I couldn't perform any aggregation function over here as it throws error saying <code>'numpy.int64' object has no attribute 'to_dict'</code>. Hence, I end up getting all of the rows in the data file.</p>
  
  <p>Can somebody help me in achieving the above JSON format?</p>