I am trying to write a python script that takes the following structured CSV file:
"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"
and converts it to the following json file:
{
"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},
]
}
]
}
The main location elements should be the country names and the child elements should be the city names.
I have wrote the following script, which in first step reads the whole css file, after that it puts all the counties in a set, in the second loop it iterates over all the counties and somehow I wanted to put an other for loop inside the json declaration, but it was a bad idea.
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))
CodePudding user response:
You need to gather up all of the counties into a dictionary before you can convert them to a list.
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))
Output:
{
"locations": [
{
"name": "Ilfov",
"slug": "",
"description": "",
"order": 0,
"child": [
{
"name": "Buftea",
"order": 1
},
{
"name": "Buciumeni",
"order": 2
},
{
"name": "Otopeni",
"order": 3
},
{
"name": "Odaile",
"order": 4
}
]
}
]
}