How to combine two sets of data frames with different columns

Question

Hello I have two sets of data frames

df :

	col1
Label
adidas	26%
Jordan	1.6%
Asics	1.7%

df1 :

	col2
Answer Code Label
adidas	11%
Jordan	16%
Asics	1%

I am trying to create this:

Label	col1	col2
adidas	26%	11%
Jordan	1.6%	16%
Asics	1.7%	1%

I have tried various methods and all produce the same error. For example

df = df.join(df1)

df.merge(df1, left_on='Answer Code Label',right_on='Label')

These are the errors I get :

Cannot join with no level specified and no overlapping names

KeyError: 'Label'

Any help would be appreciated

Thank you!

Please check [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples), otherwise it's unnecessary hard for others to convert your table df and df1 into actual code to cross check the result. — MagnusO_O, Oct 04 '22 at 14:58

score 0 · Answer 1 · answered Oct 04 '22 at 15:10

0

As you want to merge on the indices of the dataframes, you can pass True to left_index and right_index parameters to merge , and the type of merge you are looking for is inner merge:

>>> out=df1.merge(df, left_index=True, right_index=True)
>>> out.index.name=df.index.name  # Optional (If need index name)

OUTPUT:

       col2  col1
Label            
adidas  11%   26%
Jordan  16%  1.6%
Asics    1%  1.7%

answered Oct 04 '22 at 15:10

ThePyGuy

17,779
5
18
45

Hello unfortunately I get the error "cannot join with no level specified and no overlapping names" – Paul Oct 04 '22 at 19:27

score 0 · Accepted Answer · answered Oct 04 '22 at 15:19

Given your DataFrames as

df1 = pd.DataFrame({'Label': {0: 'adidas', 1: 'Jordan', 2: 'Asics'}, 'col1': {0: '26%', 1: '1.6%', 2: '1.7%'}})
df2 = pd.DataFrame({'Answer Code Label': {0: 'adidas', 1: 'Jordan', 2: 'Asics'}, 'col1': {0: '11%', 1: '16%', 2: '1%'}})

You can either use .merge() with right_on and left_on like this

df1.merge(df2, right_on='Answer Code Label', left_on='Label')

Or make sure the columns have the same name and then use the on parameter

df2.columns = ["Label", "col1"]
df1.merge(df2, on='Label')

    Label col1_x col1_y
0  adidas    26%    11%
1  Jordan   1.6%    16%
2   Asics   1.7%     1%

Than you this worked but I needed to reset the index I think it was because i had a multilevel columns. — Paul, Oct 04 '22 at 19:32

How to combine two sets of data frames with different columns

2 Answers2