Pandas - 2 个数据帧，将 df1 的索引列添加到第二列上的 df2

我有 2 个数据框：

df1 (sample, has more columns):

+---+----------------+--------------+-----------+
|   |     Region     | Placement ID |   Units   |
+---+----------------+--------------+-----------+
| 0 | Western Europe | 1.10872E+13  | 367628.76 |
| 1 | Western Europe | 1.10872E+13  | 367628.76 |
| 2 | Western Europe | 1.10872E+13  | 74604.63  |
+---+----------------+--------------+-----------+

df2 (sample, has more columns:

+-----------+----------------+--------------+
| Creatives | Publisher Name | Placement ID |
+-----------+----------------+--------------+
| Temenos   | Quantcast      | 1.10872E+13  |
| Temenos   | Quantcast      | 1.10872E+13  |
| Temenos   | Quantcast      | 1.10872E+13  |
+-----------+----------------+--------------+

我想要做的是在数据帧 2 中添加一个额外的列，其中数据帧 1 的索引列基于放置 ID。

数据帧 1 或 2 中的某些展示位置 ID 字段可能为空，或者有错误值，如果没有匹配或发现错误，那么我想添加缺失或错误值，例如 N/A、缺失、或留空

你需要的IIUCmerge http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html，但是存在重复的问题，所以首先删除它们drop_duplicates http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html然后选择一列用于添加，另一列用于连接（Placement ID):

print (pd.merge(df2, 
                df1.drop_duplicates('Placement ID')[['Units', 'Placement ID']], 
                how='left', 
                on='Placement ID'))


  Creatives Publisher Name  Placement ID      Units
0   Temenos      Quantcast  1.108720e+13  367628.76
1   Temenos      Quantcast  1.108720e+13  367628.76
2   Temenos      Quantcast  1.108720e+13  367628.76

如果需要添加索引需要reset_index http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html:

print (pd.merge(df2, 
                df1.drop_duplicates('Placement ID')
                   .reset_index()[['level_0','Placement ID']], 
                how='left', 
                on='Placement ID'))
  Creatives Publisher Name  Placement ID  level_0
0   Temenos      Quantcast  1.108720e+13        0
1   Temenos      Quantcast  1.108720e+13        0
2   Temenos      Quantcast  1.108720e+13        0

需要删除重复项，因为merge通过连接键进行多行 - 有 3 个相同的值1.108720e+13 in df2和 3 行df1，因此得到 3 x 3 行，例如：

print (pd.merge(df2, 
                df1.reset_index()[['level_0', 'Placement ID']], 
                how='left', 
                on='Placement ID'))

  Creatives Publisher Name  Placement ID  level_0
0   Temenos      Quantcast  1.108720e+13        0
1   Temenos      Quantcast  1.108720e+13        1
2   Temenos      Quantcast  1.108720e+13        2
3   Temenos      Quantcast  1.108720e+13        0
4   Temenos      Quantcast  1.108720e+13        1
5   Temenos      Quantcast  1.108720e+13        2
6   Temenos      Quantcast  1.108720e+13        0
7   Temenos      Quantcast  1.108720e+13        1
8   Temenos      Quantcast  1.108720e+13        2