python - 困难的数据帧查找查询

Question

我很确定已经有一个问题了，所以如果有人可以指出我正确的方向。

我有两个数据框，DF1:

+----------+-----------+------------+-------------+--------------------+
| Survived |  Surname  | FamilySize | NumSurvived | FamilySurvivalRate |
+----------+-----------+------------+-------------+--------------------+
|        0 | Braund    |          2 |           0 | 0                  |
|        1 | Cumings   |          1 |           1 | 1                  |
|        1 | Heikkinen |          1 |           1 | 1                  |
|        1 | Futrelle  |          2 |           1 | 0.5                |
|        0 | Allen     |          2 |           1 | 0.5                |
|        0 | Moran     |          3 |           1 | 0.333333333        |
|        0 | McCarthy  |          1 |           0 | 0                  |
|        0 | Palsson   |          4 |           0 | 0                  |
+----------+-----------+------------+-------------+--------------------+

和 DF2:

+----------+-----------+------------+-------------+--------------------+
| Survived |  Surname  | FamilySize | NumSurvived | FamilySurvivalRate |
+----------+-----------+------------+-------------+--------------------+
|        0 | Braund    |          2 |           0 |                    |
|        1 | Cumings   |          1 |           1 |                    |
|        1 | Heikkinen |          1 |           1 |                    |
|        1 | Futrelle  |          2 |           1 |                    |
|        0 | Allen     |          2 |           1 |                    |
|        0 | Moran     |          3 |           1 |                    |
|        0 | McCarthy  |          1 |           0 |                    |
|        0 | Palsson   |          4 |           0 |                    |
+----------+-----------+------------+-------------+--------------------+

对于 DF2 中的每个姓氏，我需要在 DF1 中找到该姓氏的 FamilySurvivalRate 并将该值放入 DF2 中。如果姓氏不在 DF1 中，则需要为 0。

谢谢!

Answer 1

使用Series.map由 Series 创建于 df1 和 Series.fillna用于替换不匹配的值:

print (df2)
  Survived    Surname  FamilySize  NumSurvived
0         0     Braund           2            0
1         1   Cumings1           1            1 <- change surname for no match
2         1  Heikkinen           1            1
3         1   Futrelle           2            1
4         0      Allen           2            1
5         0      Moran           3            1
6         0   McCarthy           1            0
7         0    Palsson           4            0

s = df1.set_index('Surname')['FamilySurvivalRate']
df2['FamilySurvivalRate'] = df2['Surname'].map(s).fillna(0)
print (df2)
   Survived    Surname  FamilySize  NumSurvived  FamilySurvivalRate
0         0     Braund           2            0            0.000000
1         1   Cumings1           1            1            0.000000
2         1  Heikkinen           1            1            1.000000
3         1   Futrelle           2            1            0.500000
4         0      Allen           2            1            0.500000
5         0      Moran           3            1            0.333333
6         0   McCarthy           1            0            0.000000
7         0    Palsson           4            0            0.000000