Creating a column in one DF that compares another column from other DF in pandas

Question

I have two datasets that I need to see how many names are different in one dataset to another. I used this question for reference and the code I used only found 1 issue is there are others.

Check if value from one dataframe exists in another dataframe

DF1   
Name: 'Adam', 'Ben', 'Chris', 'Dave', 'Emily'

DF2
Manager: 'Adam', 'Beth', 'Jack', 'Grace', 'Mel', 'Emily', 'Chris'

I am trying to find a method that will create a new column indicating when names match or not.

Desired Output:

Adam    1
Ben     0
Chris   1
Dave    0
Emily   1

This is the code used.

df3 = df2.assighn(indf1=df2.Manager.isin(df1.Name).astype(int))

Any suggestions?

DF1

 name         id      position_num       job_title    
Lawrence    00010293      62121         Analytic Supervisor 
Richard     00023723      73213         Lead Data Manger
Elizabeth   00024422      42392         Analytic Director
Nicole      00012123      23423         Data Management Manger 
Timothy     00032112      32134         Business Execution Manager

Mind providing your `DF` and `DF2` as `DF.to_dict()` and `DF2.to_dict()` ? — rafaelc, May 10 '23 at 13:41
I can provide a small sample the datasets are both 50,000 plus rows and 25+ columns — Coding_Nubie, May 10 '23 at 13:47
What is the issue with your current code? You just used it in the wrong direction: `df1.assign(indf2=df1['Name'].isin(df2['Manager']).astype(int))` — mozway, May 10 '23 at 13:52
It wouldn't allow me to paste so I made a quick dataset on the question @rafaelc — Coding_Nubie, May 10 '23 at 14:01
@Coding_Nubie you haven't answered my comment, what if wrong with your current code? AFAIK it works well if you swap the two Series — mozway, May 10 '23 at 14:15
@mozway Sorry, the issue was I was using the wrong column in my data it is fixed now thank you. — Coding_Nubie, May 10 '23 at 14:21

score 0 · Answer 1 · answered May 10 '23 at 13:59

0

You can do the following:

names = df1['Name'].values
manager_name = df2['Manager'].values

matched_names = {name: manager_name.count(name) for name in names}
for name, count in matched_names:
  print("we have {} of {}".format(count, name)

Output will be:

we have 1 of Adam
we have 0 of Ben
we have 1 of Chris
we have 0 of Dave
we have 1 of Emily

Also the matched_names will be like this:

matched_names = {'Adam':1, 'Ben':0, 'Chris':1, 'Dave':0, 'Emily':1}

answered May 10 '23 at 13:59

Arash Mohamadpour

13
4

It should work for you but if you got an error please inform me so I can help. – Arash Mohamadpour May 10 '23 at 14:01
This is a very inefficient solution, if the two inputs were larger this would scale badly. OP already provided the correct approach in the question, just used it incorrectly. – mozway May 10 '23 at 14:02

score 0 · Answer 2 · answered May 10 '23 at 14:06

0

You can use isin but you have to swap df1 and df2:

df3 = df1.assign(indf2=df1.Name.isin(df2.Manager).astype(int))
print(df3)

# Output
    Name  indf2
0   Adam      1
1    Ben      0
2  Chris      1
3   Dave      0
4  Emily      1

answered May 10 '23 at 14:06

Corralien

109,409
8
28
52

Creating a column in one DF that compares another column from other DF in pandas

2 Answers2