我正在阅读一个JSON文件,试图从每个log
中提取以下信息并将其存储在Excel工作表中
["name","TOM DOE"]
["value","132"]
我的python代码能够从JSON中提取一些其他必需的信息。但是,由于JSON包含多个嵌套列表和字典,我无法提取并存储上面的两个信息。请任何人都能帮我用python代码
这是我的python代码:
import json
import pandas
class ConvertToExcel:
def Process(self):
with open('C:/Users/Desktop/SampleTestFiles/new17.json') as json_file:
dataarray = json.load(json_file)
data1 = []
logcount = 0
for data in dataarray:
logcount = logcount + 1
for i in range(len(data['log'])):
code = data['log'][i]['code']
message = data['log'][i]['message']
try:
cid = data['log'][i]['message']['cid']
except:
cid = 0
text = data['log'][i]['message']['text']
refs = data['log'][i]['refs']
for k in range(len(data['log'][i]['refs'])):
try:
hrefs = data['log'][i]['refs'][k]['href']
except:
hrefs =''
try:
hrefsFacts = data['log'][i]['refs'][k]['href']
except:
hrefsFacts =''
# print(type(data['log'][i]['refs'][k]['href']['properties']))
# res1 = 'name' in chain(*data['log'][i]['refs'][k]['href']['properties'])
# for elem in len(data['log'][i]['refs'][k]['href']['properties']):
# for item in elem:
# if(item == 'val'):
# hrefspropertiesvalue = item
# else:
# pass
hrefspropertiesvalue = 'a'
level = data['log'][i]['level']
data1.append((logcount,i, code, message,cid, text, refs,hrefs,hrefsFacts, hrefspropertiesvalue, level))
pandas.DataFrame(data1, columns=['Log', 'Innerlog', 'code', 'message','cid','text','refs','hrefs','hrefsFacts','hrefspropertiesvalue', 'level']).to_excel("output.xlsx")
A = ConvertToExcel()
A.Process()
还有,JSON:
[{ "log": [
{
"code": "nikv.F1.all.1",
"message": {
"cid": "61785360",
"filing_url": "C:\\Users\\farizaleta\\Desktop\\test-428-2016Q4F1.abcd",
"severity": "error",
"text": "[nikv.F1.all.1] The values of 6,075,786 for the elements nikv:OtherChargestested is duplicated in the filing 2 times.\n\nElement : nikv:OtherChargestested\nPeriod : 2016-01-01 to 2016-12-31\n\n\nRule Id:nikv.F1.all.1 - test-428-2016Q4F1.abcd 4122"
},
"refs": [
{
"href": "test-428-2016Q4F1.abcd#f-743",
"sourceLine": 4122,
"properties": [
[
"label",
"Other charges, tested"
],
[
"namespace",
"http://nikv.com/form/2002-01-01/nikv"
],
[
"name",
"TOM DOE"
],
[
"QName",
"nikv:OtherChargestested"
],
[
"contextRef",
"c-01",
[
[
"entity",
"C002089",
[
[
"scheme",
"http://tested.com/entity/identification/scheme"
]
]
],
[
"startDate",
"2016-01-01"
],
[
"endDate",
"2016-12-31"
],
[
"dimensions",
"(1)",
[
[
"nikv:OfficerAxis",
"<nikv:OfficerDomain>0-1</nikv:OfficerDomain>\n\t\t\t\t\n"
]
]
]
]
],
[
"unitRef",
"u-02",
[
[
"measure",
"iso4217"
]
]
],
[
"decimals",
"INF"
],
[
"precision",
"None"
],
[
"xsi:nil",
"false"
],
[
"value",
"132"
]
],
"objectId": "91269"
}
],
"level": "error"
}]
}]
您可以像这样对属性进行循环:
*_
忽略properties
子列表中的任何额外元素然后可以测试
type
是name
还是value
,并设置适当的变量我还简化了所有循环,使用
for <variable> in <list>
而不是for <indexvariable> in range(len(<list>))
相关问题 更多 >
编程相关推荐