我正在阅读下面的json文件,并尝试使用python的pandas库以.xlsx
格式存储它
JSON码:
{ "log": [
{
"code": "info",
"message": {"text": "[info] Activation of plug-in abcd rule processor (xule) successful, version Check version using Tools->Xule->Version on the GUI or --xule-version on the command line. - xule "},
"refs": [{"href": "xule"}],
"level": "info"
}
,
{
"code": "xyz.F1.all.7",
"level": "error",
"message": {
"text": "[xyz.F1.all.7] The value for ForResale with a value of 63 has a unit of utr:MWh. This concept allows units of utr:MWh.\n\nElement : xyz:ForResale\nPeriod : 2016-01-01 to 2016-12-31\nUnit : utr:MWh\n\nRule Id:xyz.F1.all.7 - TestUtilitiesInc-428-2016Q4F1.abcd 4114",
"severity": "error",
"cid": "63096080",
"filing_url": "C:\\Users\\TEST\\Desktop\\TestUtilitiesInc-428-2016Q4F1.abcd"
},
"refs": [
{
"properties": [
[
"label",
"Megawatt hours sold, Sales for Resale"
],
[
"namespace",
"http://xyzcom/form/2020-01-01/xyz"
],
[
"name",
"ForResale"
],
[
"QName",
"xyz:ForResale"
],
[
"contextRef",
"c-177",
[
[
"entity",
"C002089",
[
[
"scheme",
"http://abcd.org/entity/identification/scheme"
]
]
],
[
"startDate",
"2016-01-01"
],
[
"endDate",
"2016-12-31"
],
[
"dimensions",
"(1)",
[
[
"xyz:SalesForResaleAxis",
"<xyz:SalesForResaleDomain>0-3</xyz:SalesForResaleDomain>\n\t\t\t\t\n"
]
]
]
]
],
[
"unitRef",
"u-03 (MWh)",
[
[
"measure",
"utr:MWh"
]
]
],
[
"decimals",
"INF"
],
[
"precision",
"None"
],
[
"xsi:nil",
"false"
],
[
"value",
"63"
]
],
"href": "TestUtilitiesInc-428-2016Q4F1.abcd#f-731",
"objectId": "__91261",
"sourceLine": 4114
}
]
}]}
使用下面的python代码
import json
import pandas
from pandas.io.json import json_normalize
def flatten_json(y):
out = {}
def flatten(x, name=''):
if type(x) is dict:
for a in x:
flatten(x[a], name + a + '_')
elif type(x) is list:
i = 0
for a in x:
flatten(a, name + str(i) + '_')
i += 1
else:
out[name[:-1]] = x
flatten(y)
return out
with open('C:/Users/Desktop/SampleTestFiles/logfile_1.json', encoding="utf-8") as json_file:
data = json.load(json_file)
flat = flatten_json(data)
normalizedjson = json_normalize(data)
writer = pandas.ExcelWriter('myDataFrame.xlsx')
normalizedjson.to_excel(writer, 'DataFrame')
writer.save()
但是,我想在Excel文件中创建以下格式的输出,该文件应该有-code, message, refs and level
列。而且,json中这些键下的任何值都应该是每个日志的相应行
相关问题 更多 >
编程相关推荐