Python csv to Nested Json

Question

I have a csv available for which I need to create a nested Json. Where a Id has to be root and date in subroot then all the key and values. I have converted the csv to Json but Iam struggling to get it in required format. I have achieved until here in my code.

data = {} 
with open(csvpath, encoding='utf-8') as csvf: 
    csvReader = csv.DictReader(csvf)
    for rows in csvReader:
        key = rows['id'] 
        data[key] = rows

with open(jsonpath, 'w', encoding='utf-8') as jsonf: 
        jsonf.write(json.dumps(data, indent=4)) 

data.csv

"id","datetime","amount","orders","quantity"
31937,27/11/2020 00:00,55.8,2,2
31937,29/11/2020 00:00,96.9,1,1
26575,27/11/2020 00:00,10.9,1,1

output.json

{
    "31937": {
        "order_datetime": "27/11/2020 00:00",
        "amount": "55.8",
        "orders": "2",
        "quantity": "2"
    },
    "31937": {
        "order_datetime": "29/11/2020 00:00",
        "amount": "10.9",
        "orders": "1",
        "quantity": "1"
    },
    "29581": {
        "order_datetime": "27/11/2020 00:00",
        "amount": "96.9",
        "orders": "1",
        "quantity": "1"
    }
  }
}

output required

{
  "31937": {
    "27/11/2020 00:00": {
      "amount": 55.8,
      "orders": 2,
      "quantity": 2
    },
    "29/11/2020 00:00": {
      "amount": 96.9,
      "orders": 1,
      "quantity": 1
    }
  },
  "26575": {
    "27/11/2020 00:00": {
      "amount": 10.9,
      "orders": 1,
      "quantity": 1
    }
  }
}

Answer 1

Try:

df.groupby(['id'])[['datetime','amount', 'orders', 'quantity']]\
.apply(lambda x: x.set_index('datetime').to_dict(orient='index')).to_dict()

---

Output:

{26575: {'27/11/2020 00:00': {'amount': 10.9, 'orders': 1, 'quantity': 1}},
 31937: {'27/11/2020 00:00': {'amount': 55.8, 'orders': 2, 'quantity': 2},
  '29/11/2020 00:00': {'amount': 96.9, 'orders': 1, 'quantity': 1}}}

Formatted:

{
   26575:{
      "27/11/2020 00:00":{
         "amount":10.9,
         "orders":1,
         "quantity":1
      }
   },
   31937:{
      "27/11/2020 00:00":{
         "amount":55.8,
         "orders":2,
         "quantity":2
      },
      "29/11/2020 00:00":{
         "amount":96.9,
         "orders":1,
         "quantity":1
      }
   }
}
   

Edit2:

df.groupby(['id'])[['datetime','amount', 'orders', 'quantity']]

this will group the dataframe by id. This id will be a dict key and the rest of the item I have to convert it into another dictionary with a datetime as key.

So after grouping, I will take the rows from 'datetime','amount', 'orders', 'quantity' and make the datetime as a key and rest of them as value. This will be done by using x.set_index('datetime').to_dict(orient='index')

The reason for using orient='index' is I want to have the result in this form: {index -> {column -> value}}

Here my index is datetime so it will be a key and rest of the values such as 'amount', 'orders', 'quantity' will have this form:

{ "27/11/2020 00:00" -> { amount -> 55.8, orders -> 1, quantity -> 2 }}