Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

CSV to JSON using python, only selected columns, and creating new entry for each cell having multiple values from a single CSV cell?

I am very new to python and I am trying to get the following JSON output from a csv file and the csv file looks like this

date      customer Ex employer    emailID        CIN     CIN2            BatcheSource
9-Jul-24   ABC1    EmployerAnme1  [email protected]  123456  9087690,345678  payment
9-Oct-24   BCD1    EMP2           [email protected]  234566                  adasd

The JSON output I am looking for is

[
   {
      "Cin":"123456",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1",
      "Batche Source":"Payment"
   },
   {
      "Cin":"9087690",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1" 
      "Batche Source":"Payment"
},
   {
      "Cin":"345678",
      "Date":"9-Jul-24",
      "Ex Employer":"Employer Name 1", 
      "Batche Source":"Payment"
   },
   {
      "Cin":"234566",
      "Date":"9-Oct-24",
      "Ex Employer":"EMP2",
      "Batche Source":"adasd"
   }

]

The code so far I have tried is this one, but I’m not sure how to get new entries for comma separated multiple values of a cell:

import csv
import json

with open('test.csv') as infile:
    reader = csv.DictReader(infile)
    out = [{"CIN": row['CIN'],"Date": row["Date"], "Ex Employer": row["Ex Employer"],"CIN2": row["CIN2"],"Batche Source": row["Batche Source"]} for row in reader]

with open('test1.json', 'w') as outfile:
    json.dump(out, outfile)
Tags:

2

Answers


  1. 0

    Use pandas

    import pandas as pd
import json

result = pd.read_csv('my.csv')

csv_as_dict = result.to_dict(orient='records')
csv_as_json = json.dump(csv_as_dict)
    Login or Signup to reply.
  2. 0

    You’ll want to split your CIN2 value into a list, and put that together with CIN, and create a new output for each of those CINs:

    out = []
for row in reader:
    cins = [row['CIN'], *row['CIN2'].split(',')]
    for cin in cins:
        out.append({"CIN": cin, "Date": row["Date"], "Ex Employer": row["Ex Employer"], "Batche Source": row["Batche Source"]})

    You can condense that into a list comprehension, but I’d advise against it for readability.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search