Python Converting JSON to CSV TypeError

Question

I am attempting to write a CSV file from JSON data that is coming from the PushShift API but am running into a TypeError. My code is below

import requests
import csv
import json
from urllib.request import urlopen

url = 'https://api.pushshift.io/reddit/comment/search/?subreddit=science&filter=parent_id,id,author,created_utc,subreddit,body,score,permalink'
page = requests.get(url)
page_json = json.loads(page.text)
print(page.text)
f = csv.writer(open("test.csv",'w+', newline=''))
f.writerow(["id", "parent_id", "author", "created_utc","subreddit", "body", "score"])
for x in page_json:
f.writerow([x["data"]["id"],
            x["data"]["parent_id"],
            x["data"]["author"],
            x["data"]["created_utc"],
            x["data"]["subreddit"],
            x["data"]["body"],
            x["data"]["score"]])

The error I am getting is this:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-3-82784a93576b> in <module>()
 11 f.writerow(["id", "parent_id", "author", "created_utc","subreddit", "body", "score"])
 12 for x in page:
---> 13     f.writerow([x["data"]["id"],
     14                 x["data"]["parent_id"],
     15                 x["data"]["author"],

TypeError: byte indices must be integers or slices, not str

I attempted the solution here: How can I convert JSON to CSV?

Which may or may not be the actual problem I am running into. Any suggestions would be greatly appreciated!

It appears either `x` or `x["data"]` is not a dictionary but rather a string. Try printing out your values to debug and see what the actual structure of `x` is — sshashank124, May 17 '18 at 13:32
Sorry, the page is a typo. When I run the correct code, it gives this error TypeError: string indices must be integers — dhrice, May 17 '18 at 13:40

Sasha Tsukanov · Accepted Answer · 2018-05-17T13:53:16.580

You have "data" with array of entries for your csv rows, not array of objects each with key "data". So you need to first access the "data":

page_json = json.loads(page.text)['data']

and then iterare over it:

for x in page_json:
    f.writerow([x["id"],
                x["parent_id"],
                x["author"],
                x["created_utc"],
                x["subreddit"],
                x["body"],
                x["score"]])

Notice that you need to iterate over the JSON object not the request.

You can also refactor the code to get this:

columns = ["id", "parent_id", "author", "created_utc", "subreddit", "body", "score"]
f.writerow(columns)
for x in page_json:
    f.writerow([x[column] for column in columns])

Python Converting JSON to CSV TypeError

1 Answers1