Joining on a column, not index, gives erroneous result

[Dmitrii-I]

Code Sample, a copy-pastable example if possible

import pandas as pd
d1 = pd.DataFrame({'a': [1, 2], 'b': [11, 22]})
d2 = pd.DataFrame({'a': [1, 2], 'c': ['aa', 'bb']})
d1.join(d2, on=['a'], how='outer', lsuffix='.d1', rsuffix='.d2')

Actual output

     a  a.d1     b  a.d2    c
0  1.0   1.0  11.0   2.0   bb
1  2.0   2.0  22.0   NaN  NaN
1  0.0   NaN   NaN   1.0   aa

Expected Output

   a   b   c
0  1  11  aa
1  2  22  bb

output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.4.3.final.0
python-bits: 64
OS: Linux
OS-release: 3.19.0-65-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.1
nose: None
pip: 1.5.4
setuptools: 3.3
Cython: None
numpy: 1.10.1
scipy: 0.17.1
statsmodels: 0.6.1
xarray: None
IPython: 4.0.0
sphinx: None
patsy: 0.4.1
dateutil: 2.5.2
pytz: 2016.3
blosc: None
bottleneck: None
tables: 3.2.2
numexpr: 2.4.6
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.999
httplib2: 0.9.2
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: 2.6.1 (dt dec pq3 ext lo64)
jinja2: 2.8
boto: None
pandas_datareader: None

[sinhrks]

yeah looks strange. merge should work though...

pd.merge(d1, d2, on='a', how='outer')
#    a   b   c
# 0  1  11  aa
# 1  2  22  bb

[jorisvandenbossche]

There is also a problem with the overlapping columns detection:

In [11]: d1.join(d2, on=['a'])
...
ValueError: columns overlap but no suffix specified: Index([u'a'], dtype='object')

as this should of course not be raised if this is the column you want to merge on.

[ivannz]

how="inner" also gives unexpected results:

d1.join(d1, on=['a'], how='inner', lsuffix='.d1', rsuffix='.d2')
#    a  a.d1  b.d1  a.d2  b.d2
# 0  1     1    11     2    22

d2.join(d1, on=['a'], how='inner', lsuffix='.d1', rsuffix='.d2')
#    a  a.d1   c  a.d2   b
# 0  1     1  aa     2  22

[jreback]

you just need a simple concat or merge here.

In [4]: pd.concat([d1,d2],axis=1)
Out[4]: 
   a   b  a   c
0  1  11  1  aa
1  2  22  2  bb

In [5]: pd.merge(d1,d2, on=['a'], how='outer')
   ...: 
Out[5]: 
   a   b   c
0  1  11  aa
1  2  22  bb

Here is what on does.

on : column name, tuple/list of column names, or array-like
    Column(s) in the caller to join on the index in other,
    otherwise joins index-on-index. If multiples
    columns given, the passed DataFrame must have a MultiIndex. Can
    pass an array as the join key if not already contained in the
    calling DataFrame. Like an Excel VLOOKUP operation

So this has to do with the overlapping detection. I think this should just raise as its fundamentally a user error here. This is not a join/merge rather a concat; here the difference matters.