用python将json写入excel文件时出现问题

Question

我试图通过一个API请求获取的json输出，然后将其加载到excel文件中 问题是我从api得到的响应，如果我将其转储到json.dumps（）方法，它将变得不可解析。但如果我尝试将其解析为文本，然后尝试将其格式化为json格式化程序 虽然我写了下面的代码来写入csv，但我希望它是excel文件。。 下面是我实际代码中的示例respone.text变量，如下所示：

{
    "value": [
        {
            "correlationId": "xxxxxxxxxx",
            "eventName": {
                "value": "EndRequest",
                "localizedValue": "EndRequest"
            },
            "id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
            "level": "Informational",
            "resourceGroupName": "xxxxxx",
            "resourceProviderName": {
                "value": "Microsoft.Compute",
                "localizedValue": "Microsoft.Compute"
            },
            "operationName": {
                "value": "Microsoft.Compute/virtualMachines/extensions/write",
                "localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
            },
            "status": {
                "value": "Succeeded",
                "localizedValue": "Succeeded"
            },
            "eventTimestamp": "2020-08-06T12:47:02.0657952Z",
            "submissionTimestamp": "2020-08-06T12:49:03.137537Z"
        },
        {
            "correlationId": "xxxxxxxxxx",
            "eventName": {
                "value": "EndRequest",
                "localizedValue": "EndRequest"
            },
            "id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
            "level": "Informational",
            "resourceGroupName": "xxxxxx",
            "resourceProviderName": {
                "value": "Microsoft.Compute",
                "localizedValue": "Microsoft.Compute"
            },
            "operationName": {
                "value": "Microsoft.Compute/virtualMachines/extensions/write",
                "localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
            },
            "status": {
                "value": "Succeeded",
                "localizedValue": "Succeeded"
            },
            "eventTimestamp": "2020-08-06T12:47:02.0657952Z",
            "submissionTimestamp": "2020-08-06T12:49:03.137537Z"
        },
    ]
}

下面是我正在尝试的代码：

d_date = datetime.datetime.now()
today = d_date.strftime('%Y-%m-%dT%H:%M:%S.%fZ')
print(today)

N = 10
date_N_days_ago = datetime.datetime.now() - timedelta(days=N)
start_date = date_N_days_ago.strftime('%Y-%m-%dT%H:%M:%S.%fZ')
print(start_date)

vm_list = compute_client.virtual_machines.list_all()
for vm_general in vm_list:
    general_view = vm_general.id.split("/")
    resource_group = general_view[4]
    print(resource_group)

    BASE_URL = f"https://management.azure.com/subscriptions/{subscription_id}/providers/microsoft.insights/eventtypes/management/values?api-version=2015-04-01&$filter=eventTimestamp ge {start_date} and eventTimestamp le {today} and resourceGroupName eq {resource_group}&$select=eventName,id,resourceGroupName,resourceProviderName,operationName,status,eventTimestamp,correlationId,submissionTimestamp,level"
    
    BASE_URL = BASE_URL
    headers = {
        "Authorization": 'Bearer ' + credential.token["access_token"]
    }
    response = requests.get(BASE_URL, headers=headers)
    
    # if i convert below line to df_json = response.json() it says AttributeError: 'str' object has no attribute 'json'
    df_json = response.text  # this is a string but i am able to parse it properly in json forammter
    
    print(df_json)
    
    with open('c:\csv\logs_test.csv', 'w') as f:
        for key in df_json.keys():
            f.write("%s,%s\n" % (key, df_json[key]))
    break

我得到的错误如下：

AttributeError: 'str' object has no attribute 'keys'

预期结果：

Actually I need to to write to xls (excel) format having columns as "correlationId,eventName,id,resourceGroupName,resourceProviderName,operationName,status,eventTimestamp,submissionTimestamp

Answer 1

实际上，您可以使用eval将文本转换为字典，然后使用pandas将其转换为excel文件

import pandas

response_dict = eval(response.text)
df = pd.DataFrame(response_dict['value'])
df['tag'] = "Managed by IT"

file_name = 'data.xls'
df.to_excel(file_name, index = False)

Answer 2

最简单的方法是转换为数据帧，然后转换为xls文件。 您必须安装xlwt-pip install xlwt

import pandas as pd

data = {
    "value": [
        {
            "correlationId": "xxxxxxxxxx",
            "eventName": {
                "value": "EndRequest",
                "localizedValue": "EndRequest"
            },
            "id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
            "level": "Informational",
            "resourceGroupName": "xxxxxx",
            "resourceProviderName": {
                "value": "Microsoft.Compute",
                "localizedValue": "Microsoft.Compute"
            },
            "operationName": {
                "value": "Microsoft.Compute/virtualMachines/extensions/write",
                "localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
            },
            "status": {
                "value": "Succeeded",
                "localizedValue": "Succeeded"
            },
            "eventTimestamp": "2020-08-06T12:47:02.0657952Z",
            "submissionTimestamp": "2020-08-06T12:49:03.137537Z"
        },
        {
            "correlationId": "xxxxxxxxxx",
            "eventName": {
                "value": "EndRequest",
                "localizedValue": "EndRequest"
            },
            "id": "/subscriptions/xxxxxxxxxx/resourcegroups/xxxxxxxxx/providers/Microsoft.Compute/virtualMachines/xxxxxx/extensions/enablevmaccess/events/xxxxxxxxxx/ticks/xxxxxxxx",
            "level": "Informational",
            "resourceGroupName": "xxxxxx",
            "resourceProviderName": {
                "value": "Microsoft.Compute",
                "localizedValue": "Microsoft.Compute"
            },
            "operationName": {
                "value": "Microsoft.Compute/virtualMachines/extensions/write",
                "localizedValue": "Microsoft.Compute/virtualMachines/extensions/write"
            },
            "status": {
                "value": "Succeeded",
                "localizedValue": "Succeeded"
            },
            "eventTimestamp": "2020-08-06T12:47:02.0657952Z",
            "submissionTimestamp": "2020-08-06T12:49:03.137537Z"
        }
    ]
}

df = pd.json_normalize(data['value'])
cols = ["correlationId","eventName.value","id","resourceGroupName","resourceProviderName.value","operationName.value","status.value","eventTimestamp","submissionTimestamp"]

df[cols].to_excel("data.xls", index=False)

使用demjson代替json。安装库-pip install demjson，因为json只有在它是正确的json时才能正确解析

import demjson

data = demjson.decode(response.text)

# remaining code goes on