BUG: DataFrame.to_json ignores index if it is repeated in the DataFrame.

[juanmigueltiscar]

• [x ] I have checked that this issue has not already been reported.
  Similar to DataFrame.to_json silently ignores index parameter for most orients. #25513

• [x ] 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, a copy-pastable example

import pandas as pd

df=pd.DataFrame({'column':[0,1,2,3,4], 'probe':[0,1,2,2,3]})
df.set_index('probe',inplace=True)
out = df.to_json(orient='table', index=True)

assert 'primaryKey' in out

#### Problem description
DataFrame.to_json ignores index if it is repeated in the DataFrame.
Since pandas allows dataframes with repeated indexes, 
the function `to_json` should be able to write them.
If you want to limit this functionality, at least one warning should be implemented.

Note that 'to_pickle' works fine with repeated indexes, as expected.

#### Expected Output
Not Error in assert

#### Output of ``pd.show_versions()``

<details>

INSTALLED VERSIONS
------------------
commit           : 67a3d4241ab84419856b84fc3ebc9abcbe66c6b3
python           : 3.6.6.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
Version          : 10.0.19041
machine          : AMD64
processor        : Intel64 Family 6 Model 158 Stepping 9, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : ES
LOCALE           : es_ES.cp1252

pandas           : 1.1.4
numpy            : 1.16.3
pytz             : 2018.5
dateutil         : 2.7.3
pip              : 20.2.3
setuptools       : 41.2.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.4.2
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 2.10.3
IPython          : 7.10.1
pandas_datareader: None
bs4              : 4.9.1
bottleneck       : None
fsspec           : None
fastparquet      : None
gcsfs            : None
matplotlib       : 2.2.3
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pytables         : None
pyxlsb           : None
s3fs             : None
scipy            : 1.2.1
sqlalchemy       : 1.3.18
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
numba            : None

</details>

[erfannariman]

I am not sure if this is a bug perse, since a json should not have a duplicate keys and some formats don't allow it, see here. We could maybe warn the user here, or document it properly.

[juanmigueltiscar]

I agree, but it does not happen with to_pickle(), for example. I didn't know how to catalog this issue.
Anyway, using to_json(orient='table') with a DataFrame with duplicated indexes does not imply a json with duplicated keys.

Notice that only 'primaryKey': [] needs to be included in the resulting json.

[jbrockmendel]

cc @WillAyd

[WillAyd]

Comparing it to to_pickle isn't a worthwhile endeavor as pickle is an entirely different format / protocol. As mentioned above, JSON should have unique keys, though it isn't explicitly forbidden.

[juanmigueltiscar]

Comparing it to to_pickle isn't a worthwhile endeavor as pickle is an entirely different format / protocol. As mentioned above, JSON should have unique keys, though it isn't explicitly forbidden.

Ok, I get you point of view. If you think that it is okey I will close the ticket. My concern passed when I changed the way I stored my DataFrames. Everything started to work wrong, until I discovered this "problem".

[WillAyd]

I think it is fine to keep open. Would need a community PR though. It does technically go against suggested JSON standards so unlikely that a core developer would prioritize a patch

…

Sent from my iPhone

On Nov 10, 2020, at 9:53 AM, juanmigueltiscar ***@***.***> wrote:  Comparing it to to_pickle isn't a worthwhile endeavor as pickle is an entirely different format / protocol. As mentioned above, JSON should have unique keys, though it isn't explicitly forbidden. Ok, I get you point of view. If you think that it is okey I will close the ticket. My concern passed when I changed the way I stored my DataFrames. Everything started to work wrong, until I discovered this "problem". — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[juanmigueltiscar]

I think it is fine to keep open. Would need a community PR though. It does technically go against suggested JSON standards so unlikely that a core developer would prioritize a patch
…
Sent from my iPhone
On Nov 10, 2020, at 9:53 AM, juanmigueltiscar @.***> wrote:  Comparing it to to_pickle isn't a worthwhile endeavor as pickle is an entirely different format / protocol. As mentioned above, JSON should have unique keys, though it isn't explicitly forbidden. Ok, I get you point of view. If you think that it is okey I will close the ticket. My concern passed when I changed the way I stored my DataFrames. Everything started to work wrong, until I discovered this "problem". — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

Okey, you have convinced me. As @erfannariman also suggested, at least a warning could be a good idea.

[dshemetov]

Don't mean to necro and maybe this is a separate issue, but the issue I'm having seems more general than the title here suggests. The index is ignored period:

import pandas as pd
# %%
df = pd.DataFrame({"a": [1, 2, 3, 4, 5, 6], "b": [1, 2, 3.0, None, np.nan, pd.NA]}).set_index("a")
df.to_json(orient="records")
# '[{"b":1},{"b":2},{"b":3.0},{"b":null},{"b":null},{"b":null}]'
# %%
df = pd.DataFrame({"a": [1, 2, 3, 4, 5, 6], "b": [1, 2, 3.0, None, np.nan, pd.NA]}).set_index("a")
df.to_json(orient="records", index=True)
# '[{"b":1},{"b":2},{"b":3.0},{"b":null},{"b":null},{"b":null}]'
# %%
df = pd.DataFrame({"b": [1, 2, 3.0, None, np.nan, pd.NA]}, index=range(5, 11))
df.to_json(orient="records", index=True)
# '[{"b":1},{"b":2},{"b":3.0},{"b":null},{"b":null},{"b":null}]'

My workaround this is df.reset_index(), but that creates unnecessary work in an application I need to be performant.

EDIT: Looks like my comment is a duplicate of #25513.

EDIT 2: Found elsewhere, but df.reset_index(inplace=True) benchmarked faster on a few quick tests.

pd.show_versions()

Details

INSTALLED VERSIONS

commit : 91111fd
python : 3.10.5.final.0
python-bits : 64
OS : Linux
OS-release : 5.15.0-53-generic
Version : #59~20.04.1-Ubuntu SMP Thu Oct 20 15:10:22 UTC 2022
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.5.1
numpy : 1.23.4
pytz : 2022.6
dateutil : 2.8.2
setuptools : 58.1.0
pip : 22.0.4
Cython : None
pytest : 7.2.0
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.6.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.6.2
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.9.3
snappy : None
sqlalchemy : 1.4.44
tables : None
tabulate : None
xarray : None
xlrd : 2.0.1
xlwt : None
zstandard : None
tzdata : None

[WillAyd]

@dshemetov the orient="records" keyword argument only serializes the columns and values by definition, so the index argument is ignored. We would accept a PR to raise if both orient="records" and index are passed - would you be interested in contributing that? If not can you open a separate issue so the community can find and contribute? Thanks!

[dshemetov]

Sure, I'll need a little time to get to it.

[ThomasProctor]

@mroeschke Pretty sure that #52143 fixed a completely separate issue. It is still the case that the index is silently not written when the index is duplicated, and there really should be at least a warning, if not an error as it is in #52143.
The OP's example code, df.to_json(orient='table', index=True), will not raise an error in the case of #52143, (orient='table' isn't even mentioned)