Pandas join issue columns overlap but no suffix specified

Question

I have following 2 data frames   df a         mukey  DI  PI 0   100000  35  14 1  1000005  44  14 2  1000006  44  14 3  1000007  43  13 4  1000008  43  13  df b        mukey  niccdcd 0  190236        4 1  190237        6 2  190238        7 3  190239        4 4  190240        7   When I try to join these 2 dataframes   join df   df a join df b on  mukey  how  left     I get the error       ValueError  columns overlap but no suffix specified  Index  u mukey    dtype  object     Why is this so  The dataframes do have common  mukey  values

User · Accepted Answer

Your error on the snippet of data you posted is a little cryptic, in that because there are no common values, the join operation fails because the values don't overlap it requires you to supply a suffix for the left and right hand side:

In [173]:

df_a.join(df_b, on='mukey', how='left', lsuffix='_left', rsuffix='_right')
Out[173]:
       mukey_left  DI  PI  mukey_right  niccdcd
index                                          
0          100000  35  14          NaN      NaN
1         1000005  44  14          NaN      NaN
2         1000006  44  14          NaN      NaN
3         1000007  43  13          NaN      NaN
4         1000008  43  13          NaN      NaN

merge works because it doesn't have this restriction:

In [176]:

df_a.merge(df_b, on='mukey', how='left')
Out[176]:
     mukey  DI  PI  niccdcd
0   100000  35  14      NaN
1  1000005  44  14      NaN
2  1000006  44  14      NaN
3  1000007  43  13      NaN
4  1000008  43  13      NaN

User · Answer

This error indicates that the two tables have the 1 or more column names that have the same column name  The error message translates to   I can see the same column in both tables but you haven t told me to rename either before bringing one of them in   You either want to delete one of the columns before bringing it in from the other on using del df  column name    or use lsuffix to re-write the original column  or rsuffix to rename the one that is being brought it   df a join df b  on  mukey   how  left   lsuffix   left   rsuffix   right

User · Answer

The  join   function is using the index of the passed as argument dataset  so you should use set index or use  merge function instead   Please find the two examples that should work in your case   join df   LS sgo join MSU pi set index  mukey    on  mukey   how  left     or  join df   df a merge df b  on  mukey   how  left

User · Answer

The error indicates that the two tables have the 1 or more column names that have the same column name  Anyone with the same error who doesn t want to provide a suffix can rename the columns instead  Also make sure the index of both DataFrames match in type and value if you don t want to provide the on  mukey  setting    rename example df a   df a rename columns   a old    a new    a2 old    a2 new      set the index df a   df a set index   mukus    df b   df b set index   mukus     df a join df b

User · Answer

Mainly join is used exclusively to join based on the index not on the attribute names so change the attributes names in two different dataframes then try to join they will be joined else this error is raised

[python] Pandas join issue: columns overlap but no suffix specified

Examples related to python

Examples related to join

Examples related to pandas