DataFrame.to_dict can result in data loss when indexes are not unique

[TychonautVII]

Code Sample,

df = pd.DataFrame([["A","B","C"],["A","D","E"],["F","G","K"]])
df.set_index(0, inplace=True)
print(df)

---1 2
0
A B C
A D E
F G K

print(df.to_dict(orient="index"))

{'A': {1: 'D', 2: 'E'}, 'F': {1: 'G', 2: 'K'}}

Problem description

The to_dict method silently tosses away data when indexes are not unique. At least in my impression the rest of the API doesn’t mind non-unique index’s much, so I don’t like this behavior. I think that it should either preserve the data, or raise an an error / warning saying that to_dict requires unique indexes.

Expected Output

'''
{'A': [{1: 'B', 2: 'C'},{1: 'D', 2: 'E'}], 'F': {1: 'G', 2: 'K'}}

or at least throw an error saying ".to_dict(orient="index") does not support non-unique indexes"

'''

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.5.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: Intel64 Family 6 Model 158 Stepping 9, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.23.4
pytest: None
pip: 10.0.1
setuptools: 40.0.0
Cython: 0.28.5
numpy: 1.15.0
scipy: None
pyarrow: 0.9.0
xarray: None
IPython: 6.5.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[chris-b1]

Yeah, I think we should raise here, ala #4376 for JSON. Thanks for the report!

In [28]: df.to_json(orient='index')

ValueError: DataFrame index must be unique for orient='index'.

[WillAyd]

dicts in Python do not support duplicate keys so I think we should raise. You can use JSON as an alternate if you need

[WillAyd]

Looks like @chris-b1 beat me to this, but to clarify my seemingly contradictory point about JSON you can do the following:

In [5]: df.to_json(orient='table')
Out[5]: '{"schema": {"fields":[{"name":0,"type":"string"},{"name":1,"type":"string"},{"name":2,"type":"string"}],"pandas_version":"0.20.0"}, "data": [{"0":"A","1":"B","2":"C"},{"0":"A","1":"D","2":"E"},{"0":"F","1":"G","2":"K"}]}'

[PatrickDRusk]

This issue ties in with an issue I reported a week back: #22705, rendering to_dict() an incomplete replacement for to_items().