How To Convert Python Json Rows To Dataframe Columns Without Looping

I'm trying to figure out how to do the following without using a loop. I have a dataframe that has several columns including one that has a JSON string. What I'm trying to do is c

Solution 1:

You can first convert Json Column to dict if necessary by json.loads:

import json

df = pd.DataFrame({'Column 1':[123],
                   'Column 2':['ABC'], 
                   'Json Column':['{"anotherNumber":345,"anotherString":"DEF"}']})
print (df)

   Column 1 Column 2                                     Json Column
0       123      ABC  {'anotherString': 'DEF', 'anotherNumber': 345}

print (type(df.ix[0,'Json Column']))
<class 'str'>

df['Json Column'] =  df['Json Column'].apply((json.loads))

print (type(df.ix[0,'Json Column']))
<class 'dict'>

Then generate list of lists and create Dataframe from constructor:

print (df['Json Column'].values.tolist())
[{'anotherString': 'DEF', 'anotherNumber': 345}]

df1 = pd.DataFrame(df['Json Column'].values.tolist())
print (df1)
   anotherNumber anotherString
0            345           DEF

Last concat to original, where column Json Column was removed by drop:

print (pd.concat([df.drop('Json Column', axis=1), df1], axis=1))
   Column 1 Column 2  anotherNumber anotherString
0123      ABC            345           DEF

Solution 2:

1. Standard step of converting String to Json at the time of reading

import json
import pandas as pd

df = pd.DataFrame({'Column 1':[123],
               'Column 2':['ABC'], 
               'Json_Column':['{"anotherNumber":345,"anotherString":"DEF"}']})
df
    Column 1    Column 2    Json_Column
0123         ABC    {"anotherNumber":345,"anotherString":"DEF"}

df.Json_Column = df.Json_Column.apply(lambda x: json.loads(x))

df.Json_Column
0    {'anotherNumber': 345, 'anotherString': 'DEF'}
Name: Json_Column, dtype: object

2. Convert every json row to a Data Frame

df.Json_Column=df.Json_Column.apply(lambdax:pd.DataFrame([x]))df.Json_Column0anotherNumberanotherString034...Name:Json_Column,dtype:object

3. Concatenate the Data Frame present in all the rows to a single Data Frame

importfunctoolstemp_json_df= functools.reduce(lambda x,y: pd.concat([x,y]), df.Json_Column)

temp_json_df
   anotherNumber    anotherString
0345              DEF

4. Merge master and temp_json_df

df = pd.concat([df.drop(columns='Json_Column'), temp_json_df], axis=1)

df
 Column 1   Column 2    anotherNumber   anotherString
0    123         ABC              345            DEF