Convering json to csv using Python

Question

I'm think what I'm trying to do is very simple. I'm converting .json to a .csv file, but struggling to get exactly what I want. I'm sure I'm being silly, but can't find an answer on this site. Please point out what my silly mistake is!

I'm using the following Python code:

import csv, json

inputFile = open('results_20190214b.txt') outputFile = open('results_20190214.csv', 'w', newline='')

data = json.load(inputFile) inputFile.close

output = csv.writer(outputFile)

for row in data:
    output.writerow([row])

outputFile.close()

My json looks like this:

 {
                "AccumulatedNewCapacity[UTOPIA,E01,1999]": {
                    "Value": 0.0225798659394097
                },
                "AccumulatedNewCapacity[UTOPIA,E01,2000]": {
                    "Value": 0.149302162579271
                },
                "AccumulatedNewCapacity[UTOPIA,E01,2001]": {
                    "Value": 0.354595177554284
                },
                "AccumulatedNewCapacity[UTOPIA,E01,2002]": {
                    "Value": 0.553976916527268
                },
                "AccumulatedNewCapacity[UTOPIA,E01,2003]": {
                    "Value": 0.749394931502283
                }, ETC

This code is successfully printing the field names to the CSV, but I am not getting any of the values. E.g:

AccumulatedNewCapacity[UTOPIA,E01,1999]
AccumulatedNewCapacity[UTOPIA,E01,2000]
AccumulatedNewCapacity[UTOPIA,E01,2001]

Any ideas greatly appreciated. Thanks!

score 1 · Accepted Answer · answered Feb 24 '19 at 21:15

You need to iterate the rows as it's also described in here; https://stackoverflow.com/a/26660785/11110555

So, you can do the following

for row in data.items():
    output.writerow(row)

However, keep in mind you'll be ending up tuples resulting like following on CSV;

"AccumulatedNewCapacity[UTOPIA,E01,1999]",{'Value': 0.0225798659394097}
"AccumulatedNewCapacity[UTOPIA,E01,2000]",{'Value': 0.149302162579271}
"AccumulatedNewCapacity[UTOPIA,E01,2001]",{'Value': 0.354595177554284}
"AccumulatedNewCapacity[UTOPIA,E01,2002]",{'Value': 0.553976916527268}
"AccumulatedNewCapacity[UTOPIA,E01,2003]",{'Value': 0.749394931502283}

I presume you want to have just part in borders for AccumulatedNewCapacity and Value to CSV

for row in data.items():
    # Getting the first part of AccumulatedNewCapacity
    basePart = row[0]
    #Extracting text inside borders
    baseStr = basePart[basePart.find("[")+1:basePart.find("]")]

    # Getting the Value part
    valuePart = row[1]
    #Extracting value
    valueStr = valuePart['Value']

    output.writerow([baseStr,valueStr])

This will be leading you to have following result on CSV since it's written as strings but not as list.

"UTOPIA,E01,1999",0.0225798659394097
"UTOPIA,E01,2000",0.149302162579271
"UTOPIA,E01,2001",0.354595177554284
"UTOPIA,E01,2002",0.553976916527268
"UTOPIA,E01,2003",0.749394931502283

Therefore you need to convert values to list and append value into it.

import csv, json

inputFile = open('results.txt')
outputFile = open('results_20190214.csv', 'w', newline='\n')

data = json.load(inputFile)
inputFile.close

output = csv.writer(outputFile)

for row in data.items():
    # Getting the first part of AccumulatedNewCapacity
    basePart = row[0]
    # Extracting text inside borders
    baseStr = basePart[basePart.find("[")+1:basePart.find("]")]

    # Splitting values with comma and turning into list
    writeStr = baseStr.split(",")
    # Getting the Value part
    valuePart = row[1]
    #Extracting value
    valueStr = valuePart['Value']

    # Adding value to the list
    writeStr.append(valueStr)

    # Writing into CSV
    output.writerow(writeStr)

outputFile.close()

Edit: Forgot to add the result. It'll be resulting like below

UTOPIA,E01,1999,0.0225798659394097
UTOPIA,E01,2000,0.149302162579271
UTOPIA,E01,2001,0.354595177554284
UTOPIA,E01,2002,0.553976916527268
UTOPIA,E01,2003,0.749394931502283

score 0 · Answer 2 · answered Feb 24 '19 at 20:44

Try this:

import csv 
with open('results_20190214b.txt', 'r') as in_file:
    stripped = (line.strip() for line in in_file)
    lines = (line.split(",") for line in stripped if line)
    with open('results_20190214.csv', 'w') as out_file:
        writer = csv.writer(out_file)
        writer.writerow(( 'title', 'intro')) # here enter your rows titles
        writer.writerows(lines)

score 0 · Answer 3 · answered Feb 25 '19 at 02:46

Thanks both for your responses - super helpful.

I actually wanted to keep the variable name in the csv as well, so have ended up with a slight variant of Koray B's suggested code:

for row in data.items():
    variablePart = row[0]
    variableStr = variablePart[:variablePart.find("[")]
    writeStr = []
    writeStr.append(variableStr)
    variableIndexStr = variablePart[variablePart.find("[")+1:variablePart.find("]")]
    variableIndexStrSplit = variableIndexStr.split(",")
    for i in variableIndexStrSplit:
        indexStr = i
        writeStr.append(indexStr)

    valuePart = row[1]
    valueStr = valuePart['Value']
    writeStr.append(valueStr)

    output.writerow(writeStr)

Convering json to csv using Python

3 Answers3