Question 110712 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Converting a JSON into pnadas dataframe and a variable using function

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.

startUTC endUTC Timestamp V1
1671490577 1671516101 1671490584 21.1
1671490577 1671516101 1671490666 21

How can I do this in python?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    `
    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search