Using Python to conver JSON to CSV

Question

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.

When you call read_json() you have to specify how the df is indexed from the JSON. — Barmar
– Barmar, Commented Aug 4, 2021 at 19:17
Sorry @Barmar I am new to python, what does that mean to specify how the df is indexed? — Sotark
– Sotark, Commented Aug 4, 2021 at 19:34
You have 2 levels of nested dictionaries. You need to tell it that the rows in the dataframe should be values in the 2nd level, not the 1st level. Also, where does Credit-NoSQL go in the dataframe and CSV? — Barmar
– Barmar, Commented Aug 4, 2021 at 19:36

Barmar · Accepted Answer · 2021-08-04 22:07:06Z

2

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)

edited Aug 4, 2021 at 22:07

answered Aug 4, 2021 at 19:43

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

19 Comments

Sotark Over a year ago

If I have multiple JSONS with different field names, would I have to create one of these for each one? And list out the fieldname as it appears in the JSON?

Barmar Over a year ago

I've updated the code to get the field names from the first element of the list.

Sotark Over a year ago

I just attempted to run this and recieved an error that said json.load is not defined.

Barmar Over a year ago

Are you sure you put import json, csv at the beginning?

Sotark Over a year ago

yes I copied exactly how you have it, updating the file name to exaclty what I am using.

|

Collectives™ on Stack Overflow

Using Python to conver JSON to CSV

1 Answer 1

19 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Related