Bottom Page

Thread Rating:
  • 3 Vote(s) - 2.67 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Python convert csv to json with nested array without pandas
#1
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

    "date": "2016-07-01",
    "id": "S56202",
    "items": [{"description": "Class A",
         "code": "300-E003",
         "name": "Jacky"},
Here is my code
    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

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

Quote
#2
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'
Quote
#3
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

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
"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
"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"
                                  }
            },
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  python 2D array creation and print issue developerbrain 5 103 May-15-2019, 01:38 PM
Last Post: developerbrain
  Is there any way to convert a python script (with Tkinter module), to a .exe (Windows moste 3 98 May-12-2019, 12:02 PM
Last Post: snippsat
  print python json dump onto multiple lines lhailey 1 100 May-05-2019, 03:24 PM
Last Post: Yoriz
  Nested dictionary issue Will86 3 151 Apr-16-2019, 11:58 PM
Last Post: Will86
  Read Byte Array from Zigbee coordinator using python jenkins43 3 165 Mar-26-2019, 03:03 PM
Last Post: micseydel
  Cryptodome - convert openssl command to python twebber 1 110 Mar-25-2019, 12:27 AM
Last Post: twebber
  N-Dim array manipulation in a loop, getting IndexError: too many indices for array cesardepaula 1 322 Mar-13-2019, 01:39 AM
Last Post: scidam
  Nested Dictionary to excel VictoriaE 1 204 Feb-23-2019, 03:26 AM
Last Post: Larz60+
  Nested Dictionaries with Lists BriHaug 1 195 Feb-23-2019, 02:10 AM
Last Post: ichabod801
  Min/Max From Nested List snowman24 7 278 Feb-01-2019, 11:07 AM
Last Post: snowman24

Forum Jump:


Users browsing this thread: 1 Guest(s)