Converting Json To Pandas Dataframe- Python

I have read data from particular API using the following Python lines import requests import json # read all Measurement from one sensor for several days. r = requests.get('https

Solution 1:

You can try this, it works well

// importing required librariesimport 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

You will get the desired result which you want rest some column values you can modify further for getting the column values.

Solution 2:

This should work. Btw, I'm not sure which time specifically you need in the dataframe. Therefore, I included all of them in the solution (if you're not certain of the order in which the measurements come)

import pandas as pd
import numpy as np
import json
from collections import OrderedDict
json_str = {
"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_str2 = json.dumps(dct)
df = pd.io.json.json_normalize(json_str)
df2 = pd.io.json.json_normalize(
    OrderedDict([(str(i), v) for i, v inenumerate(df["measurements"].tolist()[0])]))

# If you are certain that the list always comes in that order
df = pd.concat([df, df2], axis=1)
df[["0.source.id", "2.time","0.battery.percent.value", "1.c8y_TemperatureMeasurement.T.value", "2.c8y_DistanceMeasurement.distance.value"]]

# If you are uncertain of the order
cols = ['0.source.id'] + \
[c for c in df.columns if ('time'in c or'emperatureMeasurement.T.value'in c or'DistanceMeasurement.distance.unit'in c or'battery.percent.value'in c)]
df[cols].head()