I have a JSON file in the below format and I want to convert it into a pandas dataframe using a function. The fuction should return the dataframe and the sensorID.

sensor_data_df, sensorID = ImporterFunction( json_str )

• Input 1: json_str

o Type: String

• Output 1: sensor_data_df

o Type: Pandas dataframe

• Output 2: SensorID

o Type: stirng

{
"SensorId": "B22EA071",
"startUTC": 1671490577,
"endUTC": 1671516101,
"Data": [
    {
        "TimeStamp": 1671490584,
        "V1": 21.1,
    },
    {
        "TimeStamp": 1671490666,
        "V1": 21,
    }]

The dataframe should be like this.

How can I do this in python?

CodePudding user response：

If I am not misunderstood your requirements then this is what you need, use json_normalize to make dict to df

import json
import pandas as pd
from pandas.io.json import json_normalize

def ImporterFunction(json_str):
    data = json.loads(json_str)
    sensorID = data['SensorId']
    df = json_normalize(data, 'Data', ['startUTC', 'endUTC'])
    return df, sensorID

json_str = '{"SensorId": "B22EA071","startUTC": 1671490577,"endUTC": 1671516101,"Data": [{"TimeStamp": 1671490584,"V1": 21.1},{"TimeStamp": 1671490666,"V1": 21}]}'
sensor_data_df, sensorID = ImporterFunction(json_str)
print(sensor_data_df)
print(sensorID)

Output:

    TimeStamp    V1    startUTC      endUTC
0  1671490584  21.1  1671490577  1671516101
1  1671490666  21.0  1671490577  1671516101

B22EA071

CodePudding user response：

This Should work. Also notice that your json dump has missing ending '}'. I see someone also answered which I think is more elegant (use of json_normalize).

`

import pandas as pd
cols = ['startUTC','endUTC','Timestamp','V1']

def ImporterFunction(json_str):
    data=[]
    for item in json_str['Data']:
        row_data= [json_str['startUTC'],json_str['endUTC']]
        row_data.extend([item['TimeStamp'],item['V1']])
        data.append(row_data)
        sensor_data_df = pd.DataFrame(data=data,columns=cols)
        SensorID = json_str['SensorId']
        return sensor_data_df,SensorID

df,id = ImporterFunction(json_str)
print(id)
df

`