Python - JSON to CSV table?

Question

I was wondering how I could import a JSON file, and then save that to an ordered CSV file, with header row and the applicable data below.

Here's what the JSON file looks like:

 [
  {
    "firstName": "Nicolas Alexis Julio",
    "lastName": "N'Koulou N'Doubena",
    "nickname": "N. N'Koulou",
    "nationality": "Cameroon",
    "age": 24
  },
  {
    "firstName": "Alexandre Dimitri",
    "lastName": "Song-Billong",
    "nickname": "A. Song",
    "nationality": "Cameroon",
    "age": 26,
    etc. etc. + } ]

Note there are multiple 'keys' (firstName, lastName, nickname, etc.). I would like to create a CSV file with those as the header, then the applicable info beneath in rows, with each row having a player's information.

Here's the script I have so far for Python:

import urllib2
import json
import csv

writefilerows = csv.writer(open('WCData_Rows.csv',"wb+"))


api_key = "xxxx"
url = "http://worldcup.kimonolabs.com/api/players?apikey=" + api_key + "&limit=1000"
json_obj = urllib2.urlopen(url)
readable_json = json.load(json_obj)
list_of_attributes = readable_json[0].keys()

print list_of_attributes


writefilerows.writerow(list_of_attributes)

for x in readable_json:
    writefilerows.writerow(x[list_of_attributes])

But when I run that, I get a "TypeError: unhashable type:'list'" error. I am still learning Python (obviously I suppose). I have looked around online (found this) and can't seem to figure out how to do it without explicitly stating what key I want to print...I don't want to have to list each one individually...

Thank you for any help/ideas! Please let me know if I can clarify or provide more information.

monkut · Accepted Answer · 2014-07-14 07:00:08Z

1

Your TypeError is occuring because you are trying to index a dictionary, x with a list, list_of_attributes with x[list_of_attributes]. This is not how python works. In this case you are iterating readable_json which appears it will return a dictionary with each iteration. There is no need pull values out of this data in order to write them out.

The DictWriter should give you what your looking for.

import csv
[...]

def encode_dict(d, out_encoding="utf8"):    
    '''Encode dictionary to desired encoding, assumes incoming data in unicode'''
    encoded_d = {}
    for k, v in d.iteritems():
        k = k.encode(out_encoding)
        v = unicode(v).encode(out_encoding)        
        encoded_d[k] = v
    return encoded_d

list_of_attributes = readable_json[0].keys()
# sort fields in desired order
list_of_attributes.sort()

with open('WCData_Rows.csv',"wb+") as csv_out:
    writer = csv.DictWriter(csv_out, fieldnames=list_of_attributes)
    writer.writeheader()
    for data in readable_json:
        writer.writerow(encode_dict(data))

Note: This assumes that each entry in readable_json has the same fields.

edited Jul 14, 2014 at 7:00

answered Jul 14, 2014 at 6:01

monkut

44.1k26 gold badges133 silver badges161 bronze badges

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

5 Comments

user3718365 Over a year ago

Aha! I think that's it - however, I am getting the error "UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in posiiton 6: ordinal not in range(128)". If I'm not mistaken, that's because the player's names have accents in them, and I have to let Python know that? Do I need to use something like .encode('utf-8') somewhere?

monkut Over a year ago

do you know the encoding the JSON data is in?

user3718365 Over a year ago

I am not sure, but if you don't mind, you can see the JSON data here: kimonolabs.com/worldcup/explorer

monkut Over a year ago

character decoding/encoding is a pain, Joel has a good article on it if your interested. This should work...

user3718365 Over a year ago

I am definitely interested, do you have a link?

furas · Accepted Answer · 2014-07-14 04:46:26Z

1

Maybe pandas could do this - but I newer tried to read JSON

import pandas as pd

df = pd.read_json( ... )

df.to_csv( ... )

pandas.DataFrame.to_csv

pandas.io.json.read_json

EDIT:

data = ''' [
  {
    "firstName": "Nicolas Alexis Julio",
    "lastName": "N'Koulou N'Doubena",
    "nickname": "N. N'Koulou",
    "nationality": "Cameroon",
    "age": 24
  },
  {
    "firstName": "Alexandre Dimitri",
    "lastName": "Song-Billong",
    "nickname": "A. Song",
    "nationality": "Cameroon",
    "age": 26,
  }
]'''

import pandas as pd

df = pd.read_json(data)

print df

df.to_csv('results.csv')

result:

   age             firstName            lastName nationality     nickname
0   24  Nicolas Alexis Julio  N'Koulou N'Doubena    Cameroon  N. N'Koulou
1   26     Alexandre Dimitri        Song-Billong    Cameroon      A. Song

With pandas you can save it in csv, excel, etc (and maybe even directly in database).

And you can do some operations on data in table and show it as graph.

edited Jul 14, 2014 at 4:46

answered Jul 14, 2014 at 4:34

furas

149k12 gold badges121 silver badges171 bronze badges

3 Comments

user3718365 Over a year ago

I installed Pandas, but get an error when trying the above, "ImportError: No module named numpy".

monkut Over a year ago

pandas has a number of dependencies, it's best to install via pip

user3718365 Over a year ago

I haven't been able to use pip - I get some errors...but that's an issue for another thread altogether. I will keep trying though, thanks!

Collectives™ on Stack Overflow

Python - JSON to CSV table?

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related