CODEWITHSUNDEEP
pythonpandasdatabasedataframemerge
Learner
Learner

Reputation: 672

Pandas: compare how to compare two columns in different sheets and return matched value

I have two dataframes with multiple columns.

I would like to compare df1['id'] and df2['id'] and return a new df with another column that have the match value. example:

   df1
   **id** **Name**
1    1    Paul
2    2    Jean
3    3    Alicia
4    4    Jennifer

df2
   **id** **Name**
1    1    Paul
2    6    Jean
3    3    Alicia
4    7    Jennifer 

output
       **id** **Name** *correct_id*
    1    1     Paul        1
    2    2     Jean        N/A
    3    3     Alicia       3
    4    4     Jennifer    N/A

Note- the length of the two columns I want to match is not the same.

Upvotes: 0

Views: 551

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195528

Try:

df1["correct_id"] = (df1["id"].isin(df2["id"]) * df1["id"]).replace(0, "N/A")
print(df1)

Prints:

   id      Name correct_id
0   1      Paul          1
1   2      Jean        N/A
2   3    Alicia          3
3   4  Jennifer        N/A

Upvotes: 2

Related Questions