how to convert json to csv in python

Question

Following is my json file input

{"userID": "679d3bad-155e-4b39-9ff7-7d564f408942", "Is salary credited before 5th": "Yes", "Avg Salary of last 3 months": 15453.33, "Avg Salary of last 6 months": 15290.5, "Avg Balance before salary of last 3 months": 113.15, "Avg Balance before salary of last 6 months": 105.22}

Code

    with open('/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.json', "r") as f:
        BankData = json.loads(f.read())
    x = json.loads(json.dumps(BankData))
    f = csv.writer(open("/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.csv", "w"))
    f.writerow(["userID", "Is salary credited before 5th", "Avg Salary of last 3 months", "Avg Salary of last 6 months", "Avg Balance before salary of last 3 months", "Avg Balance before salary of last 6 months"])

    for y in x:
        f.writerow([x["userID"], x["Is salary credited before 5th"],
                    x["Avg Salary of last 3 months"],
                    x["Avg Salary of last 6 months"],
                    x["Avg Balance before salary of last 3 months"],
                    x["Avg Balance before salary of last 6 months"]])

Output

userID,Is salary credited before 5th,Avg Salary of last 3 months,Avg Salary of last 6 months,Avg Balance before salary of last 3 months,Avg Balance before salary of last 6 months
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22
679d3bad-155e-4b39-9ff7-7d564f408942,Yes,15453.33,15290.5,113.15,105.22

So, here I did got my answer but instead of printing it once, It is printing 7 times.. How do I fix this.

Why do you do this? x = json.loads(json.dumps(BankData))? In any event, x is a dict object. You iterate over it, for y in x: which will iterate over it's keys. But you never even use y, and simply write the same row 7 times (the number of keys). — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Jan 2, 2019 at 6:22

Menglong Li · Accepted Answer · 2019-01-02 06:32:06Z

5

You can also use pandas to handle dataframe,

dct = {"userID": "679d3bad-155e-4b39-9ff7-7d564f408942", "Is salary credited before 5th": "Yes", "Avg Salary of last 3 months": 15453.33,
       "Avg Salary of last 6 months": 15290.5, "Avg Balance before salary of last 3 months": 113.15, "Avg Balance before salary of last 6 months": 105.22}

import pandas as pd

df = pd.DataFrame.from_records(dct, index=[0])

df.to_csv('outputfile.csv')

answered Jan 2, 2019 at 6:32

Menglong Li

2,25517 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakesh · Accepted Answer · 2019-01-02 06:28:44Z

BankData is a dict you do not need to iterate it. You can directly access the values using the key.

Ex:

import csv
import json

with open('/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.json') as infile:
    BankData = json.loads(infile.read())

with open("/Users/vrindabv/Documents/PycharmProjects/BankStatementEngine/test.csv", "w") as outfile:
    f = csv.writer(outfile)
    f.writerow(["userID", "Is salary credited before 5th", "Avg Salary of last 3 months", "Avg Salary of last 6 months", "Avg Balance before salary of last 3 months", "Avg Balance before salary of last 6 months"])
    f.writerow([BankData["userID"], BankData["Is salary credited before 5th"],
                BankData["Avg Salary of last 3 months"],
                BankData["Avg Salary of last 6 months"],
                BankData["Avg Balance before salary of last 3 months"],
                BankData["Avg Balance before salary of last 6 months"]])

The json library can also read file objects, without getting the string representation first: json.load(infile)

ShivaGaire · Accepted Answer · 2019-01-02 07:57:25Z

0

You can do this: Read your JSON and write-in a CSV file with importing json and csv modules

import json, csv
from collections import OrderedDict #To maintain key value pair order
_json=json.loads(open('data.json', 'r').read(), object_pairs_hook=OrderedDict) 
out=open('converted.csv', 'w')
writer = csv.writer(out)               #create a csv.write
writer.writerow(_json[0].keys())      # header row
for row in _json:
    writer.writerow(row.values())

answered Jan 2, 2019 at 7:57

ShivaGaire

2,8972 gold badges23 silver badges34 bronze badges

3 Comments

Bhaskar Das Over a year ago

Your code will generate the output at the CSV with the syntax along in the JSON values.

ShivaGaire Over a year ago

Yes @BhaskarDas, It will generate JSON for provided csv

vinsent paramanantham Over a year ago

writer.writerow(_json[0].keys()) # header row KeyError: 0 getting the above error

MrIonic · Accepted Answer · 2020-01-22 16:23:44Z

GeekSambhu's solution worked for me with a minor modification. I modified it a little because like Vinsent, I saw a KeyError. People may be getting the KeyError if the JSON structure has a top level object holding the array of the rows of data (this is considered a JSON best practice). Assuming a top level object called 'data', you would change only two lines of code of GeekSambhu's solution.

writer.writerow(_json['data'][0].keys()) # header row
for row in _json['data']:

Collectives™ on Stack Overflow

how to convert json to csv in python

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

3 Comments

Comments

Linked

Related