Python Pandas - Find difference between two data frames

Question

I have two data frames df1 and df2  where df2 is a subset of df1  How do I get a new data frame  df3  which is the difference between the two data frames   In other word  a data frame that has all the rows columns in df1 that are not in df2

User · Answer

Accepted answer Method 1 will not work for data frames with NaNs inside, as pd.np.nan != pd.np.nan. I am not sure if this is the best way, but it can be avoided by

df1[~df1.astype(str).apply(tuple, 1).isin(df2.astype(str).apply(tuple, 1))]

It's slower, because it needs to cast data to string, but thanks to this casting pd.np.nan == pd.np.nan.

Let's go trough the code. First we cast values to string, and apply tuple function to each row.

df1.astype(str).apply(tuple, 1)
df2.astype(str).apply(tuple, 1)

Thanks to that, we get pd.Series object with list of tuples. Each tuple contains whole row from df1/df2. Then we apply isin method on df1 to check if each tuple "is in" df2. The result is pd.Series with bool values. True if tuple from df1 is in df2. In the end, we negate results with ~ sign, and applying filter on df1. Long story short, we get only those rows from df1 that are not in df2.

To make it more readable, we may write it as:

df1_str_tuples = df1.astype(str).apply(tuple, 1)
df2_str_tuples = df2.astype(str).apply(tuple, 1)
df1_values_in_df2_filter = df1_str_tuples.isin(df2_str_tuples)
df1_values_not_in_df2 = df1[~df1_values_in_df2_filter]

User · Answer

As mentioned here that   df1  df1 apply tuple 1  isin df2 apply tuple 1      is correct solution but it will produce wrong output if  df1 pd DataFrame   A   1   B   2    df2 pd DataFrame   A   1 2 3 3   B   2 3 4 4      In that case above solution will give Empty DataFrame  instead you should use concat method after removing duplicates from each datframe   Use concate with drop duplicates  df1 df1 drop duplicates keep  first    df2 df2 drop duplicates keep  first    pd concat  df1 df2   drop duplicates keep False

User · Answer

Perhaps a simpler one-liner  with identical or different column names  Worked even when df2  Name2   contained duplicate values   newDf   df1 set index  Name1               drop df2  Name2    errors  ignore               reset index drop False

User · Answer

edit2  I figured out a new solution without the need of setting index newdf pd concat  df1 df2   drop duplicates keep False   Okay i found the answer of highest vote already contain what I have figured out  Yes  we can only use this code on condition that there are no duplicates in each two dfs   I have a tricky method  First we set    Name    as the index of two dataframe given by the question  Since we have same    Name    in two dfs  we can just drop the    smaller    df   s index from the    bigger    df  Here is the code  df1 set index  Name  inplace True  df2 set index  Name  inplace True  newdf df1 drop df2 index

User · Answer

In addition to accepted answer  I would like to propose one more wider solution that can find a 2D set difference of two dataframes with any index columns  they might not coincide for both datarames   Also method allows to setup tolerance for float elements for dataframe comparison  it uses np isclose    import numpy as np import pandas as pd  def get dataframe setdiff2d df new  pd DataFrame                               df old  pd DataFrame                               rtol 1e-03  atol 1e-05  - gt  pd DataFrame         Returns set difference of two pandas DataFrames         union index   np union1d df new index  df old index      union columns   np union1d df new columns  df old columns       new   df new reindex index union index  columns union columns      old   df old reindex index union index  columns union columns       mask diff    np isclose new  old  rtol  atol       df bool   pd DataFrame mask diff  union index  union columns       df diff   pd concat  new df bool  stack                             old df bool  stack     axis 1       df diff columns     New    Old        return df diff   Example   In  1   df1   pd DataFrame   A   2 1 2   C   2 1 2    df2   pd DataFrame   A   1 1   B   1 1     print  df1  n   df1    n    print  df2  n   df2    n    diff   get dataframe setdiff2d df1  df2   print  diff  n   diff    n     Out  1   df1     A  C 0  2  2 1  1  1 2  2  2   df2     A  B 0  1  1 1  1  1   diff       New  Old 0 A  2 0  1 0   B  NaN  1 0   C  2 0  NaN 1 B  NaN  1 0   C  1 0  NaN 2 A  2 0  NaN   C  2 0  NaN

