Pandas如何通过DeviceVSweek匹配生成Duration_Week_N+1新列

解决方案 #

核心思路是先建立DeviceVSweek到Duration的映射关系，再用这个映射匹配DeviceVSweek_N+1列的值即可，实现代码如下：

import pandas as pd

# 示例数据构造
df = pd.DataFrame({
    'Device': [452,452,452],
    'Duration': [17461,2962,5370],
    'DeviceVSweek': ['452_1','452_2','452_3'],
    'DeviceVSweek_N+1': ['452_2','452_3','452_4']
})

# 1. 建立全表映射字典
week_duration_map = df.set_index('DeviceVSweek')['Duration'].to_dict()

# 2. 匹配生成新列，未匹配到的填充0
df['Duration_Week_N+1'] = df['DeviceVSweek_N+1'].map(week_duration_map).fillna(0).astype(int)

print(df)

运行后输出结果和预期完全一致：

Device  Duration DeviceVSweek DeviceVSweek_N+1  Duration_Week_N+1
0     452     17461        452_1            452_2               2962
1     452      2962        452_2            452_3               5370
2     452      5370        452_3            452_4                  0

方法说明 #

• 用set_index将DeviceVSweek设为索引后提取Duration列转字典，时间复杂度仅为O(n)，比循环逐行匹配效率高很多，适合大数据量场景
• map方法会自动按字典键值匹配全表对应值，不存在的键返回NaN，最后用fillna(0)统一填充未匹配的结果即可

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