困难的数据帧查找查询

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中找到该姓氏的家族存活率，并将值放入DF2中。如果姓不在DF1中，则必须为0。你知道吗

谢谢！你知道吗

Answer 1

使用从df1和^{}创建的^{}by Series替换不匹配的值：

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

Answer 2

您可以尝试以下方法：

DF2.loc[DF2['Surname']==DF1['Surname'],['FamilySurvivalRate']] = DF1['FamilySurvivalRate']

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

Answer 3

您需要根据DF2中的条目合并两个数据帧，然后用0填充缺少的值：

(
    df2
    # Remove FamilySurvivalRate from DF2, as it is of not interest
    .drop(columns=["FamilySurvivalRate"]
    # Retrieve possibly existing values from df1
    .merge(df1, how="left")
    # Fill missing values with 0
    .fillna({"FamilySurvivalRate": 0})
)