CODEWITHSUNDEEP
pythonjsonpandas
user2675217
user2675217

Reputation: 61

csv to JSON with Array Object in Python

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)

Upvotes: 2

Views: 112

Answers (1)

SeaBean
SeaBean

Reputation: 23217

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"
        }
      ]
    }
  ]
}

Upvotes: 2

Related Questions