Sorting CSV to be written into a JSON file in Python

Question

I have a CSV file which is converted into a JSON file with a Python script. It works but I want JSON to have the same sorting of CSV file. This is what I've tried without success:

#!/usr/bin/python

import csv
import json

CSV_PATH = './myFile.csv'
JSON_PATH = './myFile.json'

csv_file = csv.DictReader(open(CSV_PATH, 'r'))

json_list = []
for row in csv_file:
   json_list.append(row)

sortedlist = sorted(json_list, key=lambda row:(row['id']), reverse=False)

file(JSON_PATH, 'w').write(json.dumps(
    sortedlist, sort_keys=False, indent=2, separators=(',', ': '), 
    encoding="utf-8",ensure_ascii=False))

My CSV looks like that:

id,name,lastname
1,John,Red
2,Steve,Brown

But the created JSON looks like that even with that lambda function:

[
  {
    "name": "John",
    "id": "1",
    "lastname": "Red"
  },
  {
    "name": "Steve",
    "id": "2",
    "lastname": "Brown"
  }
]

What I'd like to get is "id, "name" and then "lastname".

Pythonist · Accepted Answer · 2017-09-04 11:25:17Z

1

Dictionaries don't keep the original order. You may want to try using OrderedDict from collection module

from collections import OrderedDict
my_dictionary=OrderedDict()
my_dictionary['foo']=3
my_dictionary['bar']=1
my_dictionary
OrderedDict([('foo', 3), ('bar', 1)])

An OrderedDict is a dict that remembers the order that keys were first inserted.

It should do the job:

import json
from collections import OrderedDict
json.dumps(OrderedDict([("b", 1), ("a", 2), ("c", 2)]))

Out: '{"b": 1, "a": 2, "c": 2}'

json.dumps({'b': 1, 'a': 2, 'c': 2})

Out: '{"a": 2, "c": 2, "b": 1}'

If you want to reorder your dict, Dictreader takes fieldnames as positional argument:

FIELDNAMES = ['id', 'name', 'last_name']
csv_file = csv.DictReader(open(CSV_PATH, 'r'), fieldnames=FIELDNAMES)
json_list = []
for row in csv_file:
   # Reorder according to your needs
   data = OrderedDict([
       ('name', row['name']), ('last_name', row['last_name']), ('id', row['id'])])
   json_list.append(data)
   ...

edited Sep 4, 2017 at 11:25

answered Sep 4, 2017 at 10:38

Pythonist

6966 silver badges28 bronze badges

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

7 Comments

antonioag Over a year ago

I already knew that, this is why I was asking for a way to sort them.

Jefferson Houp Over a year ago

@antonioag look at updated post. Just create OrderedDict with reading csv.

Jefferson Houp Over a year ago

@antonioag There updated ver of answer. With OrderedDict you can make json with any order what you want.

Pythonist Over a year ago

One line above for row in csv_file: add next(csv_file), should work tho, also remember to accept the answer if that solves your problem.

mhawke Over a year ago

@Pythonist: you don't need to specify the field names for DictReader, they will be taken from the first line of the CSV file. So, if you omit the fieldnames= argument when creating the reader you do not need to explicitly skip the first row.

|

Martin Bonner supports Monica · Accepted Answer · 2017-09-04 10:58:38Z

0

sorted is sorting the entries in the list. So if your original list was :

[
  {
    "name": "Steve",
    "id": "2",
    "surname": "Brown"
  },
  {
    "name": "John",
    "id": "1",
    "surname": "Red"
  }
]

It would put it in the correct order. As it is, your list is already in the same order as the CSV files. Your problem is that each entry in the list is a dict, and dictionaries don't preserve the order of their elements.

edited Sep 4, 2017 at 10:58

answered Sep 4, 2017 at 10:43

Martin Bonner supports Monica

29.1k3 gold badges56 silver badges91 bronze badges

9 Comments

Martin Bonner supports Monica Over a year ago

As an aside, if this is a real project, go read kalzumeus.com/2010/06/17/…

Jefferson Houp Over a year ago

Bad practice with "you are in luck". Do not use sorting like that. Be sure your dict will be right with any changes in the future.

Martin Bonner supports Monica Over a year ago

@JeffersonHoup How would you do that?

antonioag Over a year ago

I wish I were in luck but unfortunately I'm planning to update my CSV with other values which can't be alphabetically sorted.

Martin Bonner supports Monica Over a year ago

@antonioag : I suggest changing your example to "lastname" then. That keeps it simple, and makes your desired order non-alphabetic.

|

mhawke · Accepted Answer · 2017-09-04 12:03:56Z

Use a collections.OrderedDict to maintain the order of the keys:

import csv
import json
from collections import OrderedDict

CSV_PATH = 'myFile.csv'
JSON_PATH = 'myFile.json'

with open(CSV_PATH) as csv_file, open(JSON_PATH, 'w') as json_file:
    reader = csv.DictReader(csv_file)
    data = [OrderedDict((field, row[field]) for field in reader.fieldnames) for row in reader]
    json.dump(data, json_file, indent=2, separators=(',', ': '), encoding="utf-8",ensure_ascii=False)

Output for your input CSV file:

[
  {
    "id": "1",
    "name": "John",
    "surname": "Red"
  },
  {
    "id": "2",
    "name": "Steve",
    "surname": "Brown"
  }
]

This code iterates over the rows of the CSV file and creates a list of OrderedDicts. The order of the keys is maintained by the fieldnames list of the DictReader object.

The list is then output directly to file using json.dump() in preference to json.dumps() because it's slightly easier to do so.

Collectives™ on Stack Overflow

Sorting CSV to be written into a JSON file in Python

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related