title: PySpark: How to create a nested JSON from spark data frame?
body: <p>I am trying to create a nested json from my spark dataframe which has data in following structure. The below code is creating a simple json with key and value. Could you please help</p>
  
  <pre><code>df.coalesce(1).write.format('json').save(data_output_file+"createjson.json", overwrite=True)
  </code></pre>
  
  <p>Update1:
  As per @MaxU answer,I converted the spark data frame to pandas and used group by. It is putting the last two fields in a nested array. How could i first put the category and count in nested array and then inside that array i want to put subcategory and count. </p>
  
  <p>Sample text data:</p>
  
  <pre><code>Vendor_Name,count,Categories,Category_Count,Subcategory,Subcategory_Count
  Vendor1,10,Category 1,4,Sub Category 1,1
  Vendor1,10,Category 1,4,Sub Category 2,2
  Vendor1,10,Category 1,4,Sub Category 3,3
  Vendor1,10,Category 1,4,Sub Category 4,4
  
  j = (data_pd.groupby(['vendor_name','vendor_Cnt','Category','Category_cnt'], as_index=False)
               .apply(lambda x: x[['Subcategory','subcategory_cnt']].to_dict('r'))
               .reset_index()
               .rename(columns={0:'subcategories'})
               .to_json(orient='records'))
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/vuJf7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vuJf7.png" alt="enter image description here"></a></p>
  
  <pre><code>[{
          "vendor_name": "Vendor 1",
          "count": 10,
          "categories": [{
              "name": "Category 1",
              "count": 4,
              "subCategories": [{
                      "name": "Sub Category 1",
                      "count": 1
                  },
                  {
                      "name": "Sub Category 2",
                      "count": 1
                  },
                  {
                      "name": "Sub Category 3",
                      "count": 1
                  },
                  {
                      "name": "Sub Category 4",
                      "count": 1
                  }
              ]
          }]
  </code></pre>