python - 使用 pandas 将 JSON 输出为 CSV

Question

我正在尝试使用 pandas 将下面的 .json 文件转换为 .csv。

输入json文件名: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"
      }
   ]
}

期望的输出是 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

我正在使用下面的代码，但无法获得正确的输出。任何人都可以帮助我获得正确的代码吗？

代码:

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()

Answer 1

试试这个:

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)

更新(12/3/2018):

我看了文档，有一个方便的方法:

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

更新(11/7/2021):

pandas.io.json.json_normalize 已弃用，请改用 pandas.json_normalize

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