CODEWITHSUNDEEP
pythonjson
sharp
sharp

Reputation: 2158

python: split and create json data from csv

How do I create a nested field from csv to json? I looked at the other stackoverflow, but they are not quite what I am trying to format. I have a data set with 1 column that I have to convert into a nested field.

Data:

ID, NAME
1, "Smith, Mr. Adams"
2, "McAdams, Mrs. Audrey"
3, "McAdams, Doctor John"
4, "Missing Value"

CODE: 

with open('test.csv', 'r') as file:
            headers = next(file) #skip the headers
            fieldnames = headers.rstrip().split(",")
            csv_reader = csv.DictReader(file, fieldnames) #creating a dictionary
            import datetime
            for row_dict in csv_reader:
                row_dict['name'] = row_dict['name'].split(",")
                json_data = json.dumps(row_dict)
                print(json_data)

I am getting output in a list but it is not nested.

{"id": "1", "name": ["Smith", " Mr. Adams"]}
{"id": "2", "name": ["McAdams", " Mrs. Audrey"]}
{"id": "3", "name": ["McAdams", " Doctor John"]}
{"id": "4", "name": ["Missing Value"]}

Final output: Is there any way to do this?

{"id": "1", "name": [{"last_name": "Smith",
                      "prefix": "Mr.",
                      "first_name":  "Adams"}]}
{"id": "1", "name": [{"last_name": "McAdams",
                      "prefix": "Mrs.",
                      "first_name":  "Audrey"}]}
{"id": "1", "name": [{"last_name": "McAdams",
                      "prefix": "Dr.",
                      "first_name":  "John"}]}
{"id": "1", "name": [{"last_name": "Missing Value",
                      "prefix": "Missing Value",
                      "first_name":  "Missing Value"}]}

Upvotes: 0

Views: 477

Answers (1)

marcel.js
marcel.js

Reputation: 313

Just use .split() some times and create a new dict.

import json

csv = '''1, "Smith, Mr. Adams"
2, "McAdams, Mrs. Audrey"
3, "McAdams, Doctor John"
4, "Missing Value"'''

csv_lines = csv.split('\n')


for line in csv_lines:
  id = line.split(',')[0]
  name = line[len(id)+3:-1]
  split = name.split(', ')
  last_name = split[0]
  if len(split) < 2:
    first_name = last_name
    prefix = last_name
  else:
    prefix = split[1].split(' ')[0]
    first_name = split[1][len(prefix)+1:]

  row_dict = {'id': id, 'name': {'last_name': last_name, 'prefix': prefix, 'first_name': first_name}}

  json_data = json.dumps(row_dict)
  print(json_data)

Output:

{"id": "1", "name": {"last_name": "Smith", "prefix": "Mr.", "first_name": "Adams"}}
{"id": "2", "name": {"last_name": "McAdams", "prefix": "Mrs.", "first_name": "Audrey"}}
{"id": "3", "name": {"last_name": "McAdams", "prefix": "Doctor", "first_name": "John"}}
{"id": "4", "name": {"last_name": "Missing Value", "prefix": "Missing Value", "first_name": "Missing Value"}}

Upvotes: 1

Related Questions