Home > front end >  Issues converting json to csv with Python
Issues converting json to csv with Python

Time:09-27

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

CodePudding user response:

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])

CodePudding user response:

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
  • Related