Generate csv from nested json python

Question

I have following nested json file, which I need to convert in pandas dataframe, the main problem is there is only one unique item in the whole json and it is very deeply nested.

I tried to solve this problem with the following code, but it gives repeating output.

[{
"questions": [{
        "key": "years-age",
        "responseKey": null,
        "responseText": "27",
        "responseKeys": null
    },
    {
        "key": "gender",
        "responseKey": "male",
        "responseText": null,
        "responseKeys": null
    }

],
"transactions": [{
        "accId": "v1BN3o9Qy9izz4Jdz0M6C44Oga0qjohkOV3EJ",
        "tId": "80o4V19Kd9SqqN80qDXZuoov4rDob8crDaE53",
        "catId": "21001000",
        "tType": "80o4V19Kd9SqqN80qDXZuoov4rDob8crDaE53",
        "name": "Online Transfer FROM CHECKING 1200454623",
        "category": [
            "Transfer",
            "Acc Transfer"
        ]
    }

],
"institutions": [{
    "InstName": "Citizens company",
    "InstId": "inst_1",
    "accounts": [{
        "pAccId": "v1BN3o9Qy9izz4Jdz0M6C44Oga0qjohkOV3EJ",
        "pAccType": "depo",
        "pAccSubtype": "check",
        "_id": "5ad38837e806efaa90da4849"
    }]

}]
}]

I need to convert this to pandas dataframe as follows:

 id                        pAccId                                  tId      

 5ad38837e806efaa90da4849  v1BN3o9Qy9izz4Jdz0M6C44Oga0qjohkOV3EJ   80o4V19Kd9SqqN80qDXZuoov4rDob8crDaE53

The main problem I am facing is with the "id" as it very deeply nested which is the only unique key for the json.

here is my code:

  import pandas as pd
  import json
  with open('sub.json') as f:
       data = json.load(f)

  csv = ''
  for k in data:
       for t in k.get("institutions"):
           csv += k['institutions'][0]['accounts'][0]['_id']
           csv += "\t"
           csv += k['institutions'][0]['accounts'][0]['pAccId']
           csv += "\t"
           csv += k['transactions'][]['tId']
           csv += "\t"
           csv += "\n"

text_file = open("new_sub.csv", "w")
text_file.write(csv)
text_file.close()

Hope above code makes sense, as I am new to python.

Where is your problem / problem description / exception / what you hoped to get solved by asking here? Does your code not work? What is the expected outcome? what is the actual outcome? Please reread over how to ask good questions - there is a severe lack of context/information here :) — Patrick Artner
– Patrick Artner, Commented Apr 21, 2018 at 7:53

Oluwafemi Sule · Accepted Answer · 2018-04-21 08:25:08Z

1

Read the JSON file and create a dictionary of account pAccId keys mapped to account. Build the list of transactions as well.

with open('sub.json', 'r') as file:
    records = json.load(file)
    accounts = {
       account['pAccId']: account 
       for record in records 
       for institution in record['institutions']
       for account in institution['accounts']
    }
    transactions = (
        transaction 
        for record in records 
        for transaction in record['transactions']
    )

Open a csv file. For each transaction, get account for it from the accounts dictionary.

with open('new_sub.csv', 'w') as file:
    file.write('id, pAccId, tId\n')

    for transaction in transactions:
        pAccId = transaction['accId']
        account = accounts[pAccId]
        _id = account['_id']
        tId = transaction['tId']

        file.write(f"{_id}, {pAccId}, {tId}\n")

Finally, read csv file to pandas.DataFrame.

df = pd.read_csv('new_sub.csv')

answered Apr 21, 2018 at 8:25

Oluwafemi Sule

39.3k1 gold badge63 silver badges88 bronze badges

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

2 Comments

user9116643 Over a year ago

Getting syntax error while generating csv. I think file.write(f"{_id}, {pAccId}, {tId}\n"), f is causing problem here when removing it, code runs but csv file remains empty.

Oluwafemi Sule Over a year ago

f-strings are new in Python 3.6. You can use the normal format strings if you're running a version of Python that is older.

Collectives™ on Stack Overflow

Generate csv from nested json python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest