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 pymotw.com/2/collections/ordereddict.html — Hybrid
– Hybrid, Commented Aug 22, 2015 at 17:39
@njzk2 it does as far as Object.keys(json[0]) is concerned — geotheory
– geotheory, Commented Aug 24, 2015 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
– njzk2, Commented Aug 24, 2015 at 13:25

Anand S Kumar · Accepted Answer · 2015-08-22 18:33:55Z

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, 2015 at 18:33

answered Aug 22, 2015 at 17:35

Anand S Kumar

91.4k18 gold badges196 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

The6thSense Over a year ago

But why is this giving rows in the same order every time

Jason Sperske Over a year ago

@VigneshKalai, The order of a dict isn't random, it just doesn't match the order they are expecting.

The6thSense Over a year ago

@JasonSperske that makes sense but then there is some kind of ordering in dictonary

Hybrid Over a year ago

VigneshKalai, Because row remember it's items but dictionary does not because it depend upon the key value structure.

njzk2 Over a year ago

this is useless. even if the json file presents the data in some order, there is no guaranty that the parser will respect it.

|

Martin Konecny · Accepted Answer · 2015-08-22 17:59:55Z

4

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

edited Aug 22, 2015 at 17:59

answered Aug 22, 2015 at 17:50

Martin Konecny

59.9k20 gold badges144 silver badges159 bronze badges

1 Comment

geotheory Over a year ago

Noted. Thanks for the tip

Collectives™ on Stack Overflow

Python csv to json column order

2 Answers 2

13 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related