Pull data from a json and create an excel file with the data

Question

I'm trying to write a python script to:

1. Grab a JSON file.
2. Pull specific header from the json file. (couldn't figure this out)
3. Create the headers in the excel file as columns.
4. Parse through the json and put the data in the appropriate column.

I'm fairly new and struggling to get it to work.

import json
import xlwt
# Workbook is created
wb = xlwt.Workbook()
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1')
# open json file and store it in data variable
with open('input.json') as f:
    data = json.load(f)
# write the column names in the excel sheet
sheet1.write(0, 0, 'Name')
sheet1.write(0, 1, 'Schedule')
sheet1.write(0, 2, 'Time Window')
sheet1.write(0, 3, 'Type')
sheet1.write(0, 4, 'Tiers')
# iterate over the data and write it to the excel file
row = 1;
for item in data:
#write each value to the corresponding column
    sheet1.write(row, 0, item['Name'])
    sheet1.write(row, 1, item['Schedule'])
    sheet1.write(row, 2, item['Time Window'])
    sheet1.write(row, 3, item['Type'])
    sheet1.write(row, 4, item['Tiers'])
    row += 1;
#save the file
wb.save('data_converted_to_excel.xls')

JSON Example

{
  "name": "PLACEHOLDER",
  "description": "Health Rule Export",
  "version": 1,
  "healthRuleMembers": [
    {
      "model": {
        "id": 123,
        "name": "PLACEHOLDER",
        "enabled": true,
        "useDataFromLastNMinutes": 30,
        "waitTimeAfterViolation": 30,
        "scheduleName": "Always",
        "affects": {
          "affectedEntityType": "TIER_NODE_HARDWARE",
          "affectedEntities": {
            "tierOrNode": "NODE_AFFECTED_ENTITIES",
            "typeofNode": "DOT_NET_NODES",
            "affectedNodes": {
              "affectedNodeScope": "ALL_NODES"
            }
          }
        },
        "evalCriterias": {
          "criticalCriteria": {
            "conditionAggregationType": "ALL",
            "conditionExpression": null,
            "conditions": [
              {
                "name": "PLACEHOLDER",
                "shortName": null,
                "evaluateToTrueOnNoData": false,
                "evalDetail": {
                  "evalDetailType": "SINGLE_METRIC",
                  "metricAggregateFunction": "VALUE",
                  "metricPath": "PLACEHOLDER",
                  "metricEvalDetail": {
                    "metricEvalDetailType": "SPECIFIC_TYPE",
                    "compareCondition": "GREATER_THAN_SPECIFIC_VALUE",
                    "compareValue": 75
                  }
                },
                "triggerEnabled": false,
                "minimumTriggers": 0
              }
            ],
            "evalMatchingCriteria": null
          },
          "warningCriteria": {
            "conditionAggregationType": "ALL",
            "conditionExpression": null,
            "conditions": [
              {
                "name": "PLACEHOLDER",
                "shortName": null,
                "evaluateToTrueOnNoData": false,
                "evalDetail": {
                  "evalDetailType": "SINGLE_METRIC",
                  "metricAggregateFunction": "VALUE",
                  "metricPath": "PLACEHOLDER",
                  "metricEvalDetail": {
                    "metricEvalDetailType": "SPECIFIC_TYPE",
                    "compareCondition": "GREATER_THAN_SPECIFIC_VALUE",
                    "compareValue": 50
                  }
                },
                "triggerEnabled": false,
                "minimumTriggers": 0
              }
            ],
            "evalMatchingCriteria": null
          }
        }
      },
      "memberType": "HEALTH_RULE"
    },

I tried use what my friend said to use item = json.loads(item) but haven't been able to get it to work.

I'm a beginner. Sorry!

Answer 1

From comment by It_is_Chris

Based on the sample data, it is probably easiest to pull everything:

df = pd.json_normalize(data, 'healthRuleMembers') 

and then just keep the columns you want --

df[['model.id', 'model.name', 'model.scheduleName', ... ]].to_excel('test_file.xlsx', index=False)