ENH: Add support to read_json to encode character escape hex codes to utf-8 characters

[ParfaitG]

• I have checked that this issue has not already been reported.
• I have confirmed this bug exists on the latest version of pandas.
• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Code Sample

import pandas as pd

# S3 (REQUIRES s3fs)
json_df = pd.read_json("s3://public-r-data/ghcran.json")

# URL (NO REQUIREMENT)
json_df = pd.read_json("http://public-r-data.s3-website-us-east-1.amazonaws.com/ghcran.json")

json_df.loc[884, "Title"]
# Misc Functions of Eduard Sz<c3><b6>cs

json_df.loc[213, "Author"]
# Kirill M<c3><bc>ller [aut, cre]

json_df.loc[336, "Maintainer"]
# H<c3><a9>l<c3><a8>ne Morlon <[email protected]>

Problem description

All UTF-8 encoding should render to their true character form and not hex code representation. Users need reliability in text value consistency from json format to pandas containers. Looking at some source code, IO JSON appears to use the ujson C extension which may be causing above behavior. I posted same question on StackOverflow with an interesting workaround answer.

For reference see https://www.utf8-chartable.de/ for table of hex codes and corresponding characters. For c2-c9 codes, use drop down values: U+0000 ... U+007F: Basic Latin, U+0080 ... U+00FF: Latin-1 Supplement, U+0100 ... U+017F: Latin Extended-A, U+0180 ... U+024F: Latin Extended-B.

Expected Output

All angle bracketed hex codes rendered to true accent or diatric characters.

json_df.loc[884, "Title"]
# Misc Functions of Eduard Szöcs

json_df.loc[213, "Author"]
# Kirill Müller [aut, cre]

json_df.loc[336, "Maintainer"]
# Hélène Morlon <[email protected]>

Output of pd.show_versions()

INSTALLED VERSIONS

commit : fa3dbc1
python : 3.9.2.final.0
python-bits : 64
OS : Linux
OS-release : 5.8.0-50-generic
Version : #56~20.04.1-Ubuntu SMP Mon Apr 12 21:46:35 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.3.0.dev0+1672.gfa3dbc117f
numpy : 1.20.2
pytz : 2021.1
dateutil : 2.8.1
pip : 21.1.1
setuptools : 49.6.0.post20210108
Cython : 0.29.23
pytest : 6.2.3
hypothesis : 6.10.1
sphinx : 3.5.4
blosc : None
feather : None
xlsxwriter : 1.4.0
lxml.etree : 4.6.3
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 2.11.3
IPython : 7.23.0
pandas_datareader: None
bs4 : 4.9.3
bottleneck : 1.3.2
fsspec : 2021.04.0
fastparquet : 0.5.0
gcsfs : 2021.04.0
matplotlib : 3.4.1
numexpr : 2.7.3
odfpy : None
openpyxl : 3.0.7
pandas_gbq : None
pyarrow : 4.0.0
pyxlsb : None
s3fs : 2021.04.0
scipy : 1.6.3
sqlalchemy : 1.4.12
tables : 3.6.1
tabulate : 0.8.9
xarray : 0.17.0
xlrd : 2.0.1
xlwt : 1.3.0
numba : 0.53.1

[asishm]

Is this a pandas issue? I see the same behavior with requests.get("...").json() as well

In [26]: from requests import get

In [27]: data = get("http://public-r-data.s3-website-us-east-1.amazonaws.com/ghcran.json").json()

In [28]: data[884]['Title']
Out[28]: 'Misc Functions of Eduard Sz<c3><b6>cs'

In [29]: data[213]['Author']
Out[29]: 'Kirill M<c3><bc>ller [aut, cre]'

In [30]: data[336]['Maintainer']
Out[30]: 'H<c3><a9>l<c3><a8>ne Morlon <[email protected]>'

[ParfaitG]

Hmmm....good point @asishm. Confirmed with standard library urllib which pandas uses:

import urllib.request
import json

rdata_url = "http://public-r-data.s3-website-us-east-1.amazonaws.com/ghcran.json"
 
with urllib.request.urlopen(rdata_url) as req:
    rdata = json.loads(req.read())
    
print(rdata[884]['Title'])
# Misc Functions of Eduard Sz<c3><b6>cs

print(rdata[213]['Author'])
# Kirill M<c3><bc>ller [aut, cre]

print(rdata[336]['Maintainer'])
# H<c3><a9>l<c3><a8>ne Morlon <[email protected]>

Given now we know this is not a pandas bug but general json handling in Python, should we consider an enhancement in read_json to re-encode these hex values to corresponding UTF-8 accent characters when they appear in json content?

[ParfaitG]

Is your feature request related to a problem?

See above problem description. Currently, read_json does not translate character escapes of hex code but imports such angle bracketed values as is.

Describe the solution you'd like

See above expected output. Ideally, read_json converts UTF-8 hex codes to corresponding UTF-8 characters which mainly involve accented/diacritic characters in object columns.

API breaking implications

No new extra arguments or wrapper methods in IO JSON but simply an additional re-encoding step in underlying parsing to DataFrame that converts accented characters as they should appear. Possibly, too, the counterpart for write_json to retain UTF-8 characters in output.

Describe alternatives you've considered

Current method (from posted solution in StackOverflow post) uses a user defined method call with Series.apply on object dtype columns. Ideally, run below in a C-level solution to avoid Python looping over every four characters of every string value in the column.

def reencode(string):
    def is_hex(i):
        return (48 <= i <= 57) or (97 <= i <= 102)
    
    def to_bytes(arr):
        if len(arr) != 4:
            return None
        
        a, b, c, d = arr
        if a == 60 and is_hex(b) and is_hex(c) and d == 62:
            return bytes.fromhex(chr(b) + chr(c))

        return None

    old = string.encode('ascii')
    new = bytearray()

    i = 0
    while i < len(old):
        b = to_bytes(old[i:i+4])
        if b:
            new.extend(b)
            i += 4
        else:
            new.append(old[i])
            i += 1
            
    try:
        retvalue = new.decode('utf8')
    except UnicodeDecodeError as ue:
        retvalue = string
        
    return retvalue
  
json_df["Title"] = json_df["Title"].apply(reencode)
json_df["Author"] = json_df["Author"].apply(reencode)
json_df["Maintainer"] = json_df["Maintainer"].apply(reencode)

json_df.loc[884, "Title"]
# Misc Functions of Eduard Szöcs

json_df.loc[213, "Author"]
# Kirill Müller [aut, cre]

json_df.loc[336, "Maintainer"]
# Hélène Morlon <[email protected]>

[mroeschke]

Since it appears an issue in urllib, it would be best if this would be addressed upstream in Python so closing