JSON input to multiple excel file outputs

Question

I have a JSON file that looks like this:

{
  "Person A": {
    "Company A": {
      "Doctor": {
        "Morning": "2000",
        "Afternoon": "1200"
      },
      "Nurse": {}
    }
  },
  "Person B": {
    "Education": {
      "main": {
        "Primary school": {
          "2012": "2A",
          "2013": "3A"
        },
        "Secondary school": {
          "2016": "1K",
          "2017": "2K"
        }
      }
    }
  }
}

How do I extract the table for Education (without the main) with

primary_school.xlsx as an excel file:

year, class

secondary_school.xlsx as an excel file:

year, class

PersonA_CompanyA_Doctor.xlsx

Time, salary

PersonA_CompanyA_Nurse.xlsx:

Time, salary

I have tried json_normalize but still cannot get the result that I want.

pd.json_normalize(file, max_level=1)

Is there a simple way of doing it using dataframe?

Answer 1

The JSON data you presented as an example is in the form of a graph with a lot of connections, firstly, after connecting your ports on this data structure, regardless of the format of the data, -cut the green wire :)-

After this process, you should have a one-dimensional array, iterable, in which you will access the names of the xlsx files you specify. If you are specifically asking about the connection part, it is possible for us to find a general solution by simplifying the example.

But if you want to continue,

Examining the detailed example below and installing the relevant package with the pip install xlsxwriter cli command if necessary. With the list in your hand, you can create the xlsx files you want, in order. `

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

`