我有两个数据帧df1和df2,其中df2是df1的子集。我如何得到一个新的数据帧(df3),这是两个数据帧之间的差异?

换句话说,一个在df1中所有的行/列都不在df2中的数据帧?


当前回答

pandas DataFrame.compare中有一种新的方法,即比较2个不同的dataframe,并返回数据记录中每列中变化的值。

例子

第一个Dataframe

Id Customer Status      Date
1      ABC   Good  Mar 2023
2      BAC   Good  Feb 2024
3      CBA    Bad  Apr 2022

第二个Dataframe

Id Customer Status      Date
1      ABC    Bad  Mar 2023
2      BAC   Good  Feb 2024
5      CBA   Good  Apr 2024

比较Dataframes

print("Dataframe difference -- \n")
print(df1.compare(df2))

print("Dataframe difference keeping equal values -- \n")
print(df1.compare(df2, keep_equal=True))

print("Dataframe difference keeping same shape -- \n")
print(df1.compare(df2, keep_shape=True))

print("Dataframe difference keeping same shape and equal values -- \n")
print(df1.compare(df2, keep_shape=True, keep_equal=True))

结果

Dataframe difference -- 

    Id       Status            Date          
  self other   self other      self     other
0  NaN   NaN   Good   Bad       NaN       NaN
2  3.0   5.0    Bad  Good  Apr 2022  Apr 2024

Dataframe difference keeping equal values -- 

    Id       Status            Date          
  self other   self other      self     other
0    1     1   Good   Bad  Mar 2023  Mar 2023
2    3     5    Bad  Good  Apr 2022  Apr 2024

Dataframe difference keeping same shape -- 

    Id       Customer       Status            Date          
  self other     self other   self other      self     other
0  NaN   NaN      NaN   NaN   Good   Bad       NaN       NaN
1  NaN   NaN      NaN   NaN    NaN   NaN       NaN       NaN
2  3.0   5.0      NaN   NaN    Bad  Good  Apr 2022  Apr 2024

Dataframe difference keeping same shape and equal values -- 

    Id       Customer       Status            Date          
  self other     self other   self other      self     other
0    1     1      ABC   ABC   Good   Bad  Mar 2023  Mar 2023
1    2     2      BAC   BAC   Good  Good  Feb 2024  Feb 2024
2    3     5      CBA   CBA    Bad  Good  Apr 2022  Apr 2024

其他回答

edit2,我想出了一个新的解决方案,不需要设置索引

newdf=pd.concat([df1,df2]).drop_duplicates(keep=False)

好吧,我发现最高投票的答案已经包含我已经弄明白了。是的,我们只能在每个dfs中没有重复的情况下使用此代码。


我有一个棘手的方法。首先,我们将“Name”设置为问题给出的两个数据框架的索引。由于我们在两个dfs中有相同的' Name ',我们可以从'大' df中删除'小' df的索引。 这是代码。

df1.set_index('Name',inplace=True)
df2.set_index('Name',inplace=True)
newdf=df1.drop(df2.index)

Pandas现在提供了一个新的API来处理数据帧差异:Pandas . datafframe .compare

df.compare(df2)
  col1       col3
  self other self other
0    a     c  NaN   NaN
2  NaN   NaN  3.0   4.0
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']) & 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

使用lambda函数,您可以过滤_merge值为“left_only”的行,以获得df1中df2中缺失的所有行

df3 = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x :x['_merge']=='left_only']
df

nice @liangli的解决方案略有变化,不需要改变现有数据框架的索引:

newdf = df1.drop(df1.join(df2.set_index('Name').index))