Python Csv To Json Column Order

Basic Python csv to json script gets the column order mixed up in the final JSON. Any idea why? test.csv animal,age,count,legs dogs,3,5,4 cats,6,4,4 birds,2,1,2 script import csv

Solution 1:

It is because dictionary does not have any sense of order, so it is expected that the dictionaries are in arbitrary order.

If you must preserve the order (and ideally you should not have to), you would need to read each row using a simple csv reader, and then create collection.OrderedDict objects, that do store the order in which keys are added. Example -

from collections import OrderedDict
import csv
withopen('test.csv','r') as f:
    reader = csv.reader(f)
    headerlist = next(reader)
    csvlist = []
    for row in reader:
            d = OrderedDict()
            for i, x inenumerate(row):
                    d[headerlist[i]] = x
            csvlist.append(d)

import json
withopen('test.json','w') as f:
    json.dump(csvlist,f)

Please note this would still be useless if the parser who would be using this JSON does not respect the order in the same way.

---

Example/Demo -

With my test.csv as -

animal,age,count,legs
dogs,3,5,4
cats,6,4,4
birds,2,1,2

test.json looked like -

[{"animal":"dogs","age":"3","count":"5","legs":"4"},{"animal":"cats","age":"6","count":"4","legs":"4"},{"animal":"birds","age":"2","count":"1","legs":"2"}]

Solution 2:

In Anand's answer you are only temporarily solving the problem. The JSON file may be output in the correct order, but then you could have this data read in another location (such as the browser or another Python process), and when it reads in this file, the order will not be preserved!

You will want to use a list of lists if you want to guarantee order between your data source and its destination.

You can do the following to preserve order:

import csv 
import json

lst = []
csvfile = open('test.csv', 'r')
jsonfile = open('test.json', 'w')
first_line = next(csvfile).split(',')

csvfile.seek(0) # we peeked at the first_line, lets reset back to beginning

reader = csv.DictReader(csvfile)

for row in reader:
    group = []
    for h in first_line:
        h = h.strip()
        group.append([h, row[h]])
    lst.append(group)

jsonfile.write(json.dumps(lst))

Output:

[[["animal", "dogs"], ["age", "3"], ["count", "5"], ["legs", "4"]], [["animal", "cats"], ["age", "6"], ["count", "4"], ["legs", "4"]], [["animal", "birds"], ["age", "2"], ["count", "1"], ["legs", "2"]]]