Python将csv转换为特定格式的json

我正在尝试编写一个python脚本，它采用以下结构化CSV文件：

"id","city","diacritice","county","auto","zip","populatie","lat","lng"
"1","Buftea","Buftea","Ilfov","IF","70000","19202","44.5629744","25.9388214"
"2","Buciumeni","Buciumeni","Ilfov","IF","70000","2976","44.5460939","25.9574846"
"3","Otopeni","Otopeni","Ilfov","IF","75100","12540","44.5671874","26.0835113"
"4","Odaile","Odăile","Ilfov","IF","75100","1321","44.5435944","26.0487028"

并将其转换为以下json文件：

{
  "locations": [
    {
      "name": "New York",
      "slug": "",
      "description": "",
      "meta": [
        {"image_id" :  45},
        {"_icon" :  ""}
      ],
      "order": 0,
      "child": [
        {"name": "New York", "order": 1 },
        {"name" : "Port Chester", "order": 2},
        {"name" : "Mineola", "order": 3},
        {"name" : "Mount Vernon", "order": 4},
        {"name" : "Hempstead", "order": 5},
        {"name" : "Lynbrook", "order": 6},
        {"name" : "Yonkers", "order": 7},
        {"name" : "Franklin Square", "order": 8},
      ]
    }
  ]
}

主location元素应该是国家名称，子元素应该是城市名称。
我写了下面的脚本，第一步读取整个css文件，然后把所有的县放在一个集合中，在第二个循环中迭代所有的县，不知何故，我想把另一个for循环放在json声明中，但这是一个坏主意。

import csv
import json

# First, parse the CSV data using the csv module

county = set()

with open('localitati.csv', 'r', newline='') as csvfile:
  # Use the csv.reader function to read the data line by line
  reader = csv.reader(csvfile)

  # Loop through each line of the CSV data
  for row in reader:
    # Print the name from the second column of the CSV data
    #print(row[3])
    county.add(row[3])

for cou in county:
    csvData = '"1","Buftea","Ilfov","IF","70000","19202","44.5629744","25.9388214"'

    parsedData = list(csv.reader([csvData]))

    # Next, construct the JSON object using the parsed data
    jsonData = {
      "locations": [
        {
          "name": cou,
          "slug": "",
          "description": "",
          "meta": [
            {"image_id" : ""},
            {"_icon" : ""}
          ],
          "order": "",
          "child": [
              #here i tired the for loop to get the cities 
            {"name": parsedData[0][1], "order": ""},
            {"name": parsedData[0][1], "order": ""},
          ]
        }
      ]
    }

# Finally, output the JSON object
print(json.dumps(jsonData, indent=2))

with open("sample.json", "w") as outfile:
    outfile.write(json.dumps(jsonData, indent=2))

在将所有县转换为列表之前，您需要将它们收集到字典中。

import csv
import json

counties = {}

for row in csv.DictReader(open('x.csv')):
    if row['county'] not in counties:
        counties[row['county']] = {
            'name': row['county'],
            'slug': '',
            'description': '',
            'order': 0,
            'child': [
                {'name': row['city'], 'order': 1}
            ]
        }
    else:
        idx = len(counties[row['county']]['child'])+1
        counties[row['county']]['child'].append(
            {'name': row['city'], 'order': idx}
        )

data = {'locations':list(counties.values())}
print(json.dumps(data, indent=4))

输出：

{
    "locations": [
        {
            "name": "Ilfov",
            "slug": "",
            "description": "",
            "order": 0,
            "child": [
                {
                    "name": "Buftea",
                    "order": 1
                },
                {
                    "name": "Buciumeni",
                    "order": 2
                },
                {
                    "name": "Otopeni",
                    "order": 3
                },
                {
                    "name": "Odaile",
                    "order": 4
                }
            ]
        }
    ]
}

Python将csv转换为特定格式的json

1条答案

相关问题

热门标签

最新问答