Pandas compare two columns of different dataframes and copy value of a third column if there is a ma-CodePudding

I have two different dataframes that I have exported from different .xlsx documents

df1 contains the headers: Contact Name, Request ID, Contact E-mail.
df2 contains the headers: Request ID and some other headers that I need.

I'd like to check if the value of df2['Request ID'] matches with each of df1 ['Request ID] column and copy the df1['Contact E-mail'] value of that match to a new column in df2.

I have tried many things but without success, the last thing that I tried was:

 if df1.loc['Request ID'] == df2.loc['Request ID']:
        df2['Email'] = df1['Contact Email']
 else:
        df2['Email'] = ""

However I'm getting error:

KeyError: "None of [Int64Index(
[2014164503, 2014151100, 2014149017, 2014162644, 2014164489,
2014120882, 2014159262, 2013993561, 2014162450, 2014151098,
2014150239, 2014162949, 2014151485, 2014162448, 2014163094,
2014162592, 2014158897, 2014151097, 2014164495, 2014159459,
2014036452, 2014164484, 2014154555, 2014154556, 2014146835,
2014149163, 2014158899, 2014153930, 2014163579, 2014036453,
2014142169, 2014145026, 2014151724, 2014155704, 2014152046,
2014036447, 2014164490, 2014163503, 2014160983, 2014105630,
2014163612, 2014146834, 2014027641, 2014164454],
dtype='int64')] are in the [index]"

CodePudding user response：

What you are trying to achieve sounds like a typical sql join operation. This can be translated to Pandas like the following code:

df1 = df1.rename(columns={"Contact Email": "Email"})
res = df1.merge(df2, on=["Request ID"], how="right")

Pandas merge docs

It would be great if you share an example of your input and the expected output.