Python - convert json format column to dataframe

Question

I have a dataframe with single column and it's in json format.

result
[{'Start Time':'2021-01-01 00:00:00', 'Arrival Time':'2021-02-02 00:00:00'...

and I want to convert this json string to dataframe.

import json
import pandas as pd

d = json.loads(df['result'].replace("'",'"'))
df = pd.DataFrame(d)

I get **Typeerror: the json obejct must be str, bytes or bytearray, not series.**

Am I missing a step? Now I would like to build a new dataframe which shows the json column as a single dataframe, how can this be done? I've been struggle with this for whole day. Below format is output I am looking for.

start Time                Arrival Time
2021-01-01 00:00:00       2021-02-02 00:00:00
2021-01-02 00:00:00       2021-02-03 00:00:00
2021-01-03 00:00:00       2021-02-04 00:00:00
2021-01-04 00:00:00       2021-02-05 00:00:00
2021-01-05 00:00:00       2021-02-06 00:00:00

Answer 1

You are trying to load the column values of an existing dataframe as a different data frame. A simple approach is below

df["result"] = df["result"].apply(lambda x: x.replace("'", '"'))
df["result"] = df["result"].apply(lambda x: json.loads(x))
new_df = DataFrame(df["result"].values.tolist())

This will give you a dataframe with Start Time, Arrival Time .. as columns.

But, since your structure already seems to be stringified dict, you can do this as well.

import ast
import pandas as pd
raw_data = "[{'Start Time': '2021-01-01 00:00:00', 'Arrival Time': '2021-02-02 00:00:00', 'temp': 35.6},{'Start Time': '2021-01-02 00:00:00', 'Arrival Time': '2021-02-03 00:00:00', 'temp': 40.1},{'Start Time': '2021-01-03 00:00:00', 'Arrival Time': '2021-02-04 00:00:00', 'temp': 25.6}]"
json_data = ast.literal_eval(raw_data)
df = pd.DataFrame(json_data)