Python中按CUI分组，将MSH对应CODE值填充至新列MSH_ID

原始DataFrame如下：

CUI      CODE     SAB TTY STR                                cui_name
C0000772 10028183 MDR LLT Multiple congenital anomalies      Multiple congenital anomalies
C0000772 10002585 MDR LLT Anomalies congenital multiple      Multiple congenital anomalies
C0000772 10002621 MDR LLT Anomaly congenital multiple (NOS)  Multiple congenital anomalies
C0000772 10025533 MDR LLT Malformations multiple             Multiple congenital anomalies
C0000772 10028182 MDR LLT Multiple congenital abnormalities  Multiple congenital anomalies
C0000772 10028182 MDR PT Multiple congenital abnormalities   Multiple congenital anomalies
C0000772 10028185 MDR LLT Multiple congenital malformations  Multiple congenital anomalies
C0000772 D000015 MSH MH "Abnormalities, Multiple"            Multiple congenital anomalies
C0001163 10000521 MDR LLT Acoustic nerve disorder NOS       Vestibulocochlear Nerve Diseases
C0001163 10078794 MDR LLT Auditory nerve disorder           Vestibulocochlear Nerve Diseases
C0001163 10078794 MDR PT Auditory nerve disorder            Vestibulocochlear Nerve Diseases
C0001163 D000160 MSH MH Vestibulocochlear Nerve Diseases    Vestibulocochlear Nerve Diseases

需要新增一列MSH_ID，要求同一CUI的所有行都填充该CUI对应SAB字段为MSH时的CODE值，预期输出如下：

CUI        CODE     SAB TTY STR                 cui_name                      MSH_ID
C0000772  10028183 MDR LLT Multiple congenital anomalies     Multiple congenital anomalies D000015
C0000772  10002585 MDR LLT Anomalies congenital multiple     Multiple congenital anomalies D000015
C0000772  10002621 MDR LLT Anomaly congenital multiple (NOS) Multiple congenital anomalies D000015
C0000772  10025533 MDR LLT Malformations multiple            Multiple congenital anomalies D000015
C0000772  10028182 MDR LLT Multiple congenital abnormalities Multiple congenital anomalies D000015
C0000772  10028182 MDR PT  Multiple congenital abnormalities Multiple congenital anomalies D000015
C0000772  10028185 MDR LLT Multiple congenital malformations Multiple congenital anomalies D000015
C0001163 10000521  MDR LLT Acoustic nerve disorder NOS     Vestibulocochlear Nerve Diseases D000160
C0001163 10078794  MDR LLT Auditory nerve disorder         Vestibulocochlear Nerve Diseases D000160
C0001163 10078794  MDR PT  Auditory nerve disorder         Vestibulocochlear Nerve Diseases D000160

尝试了以下代码，但无法将值填充到同一CUI的其他行：

test['MSH_ID'] = test.loc[test['SAB'] == 'MSH', 'CODE']

方法1：使用groupby + transform #

按CUI分组后，筛选每组内SAB='MSH'的CODE值，通过transform将唯一值广播到组内所有行（假设每个CUI对应唯一的MSH CODE）：

test['MSH_ID'] = test.groupby('CUI')['CODE'].transform(lambda x: x[test.loc[x.index, 'SAB'] == 'MSH'].iloc[0])

方法2：构建映射字典后map #

先提取CUI与对应MSH CODE的映射关系，再通过map匹配到每行：

# 创建CUI到MSH CODE的字典
msh_map = test[test['SAB'] == 'MSH'].set_index('CUI')['CODE'].to_dict()
# 映射生成新列
test['MSH_ID'] = test['CUI'].map(msh_map)

方法3：使用merge #

将原DataFrame与筛选出的MSH记录按CUI合并，自动填充对应值：

# 提取MSH相关的CUI和CODE，并重命名列
msh_df = test[test['SAB'] == 'MSH'][['CUI', 'CODE']].rename(columns={'CODE': 'MSH_ID'})
# 左连接合并
test = test.merge(msh_df, on='CUI', how='left')

以上三种方法均可实现需求，其中方法2代码简洁且效率较高，适合处理较大数据集。

内容的提问来源于stack exchange，提问作者rshar