N.Landreau N.Landreau - 6 months ago 33
JSON Question

CSV to JSON with Python

I'm trying to build a JSON file using python and starting with a CSV file.

The CSV file look like this :

a,b,tableau.c,tableau2.d,tableau2.e,tableau2.f,tableau2.g,tableau.h
11725,11741,false,N/A,true,23,N/A,false


So far i'm managing to convert the csv file to a JSON file and arrange it to look like this :

[
{
"a": "11725",
"b": "11741",
"tableau.c": "false",
"tableau.h": "false",
"tableau2.d": "N/A",
"tableau2.e": "true",
"tableau2.f": "23",
"tableau2.g": "N/A"
}
]


Using this code :

import csv
import json

file = "myFile.csv"
json_file = "myFile.json"
csv_rows = []

with open(file) as csvfile:
reader = csv.DictReader(csvfile)
title = reader.fieldnames

for row in reader:
csv_rows.extend([{title[i]:row[title[i]] for i in range(len(title))}])


with open(json_file, "w") as f:
f.write(json.dumps(csv_rows, sort_keys=True, indent=4, separators=(',', ': ')))


But my wish would to get to a JSON file that look like this one :

[
{
"a": "11725",
"b": "11741",
"tableau": [
{
"value" :"false",
"name" : "c"
},
{
"value" :"false",
"name" : "h"
},
]
"tableau2": [
{
"value" :"N/A",
"name" : "d"
},
{
"value" :"true",
"name" : "e"
},
{
"value" :"23",
"name" : "f"
},
{
"value" :"N/A",
"name" : "g"
},
]

}
]


I have been looking around for answer about how to do this and found nothing so far.

Any suggestion about how to do this ?

Thank you in advance

Answer

This works:

for row in reader:
    row_result = {}
    for key, value in row.iteritems():
        if '.' in key:
            tableau, name = key.split('.')
            row_result.setdefault(tableau, []).append({'value': value, 'name': name})
        else:
            row_result[key] = value
    csv_rows.append(row_result)