使用 Pandas 将 JSON 转换为 CSV 输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48942801/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JSON to CSV output using pandas
提问by dataviz
I am trying to convert the below .jsonfile to .csvusing pandas.
我正在尝试将以下.json文件转换为.csv使用Pandas。
input json file name : my_json_file.json
输入 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"
}
]
}
desired output is my_csv_file.csv
期望的输出是 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()
采纳答案by Lambda
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):
更新（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(...)
回答by Stephen Rauch
If you load the json, and then feed the Dataframe the part of the json needed, then you can get it like:
如果您加载 json，然后将所需的 json 部分提供给 Dataframe，那么您可以得到如下结果：
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
结果L
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... []
