CODEWITHSUNDEEP
pythonmergepandas
BubbleGuppies
BubbleGuppies

Reputation: 5920

Python Pandas merge only certain columns

Is it possible to only merge some columns? I have a DataFrame df1 with columns x, y, z, and df2 with columns x, a ,b, c, d, e, f, etc.

I want to merge the two DataFrames on x, but I only want to merge columns df2.a, df2.b - not the entire DataFrame.

The result would be a DataFrame with x, y, z, a, b.

I could merge then delete the unwanted columns, but it seems like there is a better method.

Upvotes: 234

Views: 487922

Answers (6)

tonneofash
tonneofash

Reputation: 669

If you want to drop column(s) from the target data frame, but the column(s) are required for the join, you can do the following:

df1 = df1.merge(df2[['a', 'b', 'key1']], how = 'left',
                left_on = 'key2', right_on = 'key1').drop(columns = ['key1'])

The .drop(columns = 'key1') part will prevent 'key1' from being kept in the resulting data frame, despite it being required to join in the first place.

Upvotes: 48

Cornelius Roemer
Cornelius Roemer

Reputation: 7819

Slight extension of the accepted answer for multi-character column names, using inner join by default:

df1 = df1.merge(df2[["Key_Column", "Target_Column1", "Target_Column2"]])

This assumes that Key_Column is the only column both dataframes have in common.

Upvotes: 3

Marco167
Marco167

Reputation: 391

This is to merge selected columns from two tables.

If table_1 contains t1_a,t1_b,t1_c..,id,..t1_z columns, and table_2 contains t2_a, t2_b, t2_c..., id,..t2_z columns, and only t1_a, id, t2_a are required in the final table, then

mergedCSV = table_1[['t1_a','id']].merge(table_2[['t2_a','id']], on = 'id',how = 'left')
# save resulting output file    
mergedCSV.to_csv('output.csv',index = False)

Upvotes: 10

Arthur D. Howland
Arthur D. Howland

Reputation: 4557

You want to use TWO brackets, so if you are doing a VLOOKUP sort of action:

df = pd.merge(df,df2[['Key_Column','Target_Column']],on='Key_Column', how='left')

This will give you everything in the original df + add that one corresponding column in df2 that you want to join.

Upvotes: 295

Terrance DeJesus
Terrance DeJesus

Reputation: 241

You can use .loc to select the specific columns with all rows and then pull that. An example is below:

pandas.merge(dataframe1, dataframe2.iloc[:, [0:5]], how='left', on='key')

In this example, you are merging dataframe1 and dataframe2. You have chosen to do an outer left join on 'key'. However, for dataframe2 you have specified .iloc which allows you to specific the rows and columns you want in a numerical format. Using :, your selecting all rows, but [0:5] selects the first 5 columns. You could use .loc to specify by name, but if your dealing with long column names, then .iloc may be better.

Upvotes: 12

Andy Hayden
Andy Hayden

Reputation: 375375

You could merge the sub-DataFrame (with just those columns):

df2[list('xab')]  # df2 but only with columns x, a, and b

df1.merge(df2[list('xab')])

Upvotes: 109

Related Questions