how to convert json response to excel using python

Question

this reponse I am getting:

    {
      "value": [
        {
          "id": "/providers/Microsoft.Billing/Departments/1234/providers/Microsoft.Billing/billingPeriods/201903/providers/Microsoft.Consumption/usageDetails/usageDetails_Id1",
          "name": "usageDetails_Id1",
          "type": "Microsoft.Consumption/usageDetails",
          "kind": "legacy",
          "tags": {
            "env": "newcrp",
            "dev": "tools"
          },
          "properties": {
            "billingAccountId": "xxxxxxxx",
            "billingAccountName": "Account Name 1",
            "billingPeriodStartDate": "2019-03-01T00:00:00.0000000Z",
            "billingPeriodEndDate": "2019-03-31T00:00:00.0000000Z",
            "billingProfileId": "xxxxxxxx",
            "billingProfileName": "Account Name 1",
            "accountName": "Account Name 1",
            "subscriptionId": "00000000-0000-0000-0000-000000000000",
            "subscriptionName": "Subscription Name 1",
            "date": "2019-03-30T00:00:00.0000000Z",
            "product": "Product Name 1",
            "partNumber": "Part Number 1",
            "meterId": "00000000-0000-0000-0000-000000000000",
            "meterDetails": null,
            "quantity": 0.7329,
            "effectivePrice": 0.000402776395232,
            "cost": 0.000295194820065,
            "unitPrice": 4.38,
            "billingCurrency": "CAD",
            "resourceLocation": "USEast",
            "consumedService": "Microsoft.Storage",
            "resourceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/Resource Group 1/providers/Microsoft.Storage/storageAccounts/Resource Name 1",
            "resourceName": "Resource Name 1",
            "invoiceSection": "Invoice Section 1",
            "costCenter": "DEV",
            "resourceGroup": "Resource Group 1",
            "offerId": "Offer Id 1",
            "isAzureCreditEligible": false,
            "chargeType": "Usage",
            "benefitId": "00000000-0000-0000-0000-000000000000",
            "benefitName": "Reservation_purchase_03-09-2018_10-59"
          }
        },
{
          "id": "/providers/Microsoft.Billing/Departments/1234/providers/Microsoft.Billing/billingPeriods/201903/providers/Microsoft.Consumption/usageDetails/usageDetails_Id1",
          "name": "usageDetails_Id1",
          "type": "Microsoft.Consumption/usageDetails",
          "kind": "legacy",
          "tags": {
            "env": "newcrp",
            "dev": "tools"
          },
          "properties": {
            "billingAccountId": "xxxxxxxx",
            "billingAccountName": "Account Name 1",
            "billingPeriodStartDate": "2019-03-01T00:00:00.0000000Z",
            "billingPeriodEndDate": "2019-03-31T00:00:00.0000000Z",
            "billingProfileId": "xxxxxxxx",
            "billingProfileName": "Account Name 1",
            "accountName": "Account Name 1",
            "subscriptionId": "00000000-0000-0000-0000-000000000000",
            "subscriptionName": "Subscription Name 1",
            "date": "2019-03-30T00:00:00.0000000Z",
            "product": "Product Name 1",
            "partNumber": "Part Number 1",
            "meterId": "00000000-0000-0000-0000-000000000000",
            "meterDetails": null,
            "quantity": 0.7329,
            "effectivePrice": 0.000402776395232,
            "cost": 0.000295194820065,
            "unitPrice": 4.38,
            "billingCurrency": "CAD",
            "resourceLocation": "USEast",
            "consumedService": "Microsoft.Storage",
            "resourceId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/Resource Group 1/providers/Microsoft.Storage/storageAccounts/Resource Name 1",
            "resourceName": "Resource Name 1",
            "invoiceSection": "Invoice Section 1",
            "costCenter": "DEV",
            "resourceGroup": "Resource Group 1",
            "offerId": "Offer Id 1",
            "isAzureCreditEligible": false,
            "chargeType": "Usage",
            "benefitId": "00000000-0000-0000-0000-000000000000",
            "benefitName": "Reservation_purchase_03-09-2018_10-59"
          }
        }
      ]
    }

code:

import pandas as pd
frame=pd.DataFrame()
for i in range (len(json_output['value'])):
    
    df1= pd.DataFrame(data={'kind':json_output['value'][i]['kind'],
    'id': json_output['value'][i]['id'],
    'tags': json_output['value'][i]['tags'],
    'name':json_output['value'][i]['name'],
    'type':json_output['value'][i]['type'],
    'billingAccountid':json_output['value'][i]['properties']['billingAccountId']},index=[i])
    print(df1)                
    frame=frame.append(df1)
    
frame.to_csv('datt.csv')

Can you please help me to convert this data in to csv. I am looking for id,name,type,kind,tags,billingAccountId,resourceName etc into all column

I tried to convert into DataFrame it didn't work. At last I am trying above python but its giving tags into null. Note : I want to keep tags in dict format (for now)

By "keep tags in dict format" do you mean write them as "{'env': 'newcrp', 'dev': 'tools'}"? If yes, just try to pack them into STR() like: 'tags': str(json_output['value'][i]['tags']), — Salamander Krajza
– Salamander Krajza, Commented May 19, 2022 at 12:53
Can you please help me with one more thing suppose "billingAccountId" this does not exist in some of the loop then how can i put loop condition. because if data points doesn't exist it will give error @SalamanderKrajza — kakaji
– kakaji, Commented May 19, 2022 at 17:12

DontDownvote · Accepted Answer · 2022-05-19 17:20:44Z

1

I tried your code and stored json file into an output first:

-TAGS is a dictionary you access it without any keys so it will be NONE

If not comfortable by splitting TAGS use:

'tags':json_output['value'][i]['tags']['env']+json_output['value'][i]['tags']['dev']

edited May 19, 2022 at 17:20

answered May 19, 2022 at 14:12

DontDownvote

1927 bronze badges

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

4 Comments

DontDownvote Over a year ago

@kakaji tell if this solves your problem

kakaji Over a year ago

yes it does solve thank you so much...

kakaji Over a year ago

Can you please help me with one more thing suppose "billingAccountId" this does not exist in some of the loop then how can i put loop condition. because if data points doesn't exist it will give error

DontDownvote Over a year ago

then you should accept my answer first and for your question here, give me a proper example, will try to figure out.

Collectives™ on Stack Overflow

how to convert json response to excel using python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related