Is there a python function for creating a nested JSON file from a DF?

Keywords´╝Ü json pandas dataframe nested

Question: 

I'm trying to built a dataframe starting from the schema that the final JSON file should have.

The schema is the following:

[{"plant": "XXX",
  "at": 2019-05-03,
  "products": [{
          "product": "product1",
          "quantity": 4,
      }]

Plant should be a string, at should be a date ISO8601, product should be a string, quantity should be an integer.

I built the dataframe as follows:

Plant    At         Products    Product    Quantity
XXX    2019-05-03   products    Product1     4

And now I'm trying to generate the nested JSON file, but the code I'm using does not work as desired

j2 = (df2.groupby(['plant', 'at'], as_index=False)
    .apply(lambda x: x[['product', 'quantity']].to_dict('r'))
    .reset_index()
    .rename(columns={0: 'products'})
    .to_json(orient='columns'))

I get this JSON

{"plant":{"0":"XXX"},"at":{"0":"2019-06-07 12:53:13.983775"},"products":{"0":[{"product":"Product1","quantity":4}]}}

But actually it isn't the format I should generate.

Any suggestion?

Answers: