kkjj kkjj - 4 months ago 10
JSON Question

From JSON file to CSV file

I have a snippet of my json file below. Is there any way to use Python and transform this to a nice CSV file? So things like text and sentiment would have its own columns?

{
"status":"OK",
"totalTransactions":"1",
"language":"english",
"url":"http://well.com",
"results":[
{
"text":"food",
"sentiment":{
"score":"0.456369",
"type":"positive"
}
}
]
}{
"status":"OK",
"totalTransactions":"1",
"language":"english",
"warningMessage":"truncated-oversized-text-content",
"url":"http://www.times.com",
"results":[
{
"text":"food",
"sentiment":{
"score":"0.678684",
"type":"positive"
}
}
]
}


If not I would like to pull specific info from it. I've tried this code but keep getting the following error. I suspect it has something to do with the brackets/formatting of the json? How can I fix this best?

import json
from pprint import pprint

with open('data.json') as data_file:
data = json.load(data_file)
pprint(data)

ValueError: Extra data: line 15 column 2 - line 30 column 2 (char 367 - 780)

Answer

Here's an answer to the first part of your question that addresses the improperly formatted JSON file problem. It attempts to convert what's in the file into list of objects (dictionaries) and names the result data:

import json
import tempfile

# Fix the invalid json data and load it.
with tempfile.TemporaryFile() as temp_file:
    temp_file.write('[\n')
    with open('json_to_csv.json', 'rb') as data_file:
        for line in data_file:
            temp_file.write(line.replace('}{', '},{'))
    temp_file.write(']\n')
    temp_file.seek(0)  # rewind
    data = json.load(temp_file)

print(json.dumps(data, indent=4))  # show result