pandas dataframe to_json(orient='split') change uint64 to negative int

[yamyamyuo]

Code Sample

import pandas
import json
d = {'col1': [13342205958987758245, 12388075603347835679], 'col2': [13342204859476130034, 12388075603347835679]}
df = pandas.DataFrame(data=d)

print (df)
print (df.dtypes)
print ("--- to json output")

print (df.head(5).fillna('NULL').to_json(orient='split'))

print ("--- to dict output")
print (json.dumps(df.head(5).fillna('NULL').to_dict(orient='split')))

Problem description

the output of the above code is :

                   Col1                  Col2
0  13342205958987758245  12388075603347835679
1  13342204859476130034  12388075603347835679

--- dtypes
Col1    uint64
Col2    uint64
dtype: object
--- to json output
{"columns":["Col1","Col2"],"index":[0,1,2,3,4],"data":[[-5104538114721793371,-6058668470361715937],[-5104539214233421582,-6058668470361715937],[-5104583194698550022,-6058668470361715937],[-3637227675748224855,-6058668470361715937],[-6228767139342072872,-6058667370850087726]]}
--- to dict output
{"index": [0, 1, 2, 3, 4], "data": [[13342205958987758245, 12388075603347835679], [13342204859476130034, 12388075603347835679], [13342160879011001594, 12388075603347835679], [14809516397961326761, 12388075603347835679], [12217976934367478744, 12388076702859463890]], "columns": ["Col1", "Col2"]}

you can see the data are different for these two similar functions: to_json and to_dict.
I believe that the output of to_dictis correct but to_json has change my original digits.

Expected Output

The expected output is what we see in the to_dict output.

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 2.7.14.final.0
python-bits: 64
OS: Darwin
OS-release: 16.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 38.5.0
Cython: None
numpy: 1.14.0
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.6.1
pytz: 2017.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: 1.0.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: None
bs4: None
html5lib: None
sqlalchemy: 0.8.0
pymysql: None
psycopg2: None
jinja2: 2.7.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[WillAyd]

Can you post a minimally reproducible example, i.e. something where we could fully copy / paste what you've provided and recreate?

FWIW the numbers you have exceed the int64 limit so my guess is there's some issue with maintaining the unsigned portion of that throughout the process

[yamyamyuo]

@WillAyd Thanks for your advice. I have modified my code. Furthermore, I tested on other environment, it raise Exception in converting to_json:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Python/2.7/site-packages/pandas/core/generic.py", line 859, in to_json
    default_handler=default_handler)
  File "/Library/Python/2.7/site-packages/pandas/io/json.py", line 34, in to_json
    date_unit=date_unit, default_handler=default_handler).write()
  File "/Library/Python/2.7/site-packages/pandas/io/json.py", line 77, in write
    default_handler=self.default_handler)
OverflowError: long too big to convert

I think this is the expected feedback under my new environment

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

pd.show_versions()
INSTALLED VERSIONS

---

commit: None
python: 2.7.11.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

pandas: 0.15.2
nose: 1.3.6
Cython: 0.22
numpy: 1.9.2
scipy: 0.16.1
statsmodels: 0.6.1
IPython: None
sphinx: None
patsy: 0.4.1
dateutil: 2.4.2
pytz: 2015.7
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.5.0
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
rpy2: None
sqlalchemy: None
pymysql: None
psycopg2: None

[mroeschke]

Looks correct on master now. Could use a test

In [30]: import pandas
    ...: import json
    ...: d = {'col1': [13342205958987758245, 12388075603347835679], 'col2': [13342204859476130034, 123880756033478
    ...: 35679]}
    ...: df = pandas.DataFrame(data=d)
    ...:
    ...: print (df)
    ...: print (df.dtypes)
    ...: print ("--- to json output")
    ...:
    ...: print (df.head(5).fillna('NULL').to_json(orient='split'))
    ...:
    ...: print ("--- to dict output")
    ...: print (json.dumps(df.head(5).fillna('NULL').to_dict(orient='split')))
                   col1                  col2
0  13342205958987758245  13342204859476130034
1  12388075603347835679  12388075603347835679
col1    uint64
col2    uint64
dtype: object
--- to json output
{"columns":["col1","col2"],"index":[0,1],"data":[[13342205958987758245,13342204859476130034],[12388075603347835679,12388075603347835679]]}
--- to dict output
{"index": [0, 1], "columns": ["col1", "col2"], "data": [[13342205958987758245, 13342204859476130034], [12388075603347835679, 12388075603347835679]]}

[johnmantios]

take