DataFrame跨行列多条件值迁移问题求解

需要在DataFrame中实现跨行列的多条件值迁移：当某行的PERNR_H、WORKDATE_H、AWART_H列的值分别与另一行的PERNR、WORKDATE、AWART列的值匹配时，将该行的BEGUZ_H、ENDUZ_H列的值对应迁移到匹配行的BEGUZ、ENDUZ列。

尝试用np.where方法，但该方法仅支持同行条件匹配，无法满足跨行列需求。

示例数据 #

data = {'PERNR': [138888, 138432, 138432, 138437, 138437, 126162, 222829, 234740], 
        'WORKDATE': ['2024-01-25', '2024-01-10', '2024-01-09', '2024-01-26', '2024-01-24', '2024-01-29', '2024-01-15', '2024-01-09'],
        'AWART': [2235, 2235, 2235, 2235, 2235, 2235, 2235, 2235], 
        'BEGUZ': ['00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00'],
        'ENDUZ': ['00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00'],
        'PERNR_H': [0, 0, 126162, 222829, 234740, 0, 0, 138888], 
        'WORKDATE_H': ['NaN', 'NaN', '2024-01-29', '2024-01-15', '2024-01-09', 'NaN', 'NaN', '2024-01-25'],
        'AWART_H': [2235, 2235, 2235, 2235, 2235, 2235, 2235, 2235], 
        'BEGUZ_H': ['NaN', 'NaN', '07:59:00', '07:50:00', '07:03:00', 'NaN', 'NaN', '07:45:00'],
        'ENDUZ_H': ['NaN', 'NaN', '17:30:00', '17:35:00', '18:28:00', 'NaN', 'NaN', '18:59:00']}
df = pd.DataFrame(data)

输出结果：

PERNR    WORKDATE  AWART     BEGUZ     ENDUZ  PERNR_H  WORKDATE_H  AWART_H   BEGUZ_H   ENDUZ_H
0  138888  2024-01-25   2235  00:00:00  00:00:00        0         NaN     2235       NaN       NaN
1  138432  2024-01-10   2235  00:00:00  00:00:00        0         NaN     2235       NaN       NaN
2  138432  2024-01-09   2235  00:00:00  00:00:00   126162  2024-01-29     2235  07:59:00  17:30:00
3  138437  2024-01-26   2235  00:00:00  00:00:00   222829  2024-01-15     2235  07:50:00  17:35:00
4  138437  2024-01-24   2235  00:00:00  00:00:00   234740  2024-01-09     2235  07:03:00  18:28:00
5  126162  2024-01-29   2235  00:00:00  00:00:00        0         NaN     2235       NaN       NaN
6  222829  2024-01-15   2235  00:00:00  00:00:00        0         NaN     2235       NaN       NaN
7  234740  2024-01-09   2235  00:00:00  00:00:00   138888  2024-01-25     2235  07:45:00  18:59:00

尝试的代码 #

np.where((df['PERNR']==df['PERNR_H'])&(df['WORKDATE']==df['WORKDATE_H'])&(df['AWART']==df['AWART_H']),df['BEGUZ_H'], df['ENDUZ_H'])

期望结果 #

PERNR    WORKDATE  AWART     BEGUZ     ENDUZ  PERNR_H  WORKDATE_H  AWART_H   BEGUZ_H   ENDUZ_H
0  138888  2024-01-25   2235  07:45:00  18:59:00        0         NaN     2235       NaN       NaN
1  138432  2024-01-10   2235  00:00:00  00:00:00        0         NaN     2235       NaN       NaN
2  138432  2024-01-09   2235  00:00:00  00:00:00   126162  2024-01-29     2235  07:59:00  17:30:00
3  138437  2024-01-26   2235  00:00:00  00:00:00   222829  2024-01-15     2235  07:50:00  17:35:00
4  138437  2024-01-24   2235  00:00:00  00:00:00   234740  2024-01-09     2235  07:03:00  18:28:00
5  126162  2024-01-29   2235  07:59:00  17:30:00        0         NaN     2235       NaN       NaN
6  222829  2024-01-15   2235  07:50:00  17:35:00        0         NaN     2235       NaN       NaN
7  234740  2024-01-09   2235  07:03:00  18:28:00   138888  2024-01-25     2235  07:45:00  18:59:00

可以通过构造映射表结合merge，或者用字典映射+apply的方式实现跨行列匹配赋值，以下是两种可行方案：

方案一：Merge合并映射表（适合大数据量） #

1. 提取有效匹配键和待迁移值，构造映射表：

# 过滤无效行，重命名列以匹配原表的键
mapping = df[df['PERNR_H'] != 0].rename(columns={
    'PERNR_H': 'PERNR',
    'WORKDATE_H': 'WORKDATE',
    'AWART_H': 'AWART',
    'BEGUZ_H': 'BEGUZ_new',
    'ENDUZ_H': 'ENDUZ_new'
})[['PERNR', 'WORKDATE', 'AWART', 'BEGUZ_new', 'ENDUZ_new']]

2. 合并原表与映射表，更新目标列：

# 左连接保留原表所有行
df_merged = df.merge(mapping, on=['PERNR', 'WORKDATE', 'AWART'], how='left')
# 用新值替换原列，无匹配则保留原内容
df['BEGUZ'] = df_merged['BEGUZ_new'].combine_first(df['BEGUZ'])
df['ENDUZ'] = df_merged['ENDUZ_new'].combine_first(df['ENDUZ'])

方案二：字典映射+Apply（逻辑直观，适合小数据集） #

1. 构建匹配键到目标值的字典：

# 以(PERNR_H, WORKDATE_H, AWART_H)为键，构建BEGUZ_H和ENDUZ_H的映射字典
beguz_map = df[df['PERNR_H'] != 0].set_index(['PERNR_H', 'WORKDATE_H', 'AWART_H'])['BEGUZ_H'].to_dict()
enduz_map = df[df['PERNR_H'] != 0].set_index(['PERNR_H', 'WORKDATE_H', 'AWART_H'])['ENDUZ_H'].to_dict()

2. 逐行匹配赋值：

# 用原表的(PERNR, WORKDATE, AWART)作为键查找映射值，无匹配则保留原值
df['BEGUZ'] = df.apply(lambda x: beguz_map.get((x['PERNR'], x['WORKDATE'], x['AWART']), x['BEGUZ']), axis=1)
df['ENDUZ'] = df.apply(lambda x: enduz_map.get((x['PERNR'], x['WORKDATE'], x['AWART']), x['ENDUZ']), axis=1)

两种方案均可得到期望结果，方案一效率更高，适合处理大规模数据；方案二逻辑更清晰，便于理解调试。

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