将JSON对象导出到excel

Question

我有一个非常混乱的JSON对象，就像这样-

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

我想要这样的excel表格-

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

我试图在json_normalize的帮助下将其转换为pandas dataframe，但它只在一行中显示所有数据。 这是使用的代码

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

我尝试使用旋转木马，重新排序等，但没有成功。 我是Python的初学者。请帮忙

Answer 1

下面是加载到pandas的语法

data = json.loads(your_input)
df = pd.json_normalize(data['results'])

Answer 2

为了让这个工作，我必须把你上面的例子包装在一个列表中，并把它拼凑起来

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']

输出

  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

如果需要放入循环，只需重新编写代码，以便在执行时附加行，而不是像我所做的那样连接行