Non-silently handle duplicate column names

[twolodzko]

Code Sample, a copy-pastable example if possible

>>> df = pd.DataFrame([[1, 2, 3]], columns=["x", "y", "x"])
>>> df['x']
   x  x
0  1  3
>>> df.loc[:, 'x']
   x  x
0  1  3
>>> df['x'].between(1,2)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "[...]/python3.7/site-packages/pandas/core/generic.py", line 5179, in __getattr__
    return object.__getattribute__(self, name)
AttributeError: 'DataFrame' object has no attribute 'between'

Hopefully:

>>> df.join(pd.DataFrame([[4]], columns=["x"]))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "[...]/python3.7/site-packages/pandas/core/frame.py", line 7246, in join
    other, on=on, how=how, lsuffix=lsuffix, rsuffix=rsuffix, sort=sort
  File "[...]/python3.7/site-packages/pandas/core/frame.py", line 7269, in _join_compat
    sort=sort,
  File "[...]/python3.7/site-packages/pandas/core/reshape/merge.py", line 83, in merge
    return op.get_result()
  File "[...]/python3.7/site-packages/pandas/core/reshape/merge.py", line 648, in get_result
    ldata.items, lsuf, rdata.items, rsuf
  File "[...]/python3.7/site-packages/pandas/core/reshape/merge.py", line 2011, in _items_overlap_with_suffix
    "{rename}".format(rename=to_rename)
ValueError: columns overlap but no suffix specified: Index(['x', 'x'], dtype='object')

But...

>>> pd.concat([df, pd.DataFrame([[4]], columns=["x"])], axis=1)
   x  y  x  x
0  1  2  3  4

Problem description

At this moment, Pandas allows duplicate column names. This can potentially lead to problems, when user expects to receive pd.Series when asking for a single column, if they are not aware that the column names are duplicated.

This is especially problematic with automatic processing, where column duplication can easily appear as a consequence of some bug.

Expected Output

Better solution: at the time of creating the pd.DataFrame or adding new column to pd.DataFrame, when the column name is duplicated, the code fails with an error message informing about the duplicated name. This is easy to implement and failing fast is usually a good practice.

Worse solutions:

1. Similar as above, but instead of crashing, the warning message is displayed both: (a) when initializing duplicated column, and (b) during any operation that calls the duplicated column. In such case user would be aware that the behaviour of Pandas may give different results then if he was calling by the non-duplicated column name.
2. Silently add suffixes to duplicated column names when they appear, as for example, R does. Best to be done with some warning or info message.

Examples:

• Python's DataTable by default silently overwrites the duplicated column (bad):

>>> dt.Frame({"x" : [1], "y": [2], "x": [3]})
     x   y
--  --  --
 0   3   2

• R by default renames the duplicated column (little better):

> data.frame(x=1, y=2, x=3)
  x y x.1
1 1 2   3

• R's dplyr fails fast with error:

> tibble(x=1, y=2, x=3)
Error: Column name `x` must not be duplicated.
Use .name_repair to specify repair.
Call `rlang::last_error()` to see a backtrace

Output of pd.show_versions()

INSTALLED VERSIONS

commit : None
python : 3.7.3.final.0
python-bits : 64
OS : Linux
OS-release : 5.0.0-25-generic
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 0.25.1
numpy : 1.17.1
pytz : 2019.2
dateutil : 2.8.0
pip : 19.2.3
setuptools : 41.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[TomAugspurger]

Looks like a duplicate of #27108.

There's quite a bit of design to be done there (most prominently, whether this is a property of the Index or the DataFrame/Series).