pd.read_json(file, lines=True) does not work if json has quotes inside it

[Bigbrd]

Code Sample, a copy-pastable example if possible

{"errors":["This check-in does not exist, it may have been deleted."]},
{"list":{"id":487004,"description":"foo.”\r\n\r\n* “I am aware that I’m drafting an email responding to a complaint.”\r\n\r\n* “I am aware that I’m wondering who will win.”\r\n\r\nThe great thing about this exercise is that it is generalizable. You practice during the meditation, but then you use it for your own goals during your day to day."..........}

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.10.final.0
python-bits: 64
OS: Darwin
OS-release: 15.6.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.19.2
nose: None
pip: 9.0.1
setuptools: 32.3.1
Cython: None
numpy: 1.11.3
scipy: 0.17.1
statsmodels: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.6.0
pytz: 2016.10
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.5.3
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: None
pandas_datareader: None

Problem description

List has quotes inside the json data. Expected to read this data line by line, but we get a UnicodeDecodeError at the position of that inner quote in the description

Expected Output

read successful

Output:

Traceback (most recent call last):
data = pd.read_json(fileName, lines=True)
File "/usr/local/lib/python2.7/site-packages/pandas/io/json.py", line 275, in r
ead_json
json = u'[' + u','.join(lines) + u']'
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 4924: ordina
l not in range(128)

[jreback]

pd.show_versions() as the issue tracker indicates

[Bigbrd]

updated.

[jreback]

JSON validator says that isn't valid JSON.

[rouzazari]

Agree on the JSON provided by @Bigbrd not being valid; however, I tried it with a simple valid JSON string and received the same results.

Potential UnicodeDecodeError for non-ascii strings in Python 2.7

Contents of /tmp/test.json (note the 0xe2 character after the first "foo"):

{"a": "foo”.", "b": "bar."}
{"a": "foo", "b": "bar"}

Result:

import pandas as pd
pd.read_json('/tmp/test.json', lines=True)
...
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 10: ordinal not in range(128)

---

Why does this happen?

This can happen in Python 2.7 if the default encoding is set to ascii (check sys.getdefaultencoding()). StringIO will convert the input string to ascii when lines=True, resulting in a UnicodeDecodeError because of mixing utf-8 and ascii strings.

    if lines:
        lines = list(StringIO(json.strip()))  # <<- converts 'utf-8' to 'ascii'
        json = u'[' + u','.join(lines) + u']'  # <<- mixes 'utf-8' and 'ascii'

Note that a user can set the default encoding using sys.setdefaultencoding('utf-8') but this is discouraged and is a no-op in Python 3 (see SO article here)

---

Next steps

I would consider myself a novice on fixing this type of issue, but one (hack-y) solution could be to decode the string to utf-8 before calling StringIO:

    if lines:
        if compat.PY2:
            json = json.decode(encoding or 'utf-8')
        lines = list(StringIO(json.strip()))
        json = u'[' + u','.join(lines) + u']'

Interestingly, nosetests has the default encoding set to utf-8 (I checked sys.getdefaultencoding() while running nosetests pandas/io/tests/json/), so I'm not sure how to write a test for this other than changing the default encoding to ascii.

[Bigbrd]

Yes please ignore my crappy fake json, @rouzazari covered my scenario perfectly.

The solution proposed with switching to utf-8 before StringIO does sound like an efficient solution for my scenario. +1 if we can implement that or something like it

[jreback]

interesting. So py27 is broken again. ok @Bigbrd or @rouzazari a PR would be welcome.

we have a context manager pandas.util.testing.stdin_encoding which I think will allow you to check this in testing (or maybe need to create a similar one to save / change the system encoding / restore the original).

[rouzazari]

Seems like this has been a compatibility issue since lines=True was introduced with 6efd743.

I'm submitting a PR with my recommended change, which is simply to remove the forced conversion to unicode, as it seems like FrameParser and SeriesParser handle this just fine (and it is not an issue for non-lines JSON files).