User · Answer

By using drop duplicates  pd concat  df1 df2   drop duplicates keep False      Update        Above method only working for those dataframes they do not have duplicate itself  For example   df1 pd DataFrame   A   1 2 3 3   B   2 3 4 4    df2 pd DataFrame   A   1   B   2      It will output like below   which is wrong      Wrong Output     pd concat  df1  df2   drop duplicates keep False  Out 655       A  B 1  2  3      Correct Output   Out 656       A  B 1  2  3 2  3  4 3  3  4        How to achieve that    Method 1  Using isin with tuple  df1  df1 apply tuple 1  isin df2 apply tuple 1    Out 657       A  B 1  2  3 2  3  4 3  3  4   Method 2  merge with indicator   df1 merge df2 indicator   True  how  left   loc lambda x   x   merge     both   Out 421       A  B      merge 1  2  3  left only 2  3  4  left only 3  3  4  left only

User · Answer

A slight variation of the nice  liangli s solution that does not require to change the index of existing dataframes   newdf   df1 drop df1 join df2 set index  Name   index

User · Answer

Using the lambda function you can filter the rows with  merge value    left only    to get all the rows in df1 which are missing from df2 df3   df1 merge df2  how    outer   indicator True  loc lambda x  x   merge     left only   df

User · Answer

import pandas as pd   given df1   pd DataFrame   Name    John   Mike   Smith   Wale   Marry   Tom   Menda   Bolt   Yuswa          Age   23 45 12 34 27 44 28 39 40    df2   pd DataFrame   Name    John   Smith   Wale   Tom   Menda   Yuswa          Age   23 12 34 44 28 40       find elements in df1 that are not in df2 df 1notin2   df1   df1  Name   isin df2  Name     amp  df1  Age   isin df2  Age      reset index drop True     output  print  df1 n   df1  print  df2 n   df2  print  df 1notin2 n   df 1notin2     df1       Age   Name   0   23   John   1   45   Mike   2   12  Smith   3   34   Wale   4   27  Marry   5   44    Tom   6   28  Menda   7   39   Bolt   8   40  Yuswa   df2       Age   Name   0   23   John   1   12  Smith   2   34   Wale   3   44    Tom   4   28  Menda   5   40  Yuswa   df 1notin2       Age   Name   0   45   Mike   1   27  Marry   2   39   Bolt

User · Answer

For rows  try this  where Name is the joint index column  can be a list for multiple common columns  or specify left on and right on    m   df1 merge df2  on  Name   how  outer   suffixes            indicator True    The indicator True setting is useful as it adds a column called  merge  with all changes between df1 and df2  categorized into 3 possible kinds   left only    right only  or  both    For columns  try this   set df1 columns  symmetric difference df2 columns

User · Answer

Finding difference by index  Assuming df1 is a subset of df2 and the indexes are carried forward when subsetting  df1 loc set df1 index  symmetric difference set df2 index    dropna      Example  df1   pd DataFrame   gender  np random choice   m   f   size 5    subject  np random choice   bio   phy   chem   size 5    index    1 2 3 4 5    df2    df1 loc  1 3 5    df1   gender subject 1      f     bio 2      m    chem 3      f     phy 4      m     bio 5      f     bio  df2    gender subject 1      f     bio 3      f     phy 5      f     bio  df3   df1 loc set df1 index  symmetric difference set df2 index    dropna    df3    gender subject 2      m    chem 4      m     bio

[python] Python Pandas - Find difference between two data frames

Examples related to python

Examples related to pandas

Examples related to dataframe