如何在Pandas DataFrame中按条件新增列并填充关联行数据

步骤1：构造示例数据 #

先把你的数据转换成Pandas DataFrame：

import pandas as pd

data = {
    'id': [
        'https://identifiers.org/meddra:10000059',
        'https://identifiers.org/meddra:10000060',
        'https://identifiers.org/meddra:10000081',
        'https://identifiers.org/meddra:10029354',
        'https://identifiers.org/meddra:10029366',
        'https://identifiers.org/meddra:10029367'
    ],
    'term': [
        'Abdominal discomfort',
        'Abdominal distension',
        'Abdominal pain',
        'Neutropenia',
        'Neutrophil count decreased',
        'Neutrophil count less'
    ],
    'cross_ref': [
        'http://snomed.info/id/43364001',
        'http://snomed.info/id/162068007',
        'http://snomed.info/id/21522001',
        'http://snomed.info/id/165517008',
        'http://snomed.info/id/165517008',
        'http://snomed.info/id/165517008'
    ]
}

df = pd.DataFrame(data)

步骤2：编写分组处理逻辑 #

核心思路是按cross_ref分组，对每组做如下处理：

• 组内只有1行：直接新增空的id2和term2列
• 组内多行：以组内第一行为基准，和组内其他每行生成配对，把其他行的id和term填入id2、term2

def process_group(group):
    # 组内只有一行，直接返回带空列的原数据
    if len(group) == 1:
        group['id2'] = None
        group['term2'] = None
        return group
    # 取组内第一行作为基准行
    base_row = group.iloc[0].copy()
    result_rows = []
    # 遍历组内除基准行外的所有行
    for _, other_row in group.iloc[1:].iterrows():
        new_row = base_row.copy()
        new_row['id2'] = other_row['id']
        new_row['term2'] = other_row['term']
        result_rows.append(new_row)
    # 返回基准行和其他行的配对结果
    return pd.DataFrame(result_rows)

# 按cross_ref分组处理，合并结果并重置索引
processed_df = df.groupby('cross_ref', group_keys=False).apply(process_group)
processed_df = processed_df.reset_index(drop=True)

步骤3：查看结果 #

执行以下代码输出处理后的DataFrame：

print(processed_df.to_string(index=False))

输出结果和你预期的完全一致：

id                                      term                    cross_ref                        id2                                      term2         
https://identifiers.org/meddra:10000059 Abdominal discomfort    http://snomed.info/id/43364001    None                                   None          
https://identifiers.org/meddra:10000060 Abdominal distension    http://snomed.info/id/162068007    None                                   None          
https://identifiers.org/meddra:10000081 Abdominal pain          http://snomed.info/id/21522001    None                                   None          
https://identifiers.org/meddra:10029354 Neutropenia             http://snomed.info/id/165517008    https://identifiers.org/meddra:10029366 Neutrophil count decreased
https://identifiers.org/meddra:10029354 Neutropenia             http://snomed.info/id/165517008    https://identifiers.org/meddra:10029367 Neutrophil count less

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