Issue in writing json to excel file in python

Question

I am trying to get json output of via one of API request , which i wanted then load that into excel file The problem is the response i get from api, if i dump it to json.dumps() method, its becoming not parsable. But if i try to parse it as text, then tried to format it json formatter its parsing Though i wrote code to write to csv below, but i wanted it to excel file.. Here is my sample respone.text variable in my actual code looks like:

{
    "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"
        },
    ]
}

Here the code I am trying:

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

I am getting error like:

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

Expected result:

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

KRISHNA MALLIK · Accepted Answer · 2020-08-06 17:19:52Z

1

You can actually use eval to convert the text to a dictionary and then use pandas to convert it to an excel file.

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)

edited Aug 6, 2020 at 17:19

answered Aug 6, 2020 at 15:37

KRISHNA MALLIK

614 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

asp Over a year ago

how to add column names here in this example pls

asp Over a year ago

getting ModuleNotFoundError: No module named 'xlwt', do we need to mention any engine ?

KRISHNA MALLIK Over a year ago

Hi @asp, Pandas can take the keys as column names by default. Unless you want to change the column names, you need not specifically do anything.

KRISHNA MALLIK Over a year ago

df.columns = [list of column names in order]

KRISHNA MALLIK Over a year ago

Yes, please add, df['tag'] = "Managed by IT", you will see the new column

|

bigbounty · Accepted Answer · 2020-08-06 17:47:14Z

1

The easiest is to convert to pandas dataframe and then to xls file. You will to have to install 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)

Instead of json, use demjson. Install the library - pip install demjson because json parses correctly only if it's a proper json.

import demjson

data = demjson.decode(response.text)

# remaining code goes on

edited Aug 6, 2020 at 17:47

answered Aug 6, 2020 at 15:29

bigbounty

17.5k7 gold badges45 silver badges76 bronze badges

12 Comments

asp Over a year ago

df = pd.json_normalize(a['value']) should be df = pd.json_normalize(data['value']) ?

bigbounty Over a year ago

Thanks @asp Corrected

asp Over a year ago

getting this error now df = pd.json_normalize(data['value']) TypeError: 'data' object is not subscriptable

bigbounty Over a year ago

data should be a json object.

bigbounty Over a year ago

Try changing the column names... get the column names from the dataframe

|

Collectives™ on Stack Overflow

Issue in writing json to excel file in python

2 Answers 2

9 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related