python json处理

#@auth by ruiy
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#pip install pandas
#pip install openpyxl
#


import json
import time
import pandas
import datetime




with open('cv.txt', 'rb') as f:
#with open('xcv.txt', 'rb') as f:
    data = json.load(f)
    #print(data)
    #print(type(data))
    #print(len(data))
    #for i in int(range(data)):
    #    print(data[i])
    '''
    for i in data:
        #print(i)
        print(i['auditTime'])
        time.sleep(6)
        '''
#print(data)

dataOutput = {
"auditTime":[],
"batchNo":[],
"billType":[],
"corpName":[],
"headId":[],
"headNo":[],

"inAgName":[],

"msgReason":[],

"outAgName":[],
"resultStaus":[],
"uploadStaus":[],
"vaccName":[],
"vaccNum":[]
}

indexNum = 1




for i in data:
    #print(i['auditTime'])
    if "auditTime" in i:
        dataOutput["auditTime"].append(i["auditTime"])
    else:
        dataOutput["auditTime"].append("null")
    

    if "batchNo" in i:
        dataOutput["batchNo"].append(i["batchNo"])
    else:
        dataOutput["batchNo"].append("null")

    if "billType" in i:
        dataOutput["billType"].append(i["billType"])
    else:
        dataOutput["billType"].append("null")

    if "corpName" in i:
        dataOutput["corpName"].append(i["corpName"])
    else:
        dataOutput["corpName"].append("null")

    if "headId" in i:
        dataOutput["headId"].append(i["headId"])
    else:
        dataOutput["headId"].append(i["headId"])

    if "headNo" in i:
        dataOutput["headNo"].append(i["headNo"])
    else:
        dataOutput["headNo"].append("null")

    if "inAgName" in i:
        dataOutput["inAgName"].append(i["inAgName"])
    else:
        dataOutput["inAgName"].append("null")
        
    if "msgReason" in i:
        dataOutput["msgReason"].append(i["msgReason"])
        #print("key ok")
        #print(i.keys())
    else:
        #print("no msgReason key")
        dataOutput["msgReason"].append("null")
        #print(i.keys())
        #dataOutput["msgReason"].append(i["null"])
        #dataOutput.add("msgReason")
        #dataOutput["msgReason"] = "null"
        #dataOutput.update(msgReason = "null")
        #i.update(msgReason= "null")
        #print(i.keys())
        #time.sleep(6)
    if "outAgName" in i:
        dataOutput["outAgName"].append(i["outAgName"])
    else:
        dataOutput["outAgName"].append("null")

    if "resultStaus" in i:
        dataOutput["resultStaus"].append(i["resultStaus"])
    else:
        dataOutput["resultStaus"].append("null")

    if "uploadStaus" in i:
        dataOutput["uploadStaus"].append(i["uploadStaus"])
    else:
        dataOutput["uploadStaus"].append("null")

    if "vaccName" in i:
        dataOutput["vaccName"].append(i["vaccName"])
    else:
        dataOutput["vaccName"].append("null")

    if "vaccNum" in i:
        dataOutput["vaccNum"].append(i["vaccNum"])
    else:
        dataOutput["vaccNum"].append("null")
    
    print("正在处理" + str(indexNum) + "行")
    indexNum += 1

print(dataOutput.keys())
#print(len(dataOutput["vaccNum"]))
for i in dataOutput.keys():
    #print(i)
    #print(len(dataOutput(str(i))))

    print(len(dataOutput[i]))

#print(dataOutput)
print("正在写入Excel")


currentTime = datetime.datetime.now()
dataForm = pandas.DataFrame(dataOutput)
dataForm.to_excel("结果芜湖2021_2022_10_27" + currentTime.strftime("%Y%m%d") + '.xlsx')
print("写入完成")