how to write nested json data to CSV using python

Question

I have data which I fetched from URL and I get a JSON containing the data as nested LIST and DICTIONARY. Now I want to write this data into CSV file.

{
"type": "abs",
"format": "cdg",
"version": "6",
"data": {
    "fff": {
        "version": "6",
        "id": "fff",
        "key": "266",
        "name": "fff",
        "title": "the Darkin Blade"
        "info": {
            "attack": 8,
            "defense": 4,
            "magic": 3,
            "difficulty": 4
        },

        "tags": [
            "Fighter",
            "Tank"
        ],

        "partype": "BloodWell",

        "stats": {
            "hp": 537.8,
            "hpperlevel": 85,
            "mp": 105.6
        }
    },
    "ggg": {
        "version": "6",
        "id": "ggg",
        "key": "103",
        "name": "ggg"
        "info": {
            "attack": 3,
            "defense": 4,
            "magic": 8,
            "difficulty": 5
        },
        "tags": [
            "Mage",
            "Assassin"
        ],
        "partype": "MP",
        "stats": {
            "hp": 514.4,
            "hpperlevel": 80,
            "mp": 334
        }
    }
}

How can I iterate through all the nested values and write them in a CSV file? I want the output for all data like this:

type format version data__ data__|__version data__|__id info_attack
abs  cdg     6       fff     6.13.1          fff         8
abs  cdg     6       ggg     6.13.1          ggg         3

Your question is a bit vague as written - maybe you could try to add an example of the output you're trying to get, as well as the code you've written so far. — Wander Nauta
– Wander Nauta, Commented Jul 5, 2016 at 7:44
what he difference between the data__ columns ? because you could not have two columns with the same name. — Ali SAID OMAR
– Ali SAID OMAR, Commented Jul 5, 2016 at 8:18

Rohan Khude · Accepted Answer · 2016-07-05 13:27:57Z

2

import csv
import json

json_file='sample.json'
with open(json_file, 'r') as json_data:
    x = json.load(json_data)

f = csv.writer(open("test.csv", "w"))

f.writerow(["type", "format", "version", "data__","data__|__version","data__|__id","info_attack","info_defense"])
types=x["type"]
format=x["format"]
root_version=x["version"]
for key in x["data"]:
    f.writerow([types, 
                format, 
                root_version, 
                x["data"][key]["name"],
                x["data"][key]["version"],
                x["data"][key]["id"],
                x["data"][key]["info"]["attack"],
                x["data"][key]["info"]["defense"]])

Output

type,format,version,data__,data__|__id
abs,cdg,6,fff,fff
abs,cdg,6,ggg,ggg

edited Jul 5, 2016 at 13:27

answered Jul 5, 2016 at 9:27

Rohan Khude

5,0015 gold badges52 silver badges48 bronze badges

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

8 Comments

Sachin Jaiswal Over a year ago

Its look goog but how I write data of "info" as above described format

Rohan Khude Over a year ago

tell me the structure of output for "info"

Sachin Jaiswal Over a year ago

just add column like this in the abouve data data__|__info__attack data__|__info__defense

Rohan Khude Over a year ago

i have added attack and defense column. If answer is satisfied please accept it. Thank you

Sachin Jaiswal Over a year ago

is there any other method to do this automatically, without writing separate key as f.writerow() because my JSON data contains so many dictionary and list within it.

|

Collectives™ on Stack Overflow

how to write nested json data to CSV using python

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related