How to convert a json object to pandas json type column

Question

I have a code like below

import json
import ast
import pandas as pd

data = [[110636, [{'Name': 'cd0', 'id': '1'}, {'Name': 'cd1', 'id': '2'}, {'Name': 'cd2', 'id': '3'}]]]
df = pd.DataFrame(data, columns=['id','json_load'])
df['json_load'] = df['json_load'].astype(str).apply(json.loads)
print(df)
exploded = df.explode("json_load")
df = pd.json_normalize(exploded['json_load'])

While executing the code, I am getting error like json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 3 (char 2) even though it's a valid JSON

Can anyone please help?

Answer 1

You simply need to rewrite your line with JSON to this one:

data = [[110636, '[{"Name": "cd0", "id": "1"}, {"Name": "cd1", "id": "2"}, {"Name": "cd2", "id": "3"}]']]

First, JSON object must be str, bytes, or bytearray, not list. Thus, add a single quote to make this list a string.
Second, JSON format is expected to have double quoting for your name properties.

If you run your code with mine correction, the output would be like this:

  Name id
0  cd0  1
1  cd1  2
2  cd2  3

On the other hand, if that [[110636, [{'Name': 'cd0', 'id': '1'}, {'Name': 'cd1', 'id': '2'}, {'Name': 'cd2', 'id': '3'}]]] is the exact format of your data, try this:

data = [[str(item).replace("'", "\"") for v1 in data for item in v1]]

before applying json.loads to your df.

The reason you actually getting an error message is that both id, which is 110636, and the following list have to be in quotes, as those are JSON properties