I'm trying to create a JSON from a CSV using python. This issue I'm running into is a method of separating headers from data. My current code is:
import csv
import json
with open('input.csv') as f:
columns = ("Column 1", "Column 2")
reader = csv.DictReader(f, columns)
rows = list(reader)
with open('output.json', 'w') as f:
json.dump(rows, f, indent=4)
but this returns
[
{
"Column 2": null,
"Column 1": "Title"
},
{
"Column 2": " data2",
"Column 1": "data1"
},
{
"Column 2": " data4",
"Column 1": "data3"
},
{
"Column 2": " data6",
"Column 1": "data5"
}
]
instead of
[
"Title1": {
{
"Column 2": " data2",
"Column 1": "data1"
},
{
"Column 2": " data4",
"Column 1": "data3"
},
{
"Column 2": " data6",
"Column 1": "data5"
}
}
]
Heres an example of what the CSV would look like for reference:
Title
data1, data2
data3, data4
data5, data6
Title2
data1, data2
data3, data4
data5, data6
3 Answers
Rather than just using list, you need to build up the nested data structure yourself. Here's one way you could do it:
import csv
import json
with open('input.csv') as f:
columns = ("Column 1", "Column 2")
reader = csv.DictReader(f, columns)
tree = {}
for row in reader:
if row["Column 2"] is None:
current = []
tree[row["Column 1"]] = current
else:
current.append(row)
When a "title" row is found (one where the second column is empty), a new list is added to the tree. Further rows are added to this list, until another "title" row appears, and a new list is started.
Could you do something like this?
titles = ['Title', 'Title2']
output = {}
for line in f:
if line[0] in titles:
title = line[0]
output[title] = {}
output[title]["Column 1"] = []
output[title]["Column 2"] = []
else:
output[title]["Column 1"].append(line[0])
output[title]["Column 2"].append(line[1])
Returns:
{ 'Title': { 'Column 1': ['data1', 'data3', 'data5'],
'Column 2': ['data2', 'data4', 'data6']},
'Title2': { 'Column 1': ['data1', 'data3', 'data5'],
'Column 2': ['data2', 'data4', 'data6']}}
You need to message your data as read to a different structure.
Something like:
data={}
with open('/tmp/f.csv') as f:
reader=csv.reader(f)
for sl in (row for row in reader):
if len(sl)==1:
key=sl[0]
data[key]=[]
else:
data[key].append({"Column {}".format(i):e for i, e in enumerate(sl,1)})
>>> json.dumps(data, f, indent=4)
{
"Title2": [
{
"Column 2": " data2",
"Column 1": "data1"
},
{
"Column 2": " data4",
"Column 1": "data3"
},
{
"Column 2": " data6",
"Column 1": "data5"
}
],
"Title": [
{
"Column 2": " data2",
"Column 1": "data1"
},
{
"Column 2": " data4",
"Column 1": "data3"
},
{
"Column 2": " data6",
"Column 1": "data5"
}
]
}
