How to combine two sets of data frames with different columns-CodePudding

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!

CodePudding user response：

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%

CodePudding user response：

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%