How to create a Pandas DataFrame from another DataFrame's column, which is a list

Hi, I have a python script that is creating a DataFrame from some json data.
I can create a DataFrame (df) from the data, but I need to create a DataFrame from the 'readings' column within the df DataFrame. My code is failing because the 'readings' column is a list.
Ultimately I need to create a DataFrame with the two DataFrames combined:

This is the python code I'm working with:

import pandas as pd
import json

data = {  
   'locationId':123546987,
   'parameters':[  
      {  
         'parameterId':'11',
         'unitId':'81',
         'customParameter':False,
         'readings':[  
            {  
               'timestamp':1538957700,
               'value':2306.078
            },
            {  
               'timestamp':1538959500,
               'value':2305.892
            },
            {  
               'timestamp':1538961300,
               'value':2305.981
            }
         ]
      },
      {  
         'parameterId':'1',
         'unitId':'1',
         'customParameter':False,
         'readings':[  
            {  
               'timestamp':1538957700,
               'value':25.575
            },
            {  
               'timestamp':1538959500,
               'value':25.572
            },
            {  
               'timestamp':1538961300,
               'value':25.575
            }
         ]
      }
   ]
}
         
val = json.loads(json.dumps(data))

val1 = val['parameters']

#val2 = [{'timestamp': 1538957700, 'value': 2306.078}, {'timestamp': 1538959500, 'value': 2305.892}, {'timestamp': 1538961300, 'value': 2305.981}]
val2 = val1['readings']

df = pd.DataFrame(val1).set_index('parameterId')
print(df)

df2 = pd.DataFrame(val2)
print(df2)

Open in new window

Thank you

* python3* pandasPythonJSON

Last Comment

Subodh Tiwari (Neeraj)

8/22/2022 - Mon

Subodh Tiwari (Neeraj)

10/17/2018

I am not sure if this is the right way of doing this but you may try something like this...

import pandas as pd
import json

data = {
    'locationId':123546987,
    'parameters':[
        {
            'parameterId':'11',
            'unitId':'81',
            'customParameter':False,
            'readings':[
                {
                    'timestamp':1538957700,
                    'value':2306.078
                },
                {
                    'timestamp':1538959500,
                    'value':2305.892
                },
                {
                    'timestamp':1538961300,
                    'value':2305.981
                }
            ]
        },
        {
            'parameterId':'1',
            'unitId':'1',
            'customParameter':False,
            'readings':[
                {
                    'timestamp':1538957700,
                    'value':25.575
                },
                {
                    'timestamp':1538959500,
                    'value':25.572
                },
                {
                    'timestamp':1538961300,
                    'value':25.575
                }
            ]
        }
    ]
}

print(data)

val = json.loads(json.dumps(data))
val = pd.DataFrame(val)
print(val)

def getReadings(dict):
    d = pd.DataFrame(dict['readings'])
    d['unitId'] = dict['unitId']
    d['parameterId'] = dict['parameterId']
    return d

df = pd.DataFrame()
for i in range(len(val)):
    df1 = getReadings(val.iloc[i,1])
    df1['locationId'] = val.iloc[i,0]
    df1['customParameter'] = 'False'
    df = df.append(df1)

print(df)

Open in new window

Norie

10/18/2018

Building on what Subodh posted, try this.

val = json.loads(json.dumps(data))
val = pd.DataFrame(val)

def getReadings(dict):
    d = pd.DataFrame(dict['readings'], columns=['parameterId','customParameter','timestamp','value','unitId'])
    d['parameterId'] = dict['parameterId']
    d['customParameter'] = dict['customParameter']    
    d['unitId'] = dict['unitId']
    return d

df = pd.DataFrame()

for i in range(len(val)):
    df1 = getReadings(val.iloc[i,1])
    df = df.append(df1)

print(df)

Open in new window

Subodh Tiwari (Neeraj)

10/18/2018

I missed rearranging the columns as per the desired output. :)

Here is the revised code...

import pandas as pd
import json

data = {
    'locationId':123546987,
    'parameters':[
        {
            'parameterId':'11',
            'unitId':'81',
            'customParameter':False,
            'readings':[
                {
                    'timestamp':1538957700,
                    'value':2306.078
                },
                {
                    'timestamp':1538959500,
                    'value':2305.892
                },
                {
                    'timestamp':1538961300,
                    'value':2305.981
                }
            ]
        },
        {
            'parameterId':'1',
            'unitId':'1',
            'customParameter':False,
            'readings':[
                {
                    'timestamp':1538957700,
                    'value':25.575
                },
                {
                    'timestamp':1538959500,
                    'value':25.572
                },
                {
                    'timestamp':1538961300,
                    'value':25.575
                }
            ]
        }
    ]
}

print(data)

val = json.loads(json.dumps(data))
val = pd.DataFrame(val)
print(val)

def getReadings(dict):
    d = pd.DataFrame(dict['readings'])
    d['unitId'] = dict['unitId']
    d['parameterId'] = dict['parameterId']
    return d

df = pd.DataFrame()
for i in range(len(val)):
    df1 = getReadings(val.iloc[i,1])
    df1['locationId'] = val.iloc[i,0]
    df1['customParameter'] = 'False'
    df = df.append(df1)

df = df[['parameterId','customParameter','timestamp','value','unitId']]
print(df)