Issues converting json to csv with Python

Question

I can't figured out how to get the number values into a csv file in a col2 from the json file below. All I get is VAL1, VAL2, and VAL3 (see csv file sample at the end). I would like to get the json data like it is showing right below this text in the csv output file. Thank you so much for your help.

COL1,COL2
VAL1,123.777
VAL2,456.888
VAL3,789.999

file.json

{
    "success": true,
    "timestamp": 1663273564,
    "source": "ABC",
    "quotes": {
        "VAL1": 123.777,
        "VAL2": 456.888,
        "VAL3": 789.999
    }
}

Python export code:

import json
import csv
with open("C:file.json") as file:
    data = json.load(file)
    
fname = "c:/output.csv"

with open(fname, "w") as file:
    csv_file = csv.writer(file)
    csv_file.writerow(["COL1","COL2"])
    for item in data["quotes"]:
        csv_file.writerow([item[0:4]])

output.csv file

COL1,COL2
VAL1
VAL2
VAL3

try for item in data["quotes"].items(): that should get you closer... — Joran Beasley
– Joran Beasley, Commented Sep 26, 2022 at 19:33

Martin Evans · Accepted Answer · 2022-09-26 20:03:07Z

If you use your approach, the for loop will return one dictionary key at a time. You can then use this to look up the associated value as follows:

import json
import csv

with open("file.json") as f_input:
    data = json.load(f_input)
    
fname = "output.csv"

with open(fname, "w", newline="") as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(["COL1","COL2"])
    
    for key in data["quotes"]:
        csv_output.writerow([key, data["quotes"][key]])

Alternatively, you can use .items() to return both the key and the value in the loop, giving you:

import json
import csv

with open("file.json") as f_input:
    data = json.load(f_input)
    
fname = "output.csv"

with open(fname, "w", newline="") as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(["COL1","COL2"])
    
    for key, value in data["quotes"].items():
        csv_output.writerow([key, value])

Dmitriy Neledva · Accepted Answer · 2022-09-26 19:40:27Z

0

this is not about json or csv it's about working with dictionaries

a = {
    "success": True,
    "timestamp": 1663273564,
    "source": "ABC",
    "quotes": {
        "VAL1": 123.777,
        "VAL2": 456.888,
        "VAL3": 789.999
        }
    }

for i in a['quotes']:               # - the thing you have
    print([i[0:4]])
#output:                
    # ['VAL1']
    # ['VAL2']
    # ['VAL3']


for k,v in a['quotes'].items():     # - the thing you need to have  
    print(k,v)
#output:                
    # VAL1 123.777
    # VAL2 456.888
    # VAL3 789.999

answered Sep 26, 2022 at 19:40

Dmitriy Neledva

8646 silver badges11 bronze badges

Collectives™ on Stack Overflow

Issues converting json to csv with Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related