我有两个数据帧df1和df2,其中df2是df1的子集。我如何得到一个新的数据帧(df3),这是两个数据帧之间的差异?
换句话说,一个在df1中所有的行/列都不在df2中的数据帧?
我有两个数据帧df1和df2,其中df2是df1的子集。我如何得到一个新的数据帧(df3),这是两个数据帧之间的差异?
换句话说,一个在df1中所有的行/列都不在df2中的数据帧?
当前回答
我在处理副本时遇到了问题,当一边有副本,另一边至少有一个副本时,所以我使用了Counter。集合做一个更好的差异,确保双方有相同的计数。这不会返回副本,但如果双方有相同的计数,则不会返回任何副本。
from collections import Counter
def diff(df1, df2, on=None):
"""
:param on: same as pandas.df.merge(on) (a list of columns)
"""
on = on if on else df1.columns
df1on = df1[on]
df2on = df2[on]
c1 = Counter(df1on.apply(tuple, 'columns'))
c2 = Counter(df2on.apply(tuple, 'columns'))
c1c2 = c1-c2
c2c1 = c2-c1
df1ondf2on = pd.DataFrame(list(c1c2.elements()), columns=on)
df2ondf1on = pd.DataFrame(list(c2c1.elements()), columns=on)
df1df2 = df1.merge(df1ondf2on).drop_duplicates(subset=on)
df2df1 = df2.merge(df2ondf1on).drop_duplicates(subset=on)
return pd.concat([df1df2, df2df1])
> df1 = pd.DataFrame({'a': [1, 1, 3, 4, 4]})
> df2 = pd.DataFrame({'a': [1, 2, 3, 4, 4]})
> diff(df1, df2)
a
0 1
0 2
其他回答
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
通过使用drop_duplicate
pd.concat([df1,df2]).drop_duplicates(keep=False)
更新:
上面的方法只适用于那些本身没有副本的数据帧。例如:
df1=pd.DataFrame({'A':[1,2,3,3],'B':[2,3,4,4]})
df2=pd.DataFrame({'A':[1],'B':[2]})
它将输出如下所示,这是错误的
错误输出:
pd.concat([df1, df2]).drop_duplicates(keep=False)
Out[655]:
A B
1 2 3
正确的输出
Out[656]:
A B
1 2 3
2 3 4
3 3 4
如何实现这一目标?
方法一:将isin与tuple结合使用
df1[~df1.apply(tuple,1).isin(df2.apply(tuple,1))]
Out[657]:
A B
1 2 3
2 3 4
3 3 4
方法二:与指标合并
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
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)
使用lambda函数,您可以过滤_merge值为“left_only”的行,以获得df1中df2中缺失的所有行
df3 = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x :x['_merge']=='left_only']
df
通过索引查找差异。假设df1是df2的一个子集,并且在进行子集设置时将索引前移
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