pandas interpret str in json as json-CodePudding

I have a json file.

{
    "a": "1", 
    "b": "{"c": 2}"
}

I want to read this json in pandas but it gives a ValueError: Unexpected character found when decoding object value.

CodePudding user response：

Escape the nested quotes:

"b": "{\"c\": 2}"

CodePudding user response：

Your JSON is invalidated because of the double quotes for c. There are several online JSON format validator such as https://jsonformatter.curiousconcept.com/.

A correct JSON formats of your json can be varied. For example:

{
    "a": "1", 
    "b": "{c: 2}"
}

{
    "a": "1", 
    "b": {"c": "2"}
}

I am not sure which format you want.

For the first format, you can change the format and read using pandas as follows:

import json
import pandas as pd


def json_validator(f):
    """
    {
      "a": "1",
      "b": "{"c": 2}" -> "b": "{c: 2}"
    }
    :param f: file
    :return: validated json format string
    """
    data = ""
    for line in f.readlines():
        if ":" in line:
            # for key and value
            key, value = [l.strip().replace('"', '') for l in line.split(":", 1)]
            key = f'"{key}"'
            if "," in value:
                value = f'"{value.replace(",", "")}",'
            else:
                value = f'"{value.replace(",", "")}"'
            data  = f'  {key}: {value}\n'
        else:
            # for { and }
            data  = line
    return data


with open('a.json') as f:
    json_string = json_validator(f)

df = pd.DataFrame(json.loads(json_string), index=[0])
print(df)
"""
   a       b
0  1  {c: 2}
"""