python - 如何将 JSON 转换为数据框？

Question

我已经使用以下 Python 行从特定的 API 读取了数据

import requests 
import json
# read all Measurement from one sensor for several days.
r = requests.get('https://wastemanagement.post-iot.lu/measurement/measurements?source=83512 pageSize=1000000000&dateFrom=2019-10-26&dateTo=2019-10-28', auth=('xxxxx', 'xxxxx'))
text=r.text  # data is stored in json format  

这是一个输出示例，但实际输出包含几行:实际输出可以从这里下载actual output

enter code here
Valid JSON (RFC 8259)
Formatted JSON Data
{
"next":"https://wastemanagement.post-iot.lu/measurement/measurements?dateTo=2019-10-28&   
 pageSize=2000&source=83512&dateFrom=2019-10-26&currentPage=2",
"self":"https://wastemanagement.post-iot.lu/measurement/measurements?dateTo=2019-10-28& 
 pageSize=2000&source=83512&dateFrom=2019-10-26&currentPage=1",
"statistics":{
"totalPages":null,
"currentPage":1,
"pageSize":2000
},
"measurements":[
{
     "self":"https://wastemanagement.post-iot.lu/measurement/measurements/108451",
     "time":"2019-10-26T00:00:06.494Z",
     "id":"108451",
     "source":{
        "self":"https://wastemanagement.post-iot.lu/inventory/managedObjects/83512",
        "id":"83512"
     },
     "type":"c8y_Measurement",
     "battery":{
        "percent":{
           "unit":"%",
           "value":98
        }
     }
  },
  {
     "self":"https://wastemanagement.post-iot.lu/measurement/measurements/108452",
     "time":"2019-10-26T00:00:06.538Z",
     "id":"108452",
     "source":{
        "self":"https://wastemanagement.post-iot.lu/inventory/managedObjects/83512",
        "id":"83512"
     },
     "type":"TemperatureMeasurement",
     "c8y_TemperatureMeasurement":{
        "T":{
           "unit":"C",
           "value":23
        }
     }
  },
  {
     "self":"https://wastemanagement.post-iot.lu/measurement/measurements/108537",
     "time":"2019-10-26T00:00:06.577Z",
     "id":"108537",
     "source":{
        "self":"https://wastemanagement.post-iot.lu/inventory/managedObjects/83512",
        "id":"83512"
     },
     "type":"c8y_Measurement",
     "c8y_DistanceMeasurement":{
        "distance":{
           "unit":"cm",
           "value":21
        }
     }
  },

所附图片显示了(文本)中的输出

我尝试使用以下代码将 JSON 格式(存储在文本中)转换为 pandas DataFrame 但不起作用

data = json.loads(text) 

我想将 JSON 格式转换为 pandas DataFrame，此 Dataframe 应包含五列(id、电池、T 或温度、时间和距离)和几行

Answer 1

你可以试试这个，效果不错

// importing required libraries
import pandas as pd
import json
import requests

// hosted your json response as a url response 
URL = 'https://my-json-server.typicode.com/abhikumar22/JsonServer/data'

// getting requests from the server
req = requests.get(URL )
text_data= req.text
json_dict= json.loads(text_data)

// converting the json dictionary to a dataframe
df = pd.DataFrame.from_dict(json_dict["measurements"])
cols_to_keep = ['id','battery','c8y_TemperatureMeasurement','time','c8y_DistanceMeasurement']
df_final = df[cols_to_keep]
df_final = df_final.rename(columns={'c8y_TemperatureMeasurement': 'Temperature Or T','c8y_DistanceMeasurement':'Distance'})
print(df_final)

result of the code you can see find here

您将获得所需的结果，您可以保留一些列值，您可以进一步修改这些列值以获取列值。