Python convert JSON to CSV

Question

I have a JSON file which contains:

{
    "leaderboard": {
        "$": [
            {
                "userId": 1432024286216,
                "userName": "Joe Bloggs",
                "score": 111111,
                "gameType": "standard",
                "dateCreated": 1432024397833,
                "_id": 1432024397833
            },
            {
                "userId": 1432024626556,
                "userName": "Jane Bloggs",
                "score": 222222,
                "gameType": "demo",
                "dateCreated": 1432024730861,
                "_id": 1432024730861
            }
        ]
    },
    "users": {
        "$": [
            {
                "userId_U": 1432024286000,
                "userName_U": "Paul Bloggs",
                "score_U": 333333,
                "gameType_U": "standard",
                "dateCreated_U": 1432024397833,
                "_id_U": 1432024397833
            },
            {
                "userId_U": 1432024626777,
                "userName_U": "John Bloggs",
                "score_U": 444444,
                "gameType_U": "demo",
                "dateCreated_U": 1432024730861,
                "_id_U": 1432024730861
            }
        ]
    }
}

I am trying to create a CSV from this in Python. The CSV creates the headers: userId, userName etc. only from the 'leaderboard' data object only and populate the corresponding data for it. So create a column each for: userId, userName etc.

I started coding this but I am getting the 'leaderboard' and 'users' headers created and their data in one cell beneath them. My code:

import json, csv

x = open('test/dbTest.json')

rows = json.load(x)
with open('test.csv', 'wb+') as f:
    dict_writer = csv.DictWriter(f, fieldnames=['leaderboard', 'users'])
    dict_writer.writeheader()
    dict_writer.writerow(rows)

I have tried to change the field name to 'userId' , 'userName' etc but it then gives error:

ValueError: dict contains fields not in fieldnames: u'users', u'leaderboard'

How can I extract the data I need? Why is the above code incorrect?

Also, the CSV should look like:

userId,userName,score,gameType,dateCreated,_id,
1432024286216,Joe Bloggs,111111,standard,1432024397833,1432024397833
1432024626556,Jane Bloggs,222222,demo,1432024730861,1432024730861

and to clarify, 'users' and 'leaderboard' are different with different field names.

You have more than one piece of "tabular" data in your JSON. Which do you want converted to CSV? — James Mills, May 26 '15 at 09:12
Please [edit] your question and inlcude a sample of how the CSV should look like for this JSON. — , May 26 '15 at 09:13
i want the data inside the "$" in "leaderboard", so "userId", "userName", "score", "gameType", "dateCreated", "_id" all for "leaderboard" only. — yigames, May 26 '15 at 09:16
'users' and 'leaderboard' are different with different field names — yigames, May 26 '15 at 09:19
Whenever I need to [convert json to csv, I use data.page](https://data.page/json/csv) https://data.page/json/csv. This is not using python, of course, but it gets the job done. (disclaimer, this is not my website, and I am not affiliated with them) — Jayden Lawson, Jun 22 '21 at 23:42

score 2 · Accepted Answer · answered May 26 '15 at 09:20

2

# json_data being the literal file data, in this example

import json
import csv

data = json.loads(json_data)['leaderboard']['$']

with open('/tmp/test.csv', 'w') as outf:
    dw = csv.DictWriter(outf, data[0].keys())
    dw.writeheader()
    for row in data:
        dw.writerow(row)

answered May 26 '15 at 09:20

mephisto

661
1
6
14

well, i tested it by loading your json data into the "json_data" variable as a string. in your example, you use json.load() to load it from a file. you can just use that instead, but make sure you you do add the ['leaderboard']['$'] part at the end – mephisto May 26 '15 at 09:54
thank you kindly for your solution, i see now what i should have done, only load the specific data i need. this now works, sorry about not using the load() command to load JSON. the output however has an extra blank row after every row of data. so it has header row, then blank row, then data row, then blank row then another data row etc. any way to resolve this? – yigames May 26 '15 at 10:01
also say if i need to format a data field like score before writing to CSV, how would i do that? – yigames May 26 '15 at 10:04
in that case i would recommend doing it manually, i'd read up on the docs of the csv module – mephisto May 26 '15 at 10:12
does that answer your question? if so, please mark it as the answer :) – mephisto May 26 '15 at 12:11

Python convert JSON to CSV

1 Answers1

Linked