Convert JSON to CSV Nested JSON with Python Pandas

Question

I have a python script which can take a JSON file and pulls out specific columns I want with the Pandas json_normalize function. But I have a nested JSON set of values within the JSON that I am trying to pull out but cant get the code to properly get those values.

Below is the JSON value. The top tier is "cardEditions" within that tier is "cardDetails". I want to grab some of the displayName and value details from this nested json and put them into the csv with the cardEditions and the editionNo value.

Looking for the output to be in a CSV pipe delimited like the following with the displayValues as the headers from the nested json.

editionNo	Name	Edition	Position
666	Matt Hu	1st Edition	Center Field

{
    "cardEditions": [{
        "editionNo": 666,
        "id": 1111,
        "cardDetails": [{
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Name",
                "value": "Matt Hu"
            },
            {
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Edition",
                "value": "1st Edition"
            },
            {
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Position",
                "value": "Center Field"
            }

        ],
        "cardStatus": "NA"
    }]
}

Check-stackoverflow.com/questions/73413857/…

Divyank
– Divyank

2022-08-20 16:53:27 +00:00
Commented Aug 20, 2022 at 16:53 — Divyank
– Divyank, Commented Aug 20, 2022 at 16:53
it wasnt working using that

Justin Staugaitis
– Justin Staugaitis

2022-08-20 17:09:18 +00:00
Commented Aug 20, 2022 at 17:09 — Justin Staugaitis
– Justin Staugaitis, Commented Aug 20, 2022 at 17:09

Michael Hodel · Accepted Answer · 2022-08-20 17:37:51Z

1

import pandas as pd

d = {
    "cardEditions": [{
        "editionNo": 666,
        "id": 1111,
        "cardDetails": [{
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Name",
                "value": "Matt Hu"
            },
            {
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Edition",
                "value": "1st Edition"
            },
            {
                "valueType": "Text",
                "displayValueType": "Text",
                "displayName": "Position",
                "value": "Center Field"
            }

        ],
        "cardStatus": "NA"
    }]
}

df = pd.DataFrame(columns=['editionNo', 'Name', 'Edition', 'Position'])
for i, edition in enumerate(d['cardEditions']):
    no = edition['editionNo']
    vals = [details['value'] for details in edition['cardDetails']]
    df.loc[i, :] = (no, *vals)
print(df)

prints

index	editionNo	Name	Edition	Position
0	666	Matt Hu	1st Edition	Center Field

answered Aug 20, 2022 at 17:37

Michael Hodel

3,0381 gold badge8 silver badges12 bronze badges

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

3 Comments

Justin Staugaitis Over a year ago

This works and when I select specific data out of the json. But when I try and load my whole JSON file it seems like I run into some issues cause of the data im using. I expanded your code above to my 10 columns. I can get the code to run with the 10 columns and can get 3 rows to export. But when I try and run my whole file I get. ValueError: cannot copy sequence with size 2 to array axis with dimension 10

Justin Staugaitis Over a year ago

So some of my JSON data that I get doesnt have the cardDetails Nested JSON. Which I think is causing the issue. Is there a way to skip records if they dont have the cardDetails nested JSON?

Justin Staugaitis Over a year ago

Or if there is a way I can skip records with the missing cardDetails?

Rabinzel · Accepted Answer · 2022-08-20 18:00:50Z

0

Here is another solution with json_normalize and pivot.

# where "j" is your json data
out = (
    pd.json_normalize(json.loads(j)['cardEditions'], record_path=['cardDetails'], meta='editionNo')
    .drop(['valueType', 'displayValueType'],axis=1)
    .pivot(index='editionNo', columns='displayName', values='value')
    .rename_axis(columns=None)
    .reset_index()
)
print(out)

Output:

   editionNo      Edition     Name      Position
0        666  1st Edition  Matt Hu  Center Field

If somebody knows how to use json_normalize with record_path of which we don't want all fields, I'd really like to know. I had to drop to columns from the record_path because I don't know how to skip them in the first place.

answered Aug 20, 2022 at 18:00

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Convert JSON to CSV Nested JSON with Python Pandas

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related