0

I'm trying to convert a list of JSON objects to a pandas DataFrame. However, the JSON object has unquoted True/False, and None values, which appears to make read_json error out. Is there a way to make the pandas.read_json method handle Boolean and None values?

Updated with real code (my actual JSON I am getting from a web service, but I'm not able to post the real content):

import pandas as pd
x = '[{"A": "some text","B": True,"C":7},{"A": "more text","B":False,"C":8},{"A":None,"B":False,"C":9}]'
pd.read_json(x)

ValueError: Expected object or value

if I quote the Nones and Booleans it seems to work.

import pandas as pd
x = '[{"A": "some text","B": "True","C":7},{"A": "more text","B":"False","C":8},{"A":"None","B":"False","C":9}]'
pd.read_json(x)

Of course they are then strings rather than Booleans and NaNs

Updated with error message

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-42-c251da58cd34> in <module>()
      1 import pandas as pd
      2 x = '[{"A": "some text","B": True,"C":7},{"A": "more text","B":False,"C":8},{"A":None,"B":False,"C":9}]'
----> 3 pd.read_json(x)

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in read_json(path_or_buf, orient, typ, dtype, convert_axes, convert_dates, keep_default_dates, numpy, precise_float, date_unit)
    196         obj = FrameParser(json, orient, dtype, convert_axes, convert_dates,
    197                           keep_default_dates, numpy, precise_float,
--> 198                           date_unit).parse()
    199 
    200     if typ == 'series' or obj is None:

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in parse(self)
    264 
    265         else:
--> 266             self._parse_no_numpy()
    267 
    268         if self.obj is None:

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in _parse_no_numpy(self)
    481         if orient == "columns":
    482             self.obj = DataFrame(
--> 483                 loads(json, precise_float=self.precise_float), dtype=None)
    484         elif orient == "split":
    485             decoded = dict((str(k), v)

ValueError: Expected object or value

So, if I treat the JSON as a python object rather than JSON I get a different error. This code

x =   [{"A": "some text","B": "True","C":7},{"A": "more text","B":"False","C":8},{"A":"None","B":"False","C":9}]
pd.read_json(x)

yields

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-59-076202b1f4ce> in <module>()
      1 x =   [{"A": "some text","B": True,"C":7},{"A": "more text","B":False,"C":8},{"A":"None","B":False,"C":9}]
----> 2 pd.read_json(x)

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in read_json(path_or_buf, orient, typ, dtype, convert_axes, convert_dates, keep_default_dates, numpy, precise_float, date_unit)
    196         obj = FrameParser(json, orient, dtype, convert_axes, convert_dates,
    197                           keep_default_dates, numpy, precise_float,
--> 198                           date_unit).parse()
    199 
    200     if typ == 'series' or obj is None:

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in parse(self)
    264 
    265         else:
--> 266             self._parse_no_numpy()
    267 
    268         if self.obj is None:

C:\Users\Chris\AppData\Local\Continuum\Miniconda3\lib\site-packages\pandas\io\json.py in _parse_no_numpy(self)
    481         if orient == "columns":
    482             self.obj = DataFrame(
--> 483                 loads(json, precise_float=self.precise_float), dtype=None)
    484         elif orient == "split":
    485             decoded = dict((str(k), v)

TypeError: Expected String or Unicode
smci
  • 32,567
  • 20
  • 113
  • 146
Chris
  • 1,313
  • 2
  • 13
  • 26
  • 2
    Are you sure that's meant to be JSON? JSON looks more like `{"A": true, "C": null, "B": false}'`, I think. – DSM Nov 12 '14 at 22:22
  • I think so, it seems to align to the records format shown in the [pandas documentation](http://pandas.pydata.org/pandas-docs/dev/io.html#writing-json), i.e. [{column -> value}, ... , {column -> value}] – Chris Nov 12 '14 at 23:00
  • 3
    No, I mean I don't think your string is valid JSON. That particular string looks like valid Python, though (so `ast.literal_eval` will turn it into a Python object). – DSM Nov 12 '14 at 23:24
  • Okay, I see what your saying. So yes I can turn it into a Python object. I'm still not sure how to take it to a DataFrame through, if I use read_json on the object I get a new error (added to the question.) – Chris Nov 13 '14 at 02:46
  • 2
    @Chris Once you evaluated it into a python object, you can simply feed that to `DataFrame(..)` (no need for `read_json` then) – joris Nov 13 '14 at 08:52
  • 1
    As DSM said, that's not JSON, and you can parse it using `ast.literal_eval`, as illustrated here: https://stackoverflow.com/q/42631097/4014959 – PM 2Ring Dec 14 '19 at 10:17

0 Answers0