使用 Pandas 将 JSON 转换为 CSV 输出 [英] JSON to CSV output using pandas

问题描述

我正在尝试使用 Pandas 将以下 .json 文件转换为 .csv.

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

{  
   "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

我正在使用以下代码，但无法获得正确的输出.有人可以帮我正确获取代码吗?

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?

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

推荐答案

试试这个:

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

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

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

这篇关于使用 Pandas 将 JSON 转换为 CSV 输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！