Python – Join two DataFrames on one key column / ERROR: ‘columns overlap but no suffix specified’

join;pandaspythonsqlsyntax-error

I have two tables: sales table & product table and these two tables share the 'PART NUMBER' column.
The 'PART NUMBER' column in the sales table is not unique, but it is unique in the product table. (see image below of a snapshot of the sales table & product table)

enter image description here

I was trying to add the equivalent 'Description' to each 'PART NUMBER' on the sales table, and I followed the examples from the pandas website
my code

sales.join(part_table, on='PART NUMBER')

But I got this error:

ValueError: columns overlap but no suffix specified: Index([u'PART NUMBER'], dtype='object')

Can someone explain what this error means and how to solve it?

Many thanks!

Best Answer

I think you want to do a merge rather than a join:

sales.merge(part_table)

Here's an example dataframe:

In [11]: dfa = pd.DataFrame([[1, 2], [3, 4]], columns=['A', 'B'])

In [12]: dfb = pd.DataFrame([[1, 'a'], [3, 'b'], [3, 'c']], columns=['A', 'C'])

In [13]: dfa.join(dfb, on=['A'])
ValueError: columns overlap but no suffix specified: Index([u'A'], dtype='object')

In [14]: dfa.merge(dfb)
Out[14]:
   A  B  C
0  1  2  a
1  3  4  b
2  3  4  c

It's unclear from the docs if this is intentational (I thought that on would be used as the column) but following the exceptions message if you add suffixs we can see what's going on:

In [21]: dfb.join(dfa, on=['A'], lsuffix='_a', rsuffix='_b')
Out[21]:
   A_a  C  A_b   B
0    1  a    3   4
1    3  b  NaN NaN
2    3  c  NaN NaN

In [22]: dfb.join(dfa, lsuffix='_a', rsuffix='_b')
Out[22]:
   A_a  C  A_b   B
0    1  a    1   2
1    3  b    3   4
2    3  c  NaN NaN

It's ignoring the on kwarg and just doing the join.

Related Solutions

SQL select join: is it possible to prefix all columns as ‘prefix.*’

It seems the answer to your question is no, however one hack you can use is to assign a dummy column to separate each new table. This works especially well if you're looping through a result set for a list of columns in a scripting language such as Python or PHP.

SELECT '' as table1_dummy, table1.*, '' as table2_dummy, table2.*, '' as table3_dummy, table3.* FROM table1
JOIN table2 ON table2.table1id = table1.id
JOIN table3 ON table3.table1id = table1.id

I realize this doesn't answer your question exactly, but if you're a coder this is a great way to separate tables with duplicate column names. Hope this helps somebody.

Sql – What’s the best practice for primary keys in tables?

I follow a few rules:

Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache you will use.
Primary keys should never change. Updating a primary key should always be out of the question. This is because it is most likely to be used in multiple indexes and used as a foreign key. Updating a single primary key could cause of ripple effect of changes.
Do NOT use "your problem primary key" as your logic model primary key. For example passport number, social security number, or employee contract number as these "natural keys" can change in real world situations. Make sure to add UNIQUE constraints for these where necessary to enforce consistency.

On surrogate vs natural key, I refer to the rules above. If the natural key is small and will never change it can be used as a primary key. If the natural key is large or likely to change I use surrogate keys. If there is no primary key I still make a surrogate key because experience shows you will always add tables to your schema and wish you'd put a primary key in place.

Best Answer

Related Solutions

SQL select join: is it possible to prefix all columns as ‘prefix.*’

Sql – What’s the best practice for primary keys in tables?

Related Topic