python - 用 python 中相应的名称替换数千行 ID 名称的最佳方法是什么？

Question

我有两个数据集。一个包含 16169 行 5 列，我想用相应的名称替换其中一列。那些对应的名字来自另一个数据集。

例如:

UniProtID    NAMEQ15173     PPP2R5BP30154     PPP2R1BP63151     PPP2R2ADrugBankID  Name    Type        UniProtID   UniProt NameDB00001 Lepirudin   BiotechDrug P00734  ProthrombinDB00002 Cetuximab   BiotechDrug P00533  Epidermal growth factor receptorDB00002 Cetuximab   BiotechDrug O75015  Low affinity immunoglobulin gamma Fc region receptor III-B

在此示例中，我想将所有 UniProt ID 替换为上面数据集示例中的相应名称。最好的方法是什么？

我真的是编程和 python 的新手，所以任何建议，帮助都表示赞赏。

Answer 1

我想你需要map由 set_index 创建的系列 , 如果某些值不匹配得到 NaN:

#change data for match
print (df1)
  UniProtID     NAME
0    O75015  PPP2R5B
1    P00734  PPP2R1B
2    P63151  PPP2R2A

df2['UniProt Name'] = df2['UniProtID'].map(df1.set_index('UniProtID')['NAME'])
print (df2)
  DrugBankID       Name         Type UniProtID UniProt Name
0    DB00001  Lepirudin  BiotechDrug    P00734      PPP2R1B
1    DB00002  Cetuximab  BiotechDrug    P00533          NaN
2    DB00002  Cetuximab  BiotechDrug    O75015      PPP2R5B

如果 NaN 需要原始值:

df2['UniProt Name'] = df2['UniProtID'].map(df1.set_index('UniProtID')['NAME'])
                                      .fillna(df2['UniProt Name'])
print (df2)
  DrugBankID       Name         Type UniProtID  \
0    DB00001  Lepirudin  BiotechDrug    P00734   
1    DB00002  Cetuximab  BiotechDrug    P00533   
2    DB00002  Cetuximab  BiotechDrug    O75015   

                       UniProt Name  
0                           PPP2R1B  
1  Epidermal growth factor receptor  
2                           PPP2R5B  

解决方案 merge - 需要 left 加入 fillna或 combine_first , 最后删除列 drop :

df = pd.merge(df2, df1, on="UniProtID", how='left')
df['UniProt Name'] = df['NAME'].fillna(df['UniProt Name'])
#alternative
#df['UniProt Name'] = df['NAME'].combine_first(df['UniProt Name'])
df.drop('NAME', axis=1, inplace=True)
print (df)
  DrugBankID       Name         Type UniProtID  \
0    DB00001  Lepirudin  BiotechDrug    P00734   
1    DB00002  Cetuximab  BiotechDrug    P00533   
2    DB00002  Cetuximab  BiotechDrug    O75015   

                       UniProt Name  
0                           PPP2R1B  
1  Epidermal growth factor receptor  
2                           PPP2R5B  

---

df = pd.merge(df2, df1, on="UniProtID", how='left')
df = df.drop('UniProt Name', axis=1).rename(columns={'NAME':'UniProt Name'})
print (df)
  DrugBankID       Name         Type UniProtID UniProt Name
0    DB00001  Lepirudin  BiotechDrug    P00734      PPP2R1B
1    DB00002  Cetuximab  BiotechDrug    P00533          NaN
2    DB00002  Cetuximab  BiotechDrug    O75015      PPP2R5B