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 1 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'
can someone please help how to get extract the data i need? explanation why above code is incorrect and kindly point me in the right direction please? apologies if not clear and if missing anything.
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.