How to compare two columns in two different pandas dataframes?

Question

I am running a selenium script which captures the office location and employee name and saves to a dataframe.

Employee   Office
John Doe   Building One
Kim Joe    Building One
Harry P   Building two
Harry P   Building Three

CSV
   Employee   Office
    Kim Joe   Building One
    Harry P   Building two
    Harry P   Building Three

my code is below

df2 = df2.append({'Employee': emp.text,'Office': loc}, ignore_index=True)

I am reading the csv file into dataframe df1 and trying to compare the two columns in two dataframes, I tried the below code but it wont work because the indexes might be different

df2[df1.ne(self.df2).any(axis=1)]

What I am trying to do is want to get the employee:office which exist in df2 but not in df1, I am not sure how merge works, tried but didnt get the desired result, also tried using dictionary instead of dataframes but I guess it didnt work because of non unique keys, also open to other ways

Output
John Doe   Building One

Have you looked at Pandas Merging 101? Please also see Pandas: how to ask — ddejohn
– ddejohn, Commented Sep 22, 2021 at 1:51
@Chris this wont work because this code will check employee names only, in my scenario if 'Harry P' 'Building two' is missing from file 2 then it wont be printed because Harry P exist in file. the solution should be checking both the employee name and location — Ronron
– Ronron, Commented Sep 22, 2021 at 2:14
@ddejohn I am finding it hard to understand merges but will give it a try again, thanks — Ronron
– Ronron, Commented Sep 22, 2021 at 2:16

U13-Forward · Accepted Answer · 2021-09-22 02:12:14Z

1

Try using np.isin:

>>> df2[~np.isin(df2, df).all(axis=1)]
   Employee        Office
0  John Doe  Building One
>>>

edited Sep 22, 2021 at 2:12

answered Sep 22, 2021 at 1:54

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ronron Over a year ago

Solution 1 works, solution 2 does not work for me because if 'Harry P' 'Building two' is missing in file 2 it wont be caught because we are looking through the employee names only. The solution must be based of checking employee and location, your first solution works, thanks

U13-Forward Over a year ago

@Ronron Removed the solution 2, please accept and upvote if it works :)

U13-Forward Over a year ago

@Ronron Read stackoverflow.com/help/someone-answers, it's helping the community :)

Collectives™ on Stack Overflow

How to compare two columns in two different pandas dataframes?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related