使用Python将Json文件嵌套到csv

Question

我是python的新手，需要帮助将嵌套的json文件转换为csv。文件名：users.json文件路径：C:\_apps/test

---

文件内容：

{
"count":  3,
"next":  null,
"previous":  null,
"results":  [
{
"user": "@{id=1111; email=[e-mail]; first_name=Ann; last_name=pqr}",
"active":  true,
"marketing_optin":  null,
"enrolled_at":  "2018-05-08T18:22:15.125868Z",
"expires_at":  null,
"access_code":  null
},
{
"user":  "@{id=2222; email=[e-mail]; first_name=Benn; last_name=xyz}",
"active":  true,
"marketing_optin":  null,
"enrolled_at":  "2018-05-08T18:41:46.905016Z",
"expires_at":  null,
"access_code":  null
},
{
"user":  "@{id=3333; email=[e-mail]; first_name=Cathy; last_name=rst}",
"active":  true,
"marketing_optin":  null,
"enrolled_at":  "2018-05-08T18:41:47.015329Z",
"expires_at":  null,
"access_code":  null
}
]
}

输出文件格式应如下所示：

user_id,user_email,user_first_name,user_last_name,active,marketing_optin,enrolled_at,expires_at,access_code

到目前为止，我尝试了不同的方法，但都没有达到要求的结果。我甚至无法读取json文件。我尝试了以下代码：

with open('C:\_apps\users.json') as data_file:
data = data_file.read()
data_content = json.loads(data)

但它是否抛出了以下错误：

raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

张贴下面的代码，我目前正在尝试：

import json
from pandas.io.json import json_normalize
import argparse

def flatten_json(y):
out = {}
def flatten(x, user=''):
if type(x) is dict:
for a in x:
flatten(x[a], user + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, user + str(i) + '_')
i += 1
else:
out[user[:-1]] = x
flatten(y)
return out

if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Converting json files into csv')
parser.add_argument(
"-j", "--json", dest="users.json", help="C:_appstrainingoutput", metavar="FILE", required=True)
print(parser)
args = parser.parse_args()
print(args)
with open(args.json_file, "r") as inputFile:  # open json file
json_data = json.loads(inputFile.read())  # load json content
final_data = pd.DataFrame([flatten_json(elt) for elt in json_data['results']])
with open(args.json_file.replace(".json", ".csv"), "w") as outputFile:  # open csv file
# saving DataFrame to csv
final_data.to_csv(outputFile, encoding='utf8', index=False)

它给抛出了一个异常

args = parser.parse_args()

Answer 1

我相信您可能正在寻找一种使用panda规范json输入的方法。

pandas对这类事情有一个特定的方法，它被称为json_normalize()(更多关于pandas文档的信息(。

data = #your json here
pd.json_normalize(data)

将其加载到数据帧后，您应该能够使用pandasto_csv()方法轻松地将其写入.csv中(有关pandas文档的更多信息(。

Answer 2

由于"user"字段不是有效的python dict，我们必须手动解析它并提取所有值：

import pandas as pd
import argparse
def extract_results(jsonfile):
# Extract 'results' field
res = pd.read_json('data.json')['results'].apply(pd.Series)
# Process 'user' column
user = res['user'].str[2:-1].str.extractall('([^=]+)=([^;]+);?(?:s+|$)')
user = user.droplevel('match').pivot(columns=0, values=1).add_prefix('user_')
# Merge data
df = pd.concat([user, res.drop(columns='user')], axis='columns')
return df
if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Converting json files into csv')
parser.add_argument(
"-j", "--json", dest="jsonfile", type=argparse.FileType('r'), metavar="FILE", required=True)
args = parser.parse_args()
results = extract_results(args.jsonfile)

>>> results
user_email user_first_name user_id user_last_name  active marketing_optin                  enrolled_at expires_at access_code
0  [e-mail]             Ann    1111            pqr    True            None  2018-05-08T18:22:15.125868Z       None        None
1  [e-mail]            Benn    2222            xyz    True            None  2018-05-08T18:41:46.905016Z       None        None
2  [e-mail]           Cathy    3333            rst    True            None  2018-05-08T18:41:47.015329Z       None        None