Pandas read_json() fails with a simple JSON string

Question

I'm trying to create a DataFrame object from a json string (not a file):

json_string = "[{'code': '8', 'name': 'Human'}, {'code': '11', 'name': 'Orc'}]"
df = pd.read_json(json_string)

but this approach causes the following error:

ValueError: Expected object or value

The documentation makes it sound like this would be possible:

pandas.read_json(path_or_buf=None, orient=None, typ='frame', dtype=True, convert_axes=True, convert_dates=True, keep_default_dates=True, numpy=False, precise_float=False, date_unit=None, encoding=None, lines=False, chunksize=None, compression='infer')

Convert a JSON string to pandas object

path_or_buf : a valid JSON string or file-like, default: None The string could be a URL. Valid URL schemes include http, ftp, s3, and file. For file URLs, a host is expected. For instance, a local file could be file://localhost/path/to/table.json

I've already tried calling the method with several combinations of orient and none succeeded. Any tips on how to accomplish this?

I really don't want the overhead of saving my string to a file to be able to use read_json() successfully.

JSON object keys and values should be enclosed with double quotes not single. — Sohaib Farooqi
– Sohaib Farooqi, Commented Feb 3, 2018 at 1:23
I know and if I could change that, I would. But in this case is no-can't-do. — karlphillip
– karlphillip, Commented Feb 3, 2018 at 1:26
@karlphillip, if you can't change the quotation marks, could you at least add a preprocessing line before reading the json? json_string = json_string.replace('\'', '\"') — Peter Leimbigler
– Peter Leimbigler, Commented Feb 3, 2018 at 1:28

Sohaib Farooqi · Accepted Answer · 2018-02-03 01:35:45Z

11

You can use eval to evaluate the string to dict and then pass directly to DataFrame constructor.

>>> import pandas as pd
>>> pd.DataFrame(eval("[{'code': '8', 'name': 'Human'}, {'code': '11', 'name': 'Orc'}]"))

    code   name
0   8      Human
1   11     Orc

Similarly you can also use ast.literal_eval in place of eval

>>> import ast
>>> pd.DataFrame(ast.literal_eval("[{'code': '8', 'name': 'Human'}, {'code': '11', 'name': 'Orc'}]"))

    code   name
0   8      Human
1   11     Orc

edited Feb 3, 2018 at 1:35

answered Feb 3, 2018 at 1:31

Sohaib Farooqi

5,6944 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hilberts_drinking_problem Over a year ago

Compare eval('print("delete root")') to ast.literal_eval('print("delete root")'). I would stick with the second option.

Evan Wies · Accepted Answer · 2024-02-07 15:04:25Z

It appears that OP is parsing a Python object and not a JSON object, so read_json is not appropriate.

If one is actually ingesting Python structs, then eval is appropriate.

The eval method will not work if you have JSON booleans, because Python does not parse true or false as True/False.

Also, your json_string is not valid JSON. Single quotes are not delimiters in JSON. So you must also change that (the comments indicate this).

If one did need to have pandas.read_json process a valid JSON string, then per the Pandas GitHub issue that caused this to break, you should wrap json_string in a StringIO so that it may be read akin to a file.

import pandas as pd
from io import StringIO

json_string = '[{"code": "8", "name": "Human"}, {"code": "11", "name": "Orc"}]'
df = pd.read_json(StringIO(json_string))

Collectives™ on Stack Overflow

Pandas read_json() fails with a simple JSON string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related