csv to JSON with Array Object in Python

Question

Having troubles converting a CSV file to JSON with array of sub-lists.

Csv Input:

Id,Name,Sub1,Sub2
1,A,Mathematics,English
2,B,Mathematics,Science

JSON Output:

{
  "sources": [
    {
      "Id": 1,
      "Name": "A",
      "subjects": [
        {
          "Sub1": "Mathematics",
          "Sub2": "English"
        }
      ]
    },
    {
      "Id": 2,
      "Name": "B",
      "subjects": [
        {
          "Sub1": "Mathematics",
          "Sub2": "Science"
        }
      ]
    }
  ]
}

tried with pands as below but need to improve it

import pandas as pd
csv_file = pd.DataFrame(pd.read_csv("/home/ubuntu/Proj/sample_2.csv", sep = ",", header = 0, index_col = False))

csv_file.to_json("/home/ubuntu/Proj/csvToJson_2.json", indent=4, orient = "records", date_format = "epoch", double_precision = 10, force_ascii = True, date_unit = "ms", default_handler = None)

SeaBean · Accepted Answer · 2021-06-20 15:49:53Z

2

You can use DataFrame.apply() with lambda function to make the customized format, then use json.dump to write it as json file, as follows:

csv_file = pd.read_csv("/home/ubuntu/Proj/sample_2.csv", sep = ",", header = 0, index_col = False)

json_out_list = csv_file.apply(lambda x: {"Id": x['Id'], "Name": x['Name'], "subjects": [{"Sub1": x['Sub1'], "Sub2": x['Sub2']}]}, axis=1).to_list()

json_result = {"sources": json_out_list}

import json
with open("/home/ubuntu/Proj/csvToJson_2.json", 'w') as f:
    json.dump(json_result, f, indent=2)

Result:

{
  "sources": [
    {
      "Id": 1,
      "Name": "A",
      "subjects": [
        {
          "Sub1": "Mathematics",
          "Sub2": "English"
        }
      ]
    },
    {
      "Id": 2,
      "Name": "B",
      "subjects": [
        {
          "Sub1": "Mathematics",
          "Sub2": "Science"
        }
      ]
    }
  ]
}

edited Jun 20, 2021 at 15:49

answered Jun 19, 2021 at 21:20

SeaBean

23.4k3 gold badges16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

csv to JSON with Array Object in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related