19

Let's say that I have two tables: people_all and people_usa, both with the same structure and therefore the same primary key.

How can I get a table of the people not in the USA? In SQL I'd do something like:

select a.*
from people_all a

left outer join people_usa u
on a.id = u.id

where u.id is null

What would be the Python equivalent? I cannot think of a way to translate this where statement into Pandas syntax.

The only way I can think of is to add an arbitrary field to people_usa (e.g. people_usa['dummy']=1), do a left join, then take only the records where 'dummy' is nan, then delete the dummy field - which seems a bit convoluted.

Improve this question
3
  • 1
    wouldnt a people_all_set.difference(people_usa_set) do the trick? Commented Sep 18, 2015 at 12:19
  • Does this work only on the index of the dataframe? I'd like the option to specify the field(s) to apply this to Commented Sep 18, 2015 at 12:22
  • @LawrenceBenson difference operates on indexes so it would need to be people_all_set.index.difference(people_usa_set.index) pandas.pydata.org/docs/reference/api/… Commented Jan 28, 2023 at 15:01

3 Answers 3

Reset to default
29

use isin and negate the boolean mask:

people_usa[~people_usa['ID'].isin(people_all ['ID'])]

Example:

In [364]:
people_all = pd.DataFrame({ 'ID' : np.arange(5)})
people_usa = pd.DataFrame({ 'ID' : [3,4,6,7,100]})
people_usa[~people_usa['ID'].isin(people_all['ID'])]

Out[364]:
    ID
2    6
3    7
4  100

so 3 and 4 are removed from the result, the boolean mask looks like this:

In [366]:
people_usa['ID'].isin(people_all['ID'])

Out[366]:
0     True
1     True
2    False
3    False
4    False
Name: ID, dtype: bool

using ~ inverts the mask

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

6 Comments

Is there any easy way to do this if you have multiple columns to check/join?
You could do a merge and then eliminate the rows that exist in the merged df otherwise you'd have to build a boolean condition for all the columns you want to compare but presumably when checking the multiple columns you're stating that it's unique for those columns, correct? For instance it's not a match if say col1 and col2 match but col3 does not
Yes merge is what I have been doing but it feels like a hassle. ...I mean something like select * from A where not exists (select * from B where A.col1 = B.col1 and A.col2 = B.col2) I feel like this statement is impossible to do elegantly in pandas :(
@Pekka: You could use mask = people_all[primary_key].isin(people_usa[primary_key]).all(axis=1). Then select the nonusa people with people_nonusa = people_all.loc[~mask].
@Pekka I agree with unutbu in that you don't necessarily have to do everything in a one-liner and can split the statement to make it more readable
|
7

Here is another similar to SQL Pandas method: .query():

people_all.query('ID not in @people_usa.ID')

or using NumPy's in1d() method:

people_all.[~np.in1d(people_all, people_usa)]

NOTE: for those who have experience with SQL it might be worth to read Pandas comparison with SQL

Improve this answer

1 Comment

Your second method returns errors for me, unfortunately. For one, the syntax is wrong since it has dot notation and square brackets. Removing the dot, it complains that the item has a wrong length.
-2

I would combine (by stacking) the data frames and then perform a .drop_duplicates method. Documentation found here:

http://pandas.pydata.org/pandas-docs/version/0.17.1/generated/pandas.DataFrame.drop_duplicates.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.