Python turning CSV to JSON with headers

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