Python, parse nested JSON to make it flat for CSV

Question

I'm trying to store API output into CSV/db and can not figure out how I can make for those Key in "tierList". One row in my case should be on bin and I need key as a columns in my output. Is it possible to do with pd.JSON_Normalize ? Please direct me to the right lib/tool. Thanks to all.

Please refer to compact test python script below. I don't understand why I can only use record_path='memberList. Anything else gives an error. According all theory I should be able to use record_path=memberRiskData and add rest of columns with meta.

import json
import os
import pandas as pd

json_file = '''
{  "content":"BIN REST",   "riskMonth":"20250401",   "pagination":{      "currentPage":1,      "totalPages":26   },
   "memberList":[
      {  "bin":"22222","firstName":"MARIA", "lastName":"PLACARD",
         "memberRiskData":{
              "strata":"East",  "postParameter":"",  
              "tierList":[
               { "riskTier":"AdverseSubdomainTier",
                 "tierValue":"High"               },
               {  "riskTier":"SocialDomainTier",
                  "tierValue":"Med"               }            ]         }      }   ]} '''

data = json.loads(json_file)
print('.......type =',type(data))
print(data.items())
print(data['memberList'][0])
df = pd.json_normalize(data, record_path='memberList') # , meta=['strata','content']) TBD....

print (df) 
df.to_csv('c:/out.csv', index=False)

My current output is below. Somehow I need to break column memberRiskData.tierList into few for each key.

And this is my desired output:

This is less of a Python problem than a logic problem. Describe to yourself, in English (or whatever your preferred language is) how you want the data to be represented. Then describe what needs to change from what you have. After that, writing the code should be relatively simple. — Chris Maden
– Chris Maden, Commented Nov 15 at 19:08
Don't understand why comment above was so liked, my question is perfect, — user1982778
– user1982778, Commented yesterday

ricardkelly · Accepted Answer · 2025-11-15 21:02:42Z

2

The challenge you have here is that the JSON is turned into three different levels of structure that you want to handle differently.

First level (list): "memberList" should become the rows of your CSV
Second level (dict): "memberRiskData" should (for the non-list values of the dict) become columns named based on the keys in the dict
Third level (list): "tierList" should become columns named for the values indexed by one dict key, with values taken from the values indexed by the other dict key.

There isn't a function that will do that for you all in one step, so pandas is likely not going to help you much more than just writing the CSV.

Here's how I would do it, using native Python for the manipulation:

def processTierList(o):
    return {i['riskTier']: i['tierValue'] for i in o}

def processRiskData(o):
    return {k:o[k] for k in o.keys() if not k == 'tierList'} | processTierList(o['tierList'])

def processMember(o):
    return {k:o[k] for k in o.keys() if not k == 'memberRiskData'} | processRiskData(o['memberRiskData'])

Then processMember will handle each row to produce a flat dict, and the resulting list can then be written to a CSV either with the standard library module or with pandas.

json_file = ''' ... '''
data = json.loads(json_file)
output = [processMember(member) for member in data['memberList']]

answered Nov 15 at 21:02

ricardkelly

2,3631 gold badge6 silver badges21 bronze badges

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

2 Comments

user1982778 Nov 15 at 22:43

Thanks so much Mr. Richard !! Problem solved.

user1982778 Nov 16 at 2:36

Amazing, thanks again Richard, I also will try to append 0 level content to each row

Collectives™ on Stack Overflow

Python, parse nested JSON to make it flat for CSV

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

Related