python - Export JSON object to excel

Question

I have a pretty messed up JSON object like this -

{
"LDL Cholesterol": {
  "displayName": {
    "en": "LDL Cholesterol",
    "hi": "kls"
  },
  "sliderType": "NHHHH",
  "high": 160,
  "text": {
    "en": "LDL",
    "hi": "ldd"
  }
},

"HDL/LDL Cholesterol Ratio": {
  "displayName": {
    "en": "HDL : LDL ratio",
    "hi": "klas"
  },
  "sliderType": "LN",
  "lowThresh": 0.33,
  "text": {
    "en": "",
    "hi": "jla"
  }
}
}

I want to have excel sheet like this-

Test Name                |sliderType|high|lowThresh|en.displayName |en.text|hi.displayName|hi.text
LDL Cholesterol          | NHHHH    |160 |         |LDL Cholesterol|  LDL  |   kls        |ldd
HDL/LDL Cholesterol Ratio| LN       |    |  0.33   |HDL : LDL ratio|       |   klas       |jla

I tried to convert it into pandas dataframe with the help of json_normalize but it's showing all the data in 1 row only. This is the code used

f=open('path_to_file','rb')
data = json.load(f)
df = pandas.json_normalize(data)

I tried using swapLevel, reordering etc. but didn't work out. I am a beginner in Python. Please help!

question from:https://stackoverflow.com/questions/65599423/export-json-object-to-excel

Answer 1

In order to get this to work, I had to wrap your example above in a list and piece it together.

d={
"LDL Cholesterol": {
  "displayName": {
    "en": "LDL Cholesterol",
    "hi": "kls"
  },
  "sliderType": "NHHHH",
  "high": 160,
  "text": {
    "en": "LDL",
    "hi": "ldd"
  }
},

"HDL/LDL Cholesterol Ratio": {
  "displayName": {
    "en": "HDL : LDL ratio",
    "hi": "klas"
  },
  "sliderType": "LN",
  "lowThresh": 0.33,
  "text": {
    "en": "",
    "hi": "jla"
  }
}
}
dl =[d]
df1 = pd.json_normalize(dl[0]['LDL Cholesterol'])
df2 = pd.json_normalize(dl[0]['HDL/LDL Cholesterol Ratio'])
df = pd.concat([df1, df2], axis=0)
df['Test Name'] = ['LDL Cholesterol', 'HDL/LDL Cholesterol Ratio']

Output

  sliderType   high   displayName.en displayName.hi text.en text.hi  lowThresh                  Test Name
0      NHHHH  160.0  LDL Cholesterol            kls     LDL     ldd        NaN            LDL Cholesterol
0         LN    NaN  HDL : LDL ratio           klas             jla       0.33  HDL/LDL Cholesterol Ratio

If you need to put in a loop, you'll just have to rework the code to perhaps append rows as you go rather than concatenating like I've done.