Pandas
keyboard_arrow_down 655 guides
chevron_leftSelecting Data Cookbook
Accessing a single value of a DataFrameAccessing columns of a DataFrame using column labelsAccessing columns of a DataFrame using integer indicesAccessing rows of a DataFrame using integer indicesAccessing rows of a DataFrame using row labelsAccessing the first n rowsAccessing the last n rowsAccessing values of a multi-index DataFrameAdding prefix to column labelsAdding suffix to column labelsConverting two columns into a dictionaryExcluding columns based on typeGetting earliest or latest date from DataFrameGetting every nth rowGetting indexes of rows matching conditionsGetting shortest and longest stringsSelecting a single column as a DataFrameSelecting columns of a DataFrame using regexSelecting last column of DataFrameSelecting rows based on a conditionExtracting values of a DataFrame as a Numpy arrayGetting a list of all the column labelsGetting all columns except oneGetting all duplicate rowsGetting all numeric columns of a DataFrameGetting all unique values of columnsGetting column label of max value in each rowGetting column label of minimum value in each rowGetting columns by data typeGetting columns using integer indexGetting first row value of a columnGetting index of Series where value is TrueGetting integer index of a column using its column labelGetting integer index of rows based on column valuesGetting multiple columnsGetting row with largest index valueGetting row with smallest index valueGetting rows based on multiple column valuesGetting rows except someGetting rows from a DataFrame based on column valuesGetting rows that are not in other DataFrameGetting rows using OR statementGetting rows where column values are of specific lengthGetting rows where value is between two valuesGetting rows where values do not contain substringGetting the column labels of a DataFrameGetting the first columnGetting the index of a DataFrameGetting the length of the longest string in a columnGetting the longest string in a columnGetting the row with the maximum column valueGetting the row with the minimum column valueGetting the shape of a DataFrameGetting number of columns of a DataFrameGetting the total number of rows of a DataFrameGetting the total number of values in a DataFrameMaking column labels all lowercaseMaking column labels all uppercaseRandomly select rows based on a conditionRandomly selecting n columns from a DataFrameRandomly selecting n rows from a DataFrameReassigning column valuesRetrieving DataFrame column values as a NumPy arraySelecting columns that do not begin with certain prefixSelecting columns with certain prefixSelecting n rows with the smallest values for a columnSelecting rows based on datesSelecting rows from a DataFrame whose column values are contained in a listSelecting rows from a DataFrame whose column values are NOT contained in a listSelecting rows from a DataFrame whose column values contain a substringSelecting rows starting with substringSelecting top n rows with the largest values for a columnSplitting DataFrame based on column values
check_circle
Mark as learned thumb_up
2
thumb_down
1
chat_bubble_outline
0
Comment auto_stories Bi-column layout
settings
Getting rows that are not in other DataFrame in Pandas
schedule Aug 12, 2023
Last updated local_offer
Tags Python●Pandas
tocTable of Contents
expand_more Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!
Start your free 7-days trial now!
Consider the following DataFrames:
df1 = pd.DataFrame({"A":[3,4],"B":[5,6]})df2 = pd.DataFrame({"C":[3,8],"D":[5,9]})
A B | C D0 3 5 | 0 3 51 4 6 | 1 8 9
Here, the first row of each DataFrame has the same entries.
Solution
To fetch all the rows in df1 that do not exist in df2:
df_merged = df1.merge(df2, how="left", left_on=["A","B"], right_on=["C","D"], indicator=True)df_merged.query("_merge == 'left_only'")[["A","B"]]
A B1 4 6
Explanation
Here, we are are first performing a left join on all columns of df1 and df2:
df_merged = df1.merge(df2, how="left", left_on=["A","B"], right_on=["C","D"], indicator=True)df_merged
A B C D _merge0 3 5 3.0 5.0 both1 4 6 NaN NaN left_only
The indicate=True means that we want to append the _merge column, which tells us the type of join performed; both indicates that a match was found, whereas left_only means that no match was found.
We then use the query(~) method to select rows where _merge=left_only:
df_merged.query("_merge == 'left_only'")
A B C D _merge1 4 6 NaN NaN left_only
Since we are interested in just the original columns of df1, we simply extract them using [] syntax:
df_merged.query("_merge == 'left_only'")[["A","B"]]
A B1 4 6
Generalising the solution
As explained above, the solution to get rows that are not in another DataFrame is as follows:
df_merged = df1.merge(df2, how="left", left_on=["A","B"], right_on=["C","D"], indicator=True)df_merged.query("_merge == 'left_only'")[["A","B"]]
A B1 4 6
Instead of explicitly specifying the column labels (e.g. ["A","B"]), you can pass in a list of columns like so:
df_merged = df1.merge(df2, how="left", left_on=df1.columns.tolist(), right_on=df2.columns.tolist(), indicator=True)df_merged.query("_merge == 'left_only'")[df1.columns]
A B1 4 6
Related
Pandas DataFrame | merge method
Merges the source DataFrame with another DataFrame or a named Series.
Pandas DataFrame | query method
Filters rows according to the provided boolean expression.
Published by Isshin Inada
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
Ask a question or leave a feedback...
thumb_up
2
thumb_down
1
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!
