Flatten Nested Json Into Pandas Dataframe Columns

I have a pandas column with nested json data string. I'd like to flatten the data into multiple pandas columns. Here's data from a single cell: rent['ques'][9] = '{'Rent': [{'Nam

Solution 1:

Try this:

df['quest'] = df['quest'].str.replace("'", '"')
dfs = []
for i in df['quest']:
    data = json.loads(i)
    dfx = pd.json_normalize(data, record_path=['Rent'], meta=[['Vacancy', 'Name'], ['Vacancy', 'Unit'], ['Vacancy', 'Value']])
    dfs.append(dfx)   

df = pd.concat(dfs).reset_index(drop=['index'])
print(df)


     Name  Value Unit Vacancy.Name Vacancy.Unit Vacancy.Value
0  Asking  16.07  Usd      Vacancy          Pct        25.341
1  Asking  16.07  Usd      Vacancy          Pct        25.341
2  Asking  16.07  Usd      Vacancy          Pct        25.341