CODEWITHSUNDEEP
pythonexcelpandas
Dong Gyu Lee
Dong Gyu Lee

Reputation: 61

Python Pandas Vlookup

I am trying to merge two excel files by using Vlookup function from excel in Python.

based on my code, the result would be:

col1_x | col2_x | col3_x | col4_y | col5_y | col6_y 
   1        2        3       4        5        6
   7        8        9       10       11       12

My code :

df1 = pd.read_excel("dropped_file.xlsx")
df2 = pd.read_excel("original.xlsx")

result = pd.merge(df1, df2, on = ['col1', 'col3', 'col4'], how='left')
result.to_excel("result.xlsx", index=False)

Anyone have idea to drop out _x and _y at behind of column names ?

Upvotes: 2

Views: 239

Answers (1)

jezrael
jezrael

Reputation: 862601

Reason for _x and _y is after merge are duplicated columns names. So for avoid col1, col1, col2, col2 columns is added _x, _y so ouput is col1_x, col1_y, col2_x, col2_y.

If need remove _x, _y but ouput will be duplicated columns use Series.str.replace:

df.columns = df.columns.str.replace('_x|_y','', regex=True)
print (df)
   col1  col2  col3  col4  col5  col6
0     1     2     3     4     5     6
1     7     8     9    10    11    12

Upvotes: 3

Related Questions