JSON to CSV output using pandas

Question

I am trying to convert the below .json file to .csv using pandas.

input json file name : my_json_file.json

{  
   "profile_set":[  
      {  
         "doc_type":"PROFILE",
         "key":"123",
         "mem_list":{  
            "mem_num":"10001",
            "current_flag":"Y",
            "mem_flag":[  

            ],
            "child_mem_list":{  
               "child_mem_num":[  

               ]
            }
         },
         "first_name":"Robert",
         "middle_name":[  

         ],
         "last_name":"John",
         "created_datetime":"2018-01-06T12:52:09"
      },
      {  
         "doc_type":"PROFILE",
         "key":"456",
         "mem_list":{  
            "mem_num":"10002",
            "current_flag":"Y",
            "mem_flag":"Y",
            "child_mem_list":{  
               "child_mem_num":[  

               ]
            }
         },
         "first_name":"Lily",
         "middle_name":[  

         ],
         "last_name":"Hubert",
         "created_datetime":"2018-01-07T11:32:07"
      }
   ]
}

desired output is `my_csv_file.csv`

doc_type    key mem_num current_flag    mem_flag    child_mem_num   first_name  middle_name last_name   created_datetime
PROFILE     123 1001    Y                       Robert              John        2018-01-06T12:52:09
PROFILE     456 1002    Y       Y               Lily                Hubert      2018-01-07T11:32:07

I am using the below code but I am not able to get the correct output. Can anybody help me to get the code right?

Code:

import csv
import json
import pandas as pd
from pandas.io.json import json_normalize

def json_csv():

    with open('my_json_file.JSON') as data_file:
        data=json.load(data_file)
    normalized_df = pd.io.json.json_normalize(data)
    normalized_df.to_csv('my_csv_file.csv',index=False)
    return

def main():        

    json_csv() 

main()

Alexis.Rolland · Accepted Answer · 2021-07-11 05:56:27Z

7

Try this:

import pandas as pd

def parse_nested_json(json_d):
    result = {}
    for key in json_d.keys():
        if not isinstance(json_d[key], dict):
            result[key] = json_d[key]
        else:
            result.update(parse_nested_json(json_d[key]))
    return result

json_data = pd.read_json("my_json_file.json")
json_list = [j[1][0] for j in json_data.iterrows()]
parsed_list = [parse_nested_json(j) for j in json_list]
result = pd.DataFrame(parsed_list)
result.to_csv("my_csv_file.csv", index=False)

Update(12/3/2018):

I read the docs, there is a convenient way:

from pandas.io.json import json_normalize
df = json_normalize(data["profile_set"])
df.to_csv(...)

Update(11/7/2021):

pandas.io.json.json_normalize is deprecated, use pandas.json_normalize instead

from pandas import json_normalize
df = json_normalize(data["profile_set"])
df.to_csv(...)

edited Jul 11, 2021 at 5:56

Alexis.Rolland

6,4217 gold badges58 silver badges84 bronze badges

answered Feb 23, 2018 at 8:41

Lambda

1,3941 gold badge9 silver badges12 bronze badges

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

2 Comments

dataviz Over a year ago

Thanks a lot. That's working for me. i just modified the last line to result.to_csv("my_csv_file.csv", index=False,columns=['doc_type','key','mem_num','current_flag','mem_flag','child_mem_num','first_name','middle_name','last_name','created_datetime'])

Lambda Over a year ago

I think the key is parsing the nested json, you can use other ways to write the data to a csv file.

Stephen Rauch · Accepted Answer · 2018-02-23 07:12:26Z

1

If you load the json, and then feed the Dataframe the part of the json needed, then you can get it like:

Code:

def json_csv(filename):
    with open(filename) as data_file:
        data = json.load(data_file)
    return pd.DataFrame(data['profile_set'])

Test Code:

print(json_csv('file1'))

ResultsL

      created_datetime doc_type first_name  key last_name  \
0  2018-01-06T12:52:09  PROFILE     Robert  123      John   
1  2018-01-07T11:32:07  PROFILE       Lily  456    Hubert   

                                            mem_list middle_name  
0  {'mem_num': '10001', 'current_flag': 'Y', 'mem...          []  
1  {'mem_num': '10002', 'current_flag': 'Y', 'mem...          []

answered Feb 23, 2018 at 7:12

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

1 Comment

dataviz Over a year ago

Thank you. But i still need to segregate mem_num,mem_flag,child_mem_num,current_flag and middle_name into separate fields in the final output. Could you please help

Collectives™ on Stack Overflow

JSON to CSV output using pandas

input json file name : my_json_file.json

desired output is `my_csv_file.csv`

Code:

2 Answers 2

2 Comments

Code:

Test Code:

ResultsL

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

input json file name : my_json_file.json

desired output is my_csv_file.csv

Code:

2 Answers 2

2 Comments

Code:

Test Code:

ResultsL

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

desired output is `my_csv_file.csv`