Python csv to json column order

Question

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
import json

csvfile = open('test.csv', 'r')
jsonfile = open('test.json', 'w')

reader = csv.DictReader( csvfile)

jsonfile.write('[')
for row in reader:
    json.dump(row, jsonfile)
    jsonfile.write(',\n')
jsonfile.write(']')

test.json

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

You can use this to get ordered dictionary http://pymotw.com/2/collections/ordereddict.html — Hybrid, Aug 22 '15 at 17:39
@njzk2 it does as far as `Object.keys(json[0])` is concerned — geotheory, Aug 24 '15 at 11:13
@geotheory it is merely a coincidence. don't rely on that. (you'll also notice that small integers are sorted in a `set`, but that does not mean that other keys will be) — njzk2, Aug 24 '15 at 13:25

Anand S Kumar · Accepted Answer · 2015-08-22T18:33:55.330

6

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
with open('test.csv','r') as f:
    reader = csv.reader(f)
    headerlist = next(reader)
    csvlist = []
    for row in reader:
            d = OrderedDict()
            for i, x in enumerate(row):
                    d[headerlist[i]] = x
            csvlist.append(d)

import json
with open('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"}]

edited Aug 22 '15 at 18:33

answered Aug 22 '15 at 17:35

Anand S Kumar

88,551
18
188
176

But why is this giving rows in the same order every time – The6thSense Aug 22 '15 at 17:35
@VigneshKalai, The order of a dict isn't random, it just doesn't match the order they are expecting. – Jason Sperske Aug 22 '15 at 17:36
@JasonSperske that makes sense but then there is some kind of ordering in dictonary – The6thSense Aug 22 '15 at 17:37
1

VigneshKalai, Because row remember it's items but dictionary does not because it depend upon the key value structure. – Hybrid Aug 22 '15 at 17:37
@JasonSperske The issue occurs in `reader = csv.DictReader( csvfile)` and then reading each row from it ,don't think there is any hook to have ordered dict there. – Anand S Kumar Aug 22 '15 at 17:37
@AnandSKumar, good point, looks like the OP will need to read column by column into an OrderedDict – Jason Sperske Aug 22 '15 at 17:38
@VigneshKalai, the order comes from the hash of the keys (which is the same every time) – Jason Sperske Aug 22 '15 at 17:39
Rows are entires in a list, which preserves order. – veggie1 Aug 22 '15 at 17:39
This is nuts. I literally can't find a csv2json script that works properly. Half of them produce lists (not arrays) of json documents, and the other half require you to name headers explicitly. Guess I'm going to have to write it myself. – geotheory Aug 22 '15 at 17:46
You can check the code I gave, it should work for all csvs (that have headers) . – Anand S Kumar Aug 22 '15 at 17:47
1

this is useless. even if the json file presents the data in some order, there is no guaranty that the parser will respect it. – njzk2 Aug 22 '15 at 18:05
Well it works for me, at least for now. Thanks Anand – geotheory Aug 24 '15 at 12:37
why are numbers returned as strings? – Daniel Ferradal Nov 30 '22 at 10:49

Martin Konecny · Answer 2 · 2015-08-22T17:59:55.493

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

Python csv to json column order

2 Answers2

Linked