BUG: Index lost when serializing to json

[rikardn]

• 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.

---

When serializing a pandas DataFrame to json the index is usally serialized by use of the primary_key in the json. In some cases this does not work. For example:

import pandas as pd

df = pd.DataFrame({'ind': ['TIME', 'TIME'], 'id': [1, 1], 'data': [0.1, 0.2]})
df.set_index(['ind', 'id'], inplace=True)

js = df.to_json(orient='table')
new = pd.read_json(js, orient='table')

The json string js:

{"schema":{"fields":[{"name":"ind","type":"string"},{"name":"id","type":"integer"},{"name":"data","type":"number"}],"pandas_version":"0.20.0"},"data":[{"ind":"TIME","id":1,"data":0.1},{"ind":"TIME","id":1,"data":0.2}]}

The new data frame created by reading the json doesn't have the same index.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 2cb9652
python : 3.8.5.final.0
python-bits : 64
OS : Linux
OS-release : 5.4.0-74-generic
Version : #83-Ubuntu SMP Sat May 8 02:35:39 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.2.4
numpy : 1.20.2
pytz : 2019.3
dateutil : 2.7.3
pip : 21.1.1
setuptools : 45.2.0
Cython : None
pytest : 6.2.4
hypothesis : None
sphinx : 3.5.4
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 2.11.3
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : 2021.04.0
fastparquet : None
gcsfs : None
matplotlib : 3.4.2
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : 1.6.3
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[TomAugspurger]

The JSON table schema spec requires that the set of fields forming the primary key is unique: https://specs.frictionlessdata.io/table-schema/#missing-values

I am surprised that we don't raise a ValueError here though. Perhaps we issue a FutureWarning saying that this will raise in the future.

[rikardn]

Thanks @TomAugspurger for the explanation. It makes sense to have a well designed primary key in a table. I can solve my problem by redesigning the index.

However this means that not all valid pandas DataFrames can be serialized to json. Is there another serialization to json available that can convert any DataFrame to json and back again? If not I would consider this to be a very useful feature.

[simonjayhawkins]

Thanks @rikardn for the report. looks like a duplicate of #37600 so closing and can continue discussion there.

[TomAugspurger]

I'm not sure. From: Rikard Nordgren ***@***.***> Sent: Monday, June 21, 2021 6:29 AM To: pandas-dev/pandas ***@***.***> Cc: Tom Augspurger ***@***.***>; Mention ***@***.***> Subject: Re: [pandas-dev/pandas] BUG: Index lost when serializing to json (#42152) Thanks @TomAugspurger<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FTomAugspurger&data=04%7C01%7Ctom.augspurger%40microsoft.com%7Cb353e881da184d7a2fb108d934a7cfd0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637598717622188414%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=bIxMdFShu7%2BnJ6fVCqi%2FwaLH5pzO4d5uTD5TWBtmYaM%3D&reserved=0> for the explanation. It makes sense to have a well designed primary key in a table. I can solve my problem by redesigning the index. However this means that not all valid pandas DataFrames can be serialized to json. Is there another serialization to json available that can convert any DataFrame to json and back again? If not I would consider this to be a very useful feature. - You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpandas-dev%2Fpandas%2Fissues%2F42152%23issuecomment-864958852&data=04%7C01%7Ctom.augspurger%40microsoft.com%7Cb353e881da184d7a2fb108d934a7cfd0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637598717622198406%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=mFgaYnllhNYrkfEWL8ZtIg5HlXdZOEftyrC2L1yfZ40%3D&reserved=0>, or unsubscribe<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAAKAOIRC7H2I5VLFDBWRA63TT4PA3ANCNFSM47BIT3OA&data=04%7C01%7Ctom.augspurger%40microsoft.com%7Cb353e881da184d7a2fb108d934a7cfd0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637598717622198406%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=wIFy5wOsJaRGnfMZQnkrqYR9FoneJkpvkSwqMLiXC5c%3D&reserved=0>.