测试不同列中是否存在值

Question

我想搜索我的列id等于id1或id2，然后在F1中添加值为col3的列。否则，我会的

   d = {'id1': ["ABC","ANB","ATB","BTP"],'id2':["XXX","YYY","ZZZ","TTT"], 'Name': ["A1","A2","A3","A4"]}
   F1 = pd.DataFrame(data=d)
   d = {'id': ["ABC","ANB","ZZZ"], 'col3': [0,1,1]}
   F2 = pd.DataFrame(data=d)

我做了这行代码，但没有给出预期的结果

 pd.concat([F1.merge(F2, left_on='id1', right_on='id'),F1.merge(F2, left_on='id2', right_on='id')], axis=0).drop(['Name','id'], axis=1)

预期输出如图所示。enter image description here

Answer 1

对两个列都使用双精度^{}，由^{}创建的^{cd2>}和^{}替换缺少的值：

s = F2.set_index('id')['col3']
F1['col3'] = F1['id1'].map(s).fillna(F1['id2'].map(s))
print (F1)
   id1  id2 Name  col3
0  ABC  XXX   A1   0.0
1  ANB  YYY   A2   1.0
2  ATB  ZZZ   A3   1.0
3  BTP  TTT   A4   NaN

详细信息：

print (F1['id1'].map(s))
0    0.0
1    1.0
2    NaN
3    NaN
Name: id1, dtype: float64

print (F1['id2'].map(s))
0    NaN
1    NaN
2    1.0
3    NaN
Name: id2, dtype: float64

print(F1['id1'].map(s).fillna(F1['id2'].map(s)))
0    0.0
1    1.0
2    1.0
3    NaN
Name: id1, dtype: float64

您的解决方案应该使用left join和fillna更改：

a = F1.merge(F2, left_on='id1', right_on='id', how='left')['col3']
b = F1.merge(F2, left_on='id2', right_on='id', how='left')['col3']

F1['col3'] = a.fillna(b)
print (F1)
   id1  id2 Name  col3
0  ABC  XXX   A1   0.0
1  ANB  YYY   A2   1.0
2  ATB  ZZZ   A3   1.0
3  BTP  TTT   A4   NaN

Last if need最后一列中的整数需要0.24+，按^{}强制转换：

F1['col3'] = F1['id1'].astype('Int64')
print (F1)
   id1  id2 Name  col3
0  ABC  XXX   A1     0
1  ANB  YYY   A2     1
2  ATB  ZZZ   A3     1
3  BTP  TTT   A4   NaN