Converting JSON to CSV with missing fieldnames

Question

I am having a hard time converting JSON to csv because the names on some of the records don't show up. For example:

[{device: 1,
  name: 'John',
  age: 25,
  city: 'Denver'
 },
 {device: 2,
  name: 'Jake',
  age: 24,
  city: 'New York'
 },
 {device: 3,
  name: 'Phil',
  age: 23}]

It is further made difficult because it's several thousand rows where sometimes the city is known, other times it's not.

I would like to put these together into a csv and just leave Phil's city blank.

Have you tried using a default schema... so you can easily find nulls. — jidexl21, Sep 17 '19 at 17:16

score 2 · Accepted Answer · answered Sep 18 '19 at 01:20

You can use this:

import json
import csv
js = """[{"device": 1,
  "name": "John",
  "age": 25,
  "city": "Denver"
 },
 {"device": 2,
  "name": "Jake",
  "age": 24,
  "city": "New York"
 },
 {"device": 3,
  "name": "Phil",
  "age": 23}]
  """
js = json.loads(js)

with open( 'result.csv', 'w' ) as csv_file:
    writer = csv.writer( csv_file )
    columns =  list({column for row in js for column in row.keys()})
    writer.writerow( columns )
    for row in js:
        writer.writerow([None if column not in row else row[column] for column in columns])

This works even with different column names and larger numbers of columns!

score 0 · Answer 2 · answered Sep 17 '19 at 17:19

There is probably a built in way to do this in the json to csv module but you could iterate over all your dicts and add the missing keys:

my_list = [{'device':1,
  'name':'John',
  'age':25,
  'city': 'Denver' },
 {'device':2,
  'name':'Jake',
  'age':24,
  'city': 'New York'
 },
 {'device':3,
  'name':'Phil',
  'age':23}]

keys_and_default = {
  'device': -1, 
  'name': 'N/A', 
  'age': -1,
  'city': 'N/A'
}
for dic in my_list:
    for key, val in keys_and_default.items():
        if key not in dic:
            dic[key] = val
print(my_list)

Converting JSON to CSV with missing fieldnames

2 Answers2