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