将CSV转换为JSON（嵌套对象）

Question

我是一个有用的助手，可以翻译文本。

我刚开始学习 Python，需要将一个 CSV 文件转换成以下格式的 JSON：

CSV 文件：

firstname, lastname, email, customerid, dateadded, customerstatus
john, doe, [e-mail], 124,26/11/18,active
jane, doe, [e-mail], 125,26/11/18,active

JSON格式：

{
    firstname: "John",
    lastname: "Doe",
    emailOrPhone: "[e-mail]",
    extraFields: [{
            name: "customerid",
            value: "124"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        }
    ]
}, {
    firstname: "Jane",
    lastname: "Doe",
    emailOrPhone: "[e-mail]",
    extraFields: [{
            name: "customerid",
            value: "125"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        },
        {
            name: "dateadded",
            value: "26/11/18"
        }
    ]
}


current code I am using:
import requests
import json
import time
import csv
import json
import glob
import os
import logging


for filename in glob.glob('D:\\api\\Extract.csv'):
    csvfile = os.path.splitext(filename)[0]
    jsonfile = csvfile + '.json'

    with open(csvfile+'.csv') as f:
        reader = csv.DictReader(f)
        rows = list(reader)

    with open(jsonfile, 'w') as f:
        json.dump(rows, f)

url = 'api_url'

with open("D:\\api\\Extract.json", "r") as read_file:
    data = json.load(read_file)

    for item in data:


        headers = {"Authorization" : "key", "Content-Type" : "application/json"}

        r = requests.post(url, data= json.dumps(item), headers= headers)



        logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(message)s',
                    handlers=[logging.FileHandler("D:\\api\\log_file.log"),
                              logging.StreamHandler()])

我可以产生JSON格式的父级值，但我不确定如何获取子节点，并将列名称解析为值并像那样迭代整个文件。

上述代码将CSV转换为简单的JSON对象，我想要实现嵌套对象。我在考虑可能添加操作是解决方案，但不确定如何将列作为值传递以及相应数据作为值。

- jdag

---

你能分享你的代码吗，这样我们就可以从那里更新它。 - Krishna

嗨，我已经在上面的问题中添加了我的代码。这可能没有帮助，因为我直接将csv转换为json。 - jdag

可能是如何将CSV转换为JSON？的重复问题。 - SuperShoot

Answer 1

你可以使用csv.DictReader，这样在迭代每一行时就能够访问列名。然后你可以按照以下方式构建每个项：

import json
import csv

primary_fields = ['firstname', 'lastname', 'email']
result = []
with open('mydata.csv') as csv_file:
    reader = csv.DictReader(csv_file, skipinitialspace=True)
    for row in reader:
        d = {k: v for k, v in row.items() if k in primary_fields}
        d['extraFields'] = [{'name': k, 'value': v} for k, v in row.items() if k not in primary_fields]
        result.append(d)

print(json.dumps(result, indent=2))

输出

[
  {
    "firstname": "john",
    "lastname": "doe",
    "email": "[e-mail]",
    "extraFields": [
      {
        "name": "customerid",
        "value": "124"
      },
      {
        "name": "dateadded",
        "value": "26/11/18"
      },
      {
        "name": "customerstatus",
        "value": "active"
      }
    ]
  },
  {
    "firstname": "jane",
    "lastname": "doe",
    "email": "[e-mail]",
    "extraFields": [
      {
        "name": "customerid",
        "value": "125"
      },
      {
        "name": "dateadded",
        "value": "26/11/18"
      },
      {
        "name": "customerstatus",
        "value": "active"
      }
    ]
  }
]

如果您想在最终的json中设置自定义字段名称（例如将email设置为emailOrPhone），您可以手动设置d的字段名称并设置相应的值。

- slider

Answer 2

这个过程可能比需要的更加复杂，但是您可以在读取csv文件中的值时尝试构建您的JSON数组，然后在最后使用json.dump将结果输出到一个.json文件中：

from csv import reader
from json import dump

top_fields = ["firstname", "lastname", "email"]
extra_fields = ["customerid", "dateadded", "customerstatus"]

data = []
with open("customers.csv") as csv_in:
    csv_reader = reader(csv_in)

    # Get headers
    headers = list(map(str.strip, next(csv_reader)))

    for row in csv_reader:
        json_object = {}

        # Build dictionary for each row
        row_map = dict(zip(headers, map(str.strip, row)))

        # Add in top fields first
        for top in top_fields:
            json_object[top] = row_map[top]

        # Then add in extra fields
        for extra in extra_fields:
            json_object.setdefault("extraFields", []).append(
                {"name": extra, "value": row_map[extra]}
            )

        data.append(json_object)

with open("customers.json", "w") as fp:
    dump(data, fp, indent=4, sort_keys=True)

以下是给出的customers.json内容：

[
    {
        "email": "[e-mail]",
        "extraFields": [
            {
                "name": "customerid",
                "value": "124"
            },
            {
                "name": "dateadded",
                "value": "26/11/18"
            },
            {
                "name": "customerstatus",
                "value": "active"
            }
        ],
        "firstname": "john",
        "lastname": "doe"
    },
    {
        "email": "[e-mail]",
        "extraFields": [
            {
                "name": "customerid",
                "value": "125"
            },
            {
                "name": "dateadded",
                "value": "26/11/18"
            },
            {
                "name": "customerstatus",
                "value": "active"
            }
        ],
        "firstname": "jane",
        "lastname": "doe"
    }
]

- RoadRunner

Answer 3

import csv
import sys
import json

#EDIT THIS LIST WITH YOUR REQUIRED JSON KEY NAMES
fieldnames=["firstname","secondname","age"]

def convert(filename):
  csv_filename = filename[0]
  print "Opening CSV file: ",csv_filename 
  f=open(csv_filename, 'r')
  csv_reader = csv.DictReader(f,fieldnames)
  json_filename = csv_filename.split(".")[0]+".json"
  print "Saving JSON to file: ",json_filename
  jsonf = open(json_filename,'w') 
  data = json.dumps([r for r in csv_reader])
  jsonf.write(data) 
  f.close()
  jsonf.close()

if __name__=="__main__":
  convert(sys.argv[1:])

使用方法：

python csv2json.py myCSVfile.txt

其中，myCSVfile.txt 是您的 CSV 文件（请按照自己的喜好命名）。

它将在一个名为 myCSVfile.json 的文件中创建一个 JSON 数组。

就是这样。

- Snivio