Python convert csv to json with nested array without pandas

[terrydidi]

I'm trying to insert new array inside the array but I'm not sure where can I append the data.

Csv table

date, id, description, name, code
2016-07-01, S56202, Class A, Jacky, 300-E003

Currently, my result is

1 2 3 4 5

"date": "2016-07-01", "id": "S56202", "items": [{"description": "Class A",      "code": "300-E003",      "name": "Jacky"},

Here is my code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

import csv import json from itertools import groupby   with open('student.csv', 'r') as csv_ledger:     r = csv.DictReader(csv_ledger)     data = [dict(d) for d in r]       groups = []       for k, g in groupby(data, lambda r: (r['id'], r['date'])):         groups.append({"date": k[1],                        "id": k[0],                        "items": [{k: v for k, v in d.items() if k not in                                 ['ref_num', 'date']} for d in list(g)]})

I need to insert another array in items then add the email, code and name into the new array.

Expected result

1 2 3 4

"items": [{"description": "Class A",            "new_account": {"email": "abc@hotmail.com",                            "code": "300-E003",                            "name": "Jacky"}}]

[micseydel]

I tried to run your code, planning to tinker with it to try to get a solution, but got an error:

Error:
Traceback (most recent call last):
  File "testit.py", line 11, in <module>
    for k, g in groupby(data, lambda r: (r['id'], r['date'])):
  File "testit.py", line 11, in <lambda>
    for k, g in groupby(data, lambda r: (r['id'], r['date'])):
KeyError: 'id'

[terrydidi]

Thanks for your reply. The 'id' must exactly same with the header in csv. It's case sensitive.
I updated the content

date, ref_num, description,debit,credit,coa_code, coa_name
2018-12-28, PV18/12/066, Jacky , ,59.80,310-1000, Samboi

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

import csv import json from itertools import groupby   with open('testing.csv', 'r') as csv_ledger:     r = csv.DictReader(csv_ledger)     data = [dict(d) for d in r]       groups = []       for k, g in groupby(data, lambda r: (r['ref_num'], r['date'])):         groups.append({             "date": k[1],             "ref_num": k[0],             "items": [{k: v for k, v in d.items() if k not in ['ref_num', 'date']} for d in list(g)]         })   print(json.dumps(groups[:10], indent=4))

Output result

1 2 3 4 5 6 7 8 9 10

"date": "2018-12-28",         "ref_num": "PV18/12/066",         "items": [             {                 "description": "Jacky",                 "debit": "",                 "credit": "59.80",                 "coa_code": "310-1000",                 "coa_name": "Samboi"             },

Expected Result

1 2 3 4 5 6 7 8 9 10 11 12 13

"date": "2018-12-28",         "ref_num": "PV18/12/066",         "items": [             {                 "description": "SHARMINI SHANKAR",                 "debit": "",                 "credit": "59.80",                 "new_of_account": {                                     "id": "1",                                     "coa_code": "310-1000",                                     "coa_name": "samboi"                                   }             },