Using Python to conver JSON to CSV

user_11909200 0
Viewed 63

I have tried a few different ways using Panda to import my JSON to a csv file.

import pandas as pd
df = pd.read_json("CDMP_E2.json")
df.ts_csv("CDMP_Output.csv")

The problem is when I run that code it makes the output all in one "column".

The column header shows up as Credit-NoSQL. Then the data in the column is everything from each "object"

'date':'2021-08-01','type':'CARD','amount':'100'

So it looks like this:

Credit-NoSQL

'date':'2021-08-01','type':'CARD','amount':'100'

I would instead expect to see date, type and amount as the headers instead.

account     date          type     amount     returneddate
ABCD         2021-08-01    CARD    100  
EFGHI        2021-08-01    CARD    150          2021-08-04

My JSON file looks as such:

[
     {
          "Credit-NoSQL":{
               "account":"ABCD"
               "date":"2021-08-01",
               "type":"CARD",
               "amount":"100"
     }
},
{
          "Credit-NoSQL":{
               "account":"EFGHI"
               "date":"2021-08-02",
               "type":"CARD",
               "amount":"150"
               "returneddate":"2021-08-04"
          }
     }
]

so I am not sure if it is the way my JSON file is set up with it's list and such or if I am missing something in my python command. I am new to python and still learning so I am at a loss at what I can do next.

pandas csv python json
1 Answers

No need to use pandas for this.

import json, csv

with open("CDMP_E2.json") as json_file:
    data = [item['Credit-NoSQL'] for item in json.load(json_file)]

# Get the union of all dictionary keys
fieldnames = set()
for row in data:
    fieldnames |= row

with open("CDMP_Output.csv", "w") as csv_file:
    cwrite = csv.DictWriter(csv_file, fieldnames = fieldnames)
    cwrite.writeheader()
    cwrite.writerows(data)
user_14918950
Related