python - Compare two spreadsheets and extract the values -

i have 2 spreadsheets different number of rows , columns.

what compare both , extract values of , b a1.xlsx match column names of a2.xlsx , copy values columns c , d in a2.xlsx. know how in excel index-match not using python's pandas.

spreadsheet 1 (a1.xlsx)

index     b   c 0   s   0.2 new york 1   d   1   vienna 2   g   2   london 3   c   3   tokyo 4   r   2   paris 5   d   1   berlin 6     8   madrid 7   f   10  seattle 

spreadsheet 2 (a2.xlsx)

index     b   c   d 0   dublin  34  x   x 1   seoul   36  x   x 2   london  12  x   x 3   berlin  4   x   x 4   tokyo   6   x   x 5   seatte  22  x   x 

assuming spreadsheet1 loaded pandas df , spreadsheet 2 loaded df1 can assign values result of merge:

in [20]:  df1[['c','d']] = df1.merge(df, left_on='a', right_on='c',how='left')[['a_y','b_y']] df1 out[20]:                b    c   d index                       0       dublin  34  nan nan 1        seoul  36  nan nan 2       london  12    g   2 3       berlin   4    d   1 4        tokyo   6    c   3 5      seattle  22    f  10 

just explain little when merge perform left merge , produce clash of columns have column names clash:

in [24]:  df1.merge(df, left_on='a', right_on='c',how='left') out[24]:        a_x  b_x c_x  d  a_y  b_y      c_y 0   dublin   34   x  x  nan  nan      nan 1    seoul   36   x  x  nan  nan      nan 2   london   12   x  x    g    2   london 3   berlin    4   x  x    d    1   berlin 4    tokyo    6   x  x    c    3    tokyo 5  seattle   22   x  x    f   10  seattle 

we interested in columns a_y , b_y, perform left merge because if did default merge type 'inner' not align original df, i.e. values shifted top of df.