JSON行数据拆分多列存入pandas DataFrame报错求助

我在将JSON数据读取到pandas的过程中，多标准Value字段读取结果如下所示，需要基于底层数据创建包含合理维度的时间序列图表。
首先我使用如下代码从示例URL导入数据，示例输入数据如下：

import requests
import json
import pandas as pd
from urllib.request import urlopen
from pandas.io.json import json_normalize
response = requests.get('http://test1.com').json

输入JSON数据：

{
  "odata.metadata":"http://test1.com",
  "value":[
    {
      "Data":"28.00000000",
      "Date_Code":"20200401",
      "Date_ItemName_ENG":"1 Apr 2020",
      "Date_SortOrder":"10",
      "Date_ItemNotes_ENG":"",
      "LocalHealthBoard_Code":"7A1",
      "LocalHealthBoard_ItemName_ENG":"Betsi Cadwaladr University Local Health Board",
      "LocalHealthBoard_SortOrder":"2",
      "LocalHealthBoard_Hierarchy":"W92000004",
      "LocalHealthBoard_ItemNotes_ENG":"",
      "LocalHealthBoard_AltCode1":"W11000023",
      "Hospitaltype_Code":"NHS",
      "Hospitaltype_ItemName_ENG":"All NHS hospitals",
      "Hospitaltype_SortOrder":"1",
      "Hospitaltype_Hierarchy":"AllHosp",
      "Hospitaltype_ItemNotes_ENG":"Includes data from acute hospitals only until 19 April 2020. Field hospitals data were added from 20 April 2020, community hospitals data were added from 23 April 2020, and mental health hospitals data were added from 10 July 2020.",
      "Indicator_Code":"CO_Admissions_C19",
      "Indicator_ItemName_ENG":"COVID-19 admissions (suspected and confirmed)",
      "Indicator_SortOrder":"102",
      "Indicator_Hierarchy":"Misc_Admissions_All",
      "Indicator_ItemNotes_ENG":"Patients admitted as suspected or confirmed with COVID-19",
      "RowKey":"0000000000000000",
      "PartitionKey":""
    },
    {
      "Data":"28.00000000",
      "Date_Code":"20200401",
      "Date_ItemName_ENG":"1 Apr 2020",
      "Date_SortOrder":"10",
      "Date_ItemNotes_ENG":"",
      "LocalHealthBoard_Code":"7A1",
      "LocalHealthBoard_ItemName_ENG":"Betsi Cadwaladr University Local Health Board",
      "LocalHealthBoard_SortOrder":"2",
      "LocalHealthBoard_Hierarchy":"W92000004",
      "LocalHealthBoard_ItemNotes_ENG":"",
      "LocalHealthBoard_AltCode1":"W11000023",
      "Hospitaltype_Code":"TotAcute",
      "Hospitaltype_ItemName_ENG":"All Acute hospitals",
      "Hospitaltype_SortOrder":"2",
      "Hospitaltype_Hierarchy":"NHS",
      "Hospitaltype_ItemNotes_ENG":"Prior to 10 July 2020, data may include small numbers of mental health unit beds.",
      "Indicator_Code":"CO_Admissions_C19",
      "Indicator_ItemName_ENG":"COVID-19 admissions (suspected and confirmed)",
      "Indicator_SortOrder":"102",
      "Indicator_Hierarchy":"Misc_Admissions_All",
      "Indicator_ItemNotes_ENG":"Patients admitted as suspected or confirmed with COVID-19",
      "RowKey":"0000000000000001",
      "PartitionKey":""
    }
  ]
}

预期输出：结构化pandas表，包含Data、Date_Code、Date_ItemName_ENG、Date_SortOrder等全量字段。 #

已尝试的解决方案 #

尝试方法1 #

split = response['value'].values.tolist()
rate = pd.DataFrame(split,columns =['Data', 'Date_Code','Date_ItemName_ENG','Date_SortOrder'])

报错：TypeError: 'method' object is not subscriptable

尝试方法2 #

data = json.loads(response)
final_data = []
for item in data['value']:
    my_dict = {}
    my_dict['Data'] = item['Data']
    my_dict['Date_Code'] = item['Date_Code']
    my_dict['Date_ItemName_ENG'] = item['Date_ItemName_ENG']
    my_dict['Date_SortOrder'] = item['Date_SortOrder']   
    print(my_dict)
    final_data.append(my_dict)
back_json=json.dumps(final_data)

结果：仅得到dict格式输出，无法生成多列结构。

尝试方法3 #

back_json=json.dumps(final_data)
parsed_days = json_normalize(back_json)
json_struct = json.loads(response.to_json(orient="records"))    
df_flat = pd.io.json.json_normalize(json_struct)

报错：AttributeError: 'str' object has no attribute 'values' #

核心错误是调用requests.get().json时漏了括号，json是方法不是属性，加()执行后才能拿到解析后的JSON字典。不需要手动循环拼字段，pandas可直接将value数组转为结构化表：

import requests
import pandas as pd

# json后加括号执行方法，得到解析后的字典对象
response_data = requests.get('http://test1.com').json()
# 直接取value数组转DataFrame
df = pd.DataFrame(response_data['value'])
# 可选：做数据类型转换方便后续做时间序列分析
df['Data'] = pd.to_numeric(df['Data'])
df['Date_Code'] = pd.to_datetime(df['Date_Code'], format='%Y%m%d')

错误原因说明 #

• 方法1报错是因为未执行json()方法，此时的response是方法对象不是字典，无法用下标取值
• 方法2已经得到了字典列表final_data，直接用pd.DataFrame(final_data)即可生成表，不需要多余的序列化转JSON字符串操作
• 方法3报错是因为json_normalize只能处理字典/列表结构的Python对象，不能处理序列化后的字符串

内容的提问来源于stack exchange，提问作者Kuks