如何比较来自两个不同数据帧的列并保留来自第一个数据帧的值？

Question

我有两个不同大小的数据帧。它们都有四列：Words、x、y和z

但是，当连接这两个数据帧时，我希望保持相似单词的x、y、z值。保留df1中不存在但df2中存在的单词

我试图使用pd.merge，但这将保留这两个值，并且只保留相似的单词。如果我使用pd.concat，我必须删除类似的元素，但不会从第一个数据帧中删除

样品

df1 = pd.DataFrame({'Words': 
       ['aardvark', 'abalone', 'abandon'],
     'x': [0.999, 0.888, 0.777], 
     'y': [0.999, 0.888, 0.777],
     'z': [0.999, 0.888, 0.777]})

df2 = pd.DataFrame({'Words': 
       ['aaaaahh', 'aardvark', 'abalone', 'abandon', 'zoo', 'zoom', 'zucchini'], 
     'x': [0.199, 0.111, 0.222, 0.333, 0.232, 0.842, 0.945], 
     'y': [0.929, 0.111, 0.222, 0.333, 0.112, 0.62, 0.265],
     'z': [0.993, 0.111, 0.222, 0.333, 0.212, 0.344, 0.745]})

# Expected output
df_res = pd.DataFrame({'Words': 
          ['aaaaahh', 'aardvark', 'abalone', 'abandon', 'zoo', 'zoom', 'zucchini'], 
     'x': [0.199, 0.999, 0.888, 0.777, 0.232, 0.842, 0.945], 
     'y': [0.929, 0.999, 0.888, 0.777, 0.112, 0.62, 0.265],
     'z': [0.993, 0.999, 0.888, 0.777, 0.212, 0.344, 0.745]})

我试过的

import pandas as pd

# Merge
df_res = pd.merge(df1, df2, on='Word', how='inner')

# Concat
df_concat = pd.concat(objs=[df1, df2], ignore_index=True)
df_concat = pd.drop_duplicates(subset=['Word'], keep=False, ignore_index=True)

# Compare
d_res = d1[(d1['Word'] != d1['Word'])]
ValueError: Can only compare identically-labeled Series objects

Answer 1

您可以使用df.append将df1附加到df2，然后是drop_duplicates，再加上keep='last'，然后是sort_index和reset_index：

>>> (df2.append(df1)
        .drop_duplicates('Words', keep='last')
        .sort_index()
        .reset_index(drop=True))

      Words      x      y      z
0   aaaaahh  0.199  0.929  0.993
1  aardvark  0.999  0.999  0.999
2   abalone  0.888  0.888  0.888
3   abandon  0.777  0.777  0.777
4       zoo  0.232  0.112  0.212
5      zoom  0.842  0.620  0.344
6  zucchini  0.945  0.265  0.745

Answer 2

也许性能不如@Sayandip Dutta answer，您可以尝试右连接（或左连接，取决于您在pd.merge中放置参数的顺序）：

In [4]: res = pd.merge(df1, df2, how='right', on='Words', suffixes=("_1", "_2"))

In [5]: res
Out[6]:
      Words    x_1    y_1    z_1    x_2    y_2    z_2
0  aardvark  0.999  0.999  0.999  0.111  0.111  0.111
1   abalone  0.888  0.888  0.888  0.222  0.222  0.222
2   abandon  0.777  0.777  0.777  0.333  0.333  0.333
3   aaaaahh    NaN    NaN    NaN  0.199  0.929  0.993
4       zoo    NaN    NaN    NaN  0.232  0.112  0.212
5      zoom    NaN    NaN    NaN  0.842  0.620  0.344
6  zucchini    NaN    NaN    NaN  0.945  0.265  0.745

然后你可以用x_2，y_2和z_2的值来fillnax_1，y_1，z_1

In [8]: res.x_1.fillna(res.x_2, inplace=True)

In [8]: res.y_1.fillna(res.y_2, inplace=True)

In [9]: res.z_1.fillna(res.z_2, inplace=True)

In [10]: df_res = res[["Words", "x_1", "y_1", ,"z_1"]]

In [11]: df_res
Out[11]:
      Words    x_1    y_1    z_1
0  aardvark  0.999  0.999  0.999
1   abalone  0.888  0.888  0.888
2   abandon  0.777  0.777  0.777
3   aaaaahh  0.199  0.929  0.993
4       zoo  0.232  0.112  0.212
5      zoom  0.842  0.620  0.344
6  zucchini  0.945  0.265  0.745