比较两个熊猫数据框的不同

Question

假设我有两个 pandas DataFrame 即 df1, df2df1 = {name : [tom, jerry, jennifer, hafiz, kitty]}df2 = {name : [tom, jerry, alex, hafiz, samdin, unnar]}从这两个数据集中，我想生成good_boy = [tom, jerry] # present in both the datasetsbad_boy = [jenifer, hafiz, kitty] # present in df1 but not in df2new_boy = [alex, samdin, unnar] # in df2 but not in df1实际数据集非常大，有数百万行，我尝试进行迭代检查，但速度太慢了。Pandas 中是否已经存在任何 tric（并行处理）。请帮我解决这个问题，我的注意力是时间。谢谢

Answer 1

正如@QuangHoang 在评论中所说，这里的关键是merge. 该indicator=True选项需要一个额外的_merge列来指示该行是否存在于其中一个数据帧（以及哪个数据帧）或两者中：

df1 = pd.DataFrame({'name' : ['tom', 'jerry', 'jennifer', 'hafiz', 'kitty']})

df2 = pd.DataFrame({'name' : ['tom', 'jerry', 'alex', 'hafiz', 'samdin', 'unnar']})

tmp = pd.merge(df1, df2, how='outer', on='name', indicator=True)

good_boy = tmp.loc[tmp['_merge']=='both', 'name'].to_list()

bad_boy = tmp.loc[tmp['_merge']=='left_only', 'name'].to_list()

new_boy = tmp.loc[tmp['_merge']=='right_only', 'name'].to_list()

Answer 2

您可以使用DataFrame.join

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html

good_boy = df1.join(df2, on = 'name', how = 'inner')[['name_left']].rename(columns = {'name_left' : 'name'})

bad_boy = df1[~df1['name'].isin(df2['name'].tolist())]

new_boy = df2[~df2['name'].isin(df1['name'].tolist())]