基于0/1 DataFrame与数值DataFrame生成目标DataFrame的优化实现

• 拥有两个DataFrame：仅含0和1的df_one_zero、存储数值的df_value_total，二者均包含数千行和列，第一列id需保持完全不变。
• 需求：以5为窗口大小对列滑动处理，每个窗口内识别1的开始列和结束列，生成与df_one_zero形状相同、初始全0的df_out：
  • 若1在窗口的col列开始（且col不是窗口第0列），则在df_out的(row, col-1)位置填充df_value_window[row, col-1] - df_value_window[row, col]
  • 若1在窗口的col_end列结束（且col_end不是窗口最后一列），则在df_out的(row, col_end+1)位置填充df_value_window[row, col_end+1] - df_value_window[row, col_end]
• 现有代码无法准确追踪1的结束位置，且效率低下，需优化。

示例数据：

## df_one_zero示例
df = pd.DataFrame()
df['id'] = ['a', 'b', 'c']
df['0'] = [0, 0, 0]
df['1'] = [1, 0, 1]
df['2'] = [1, 1, 1]
df['3'] = [0, 0, 0]
df['4'] = [0, 0, 0]

df['5'] = [0, 0, 0]
df['6'] = [0, 1, 1]
df['7'] = [0, 0, 1]
df['8'] = [0, 0, 0]
df['9'] = [0, 0, 0]

df['10'] = [0, 0, 0]
df['11'] = [0, 0, 1]
df['12'] = [1, 1, 1]
df['13'] = [1, 0, 0]
df['14'] = [0, 0, 0]

df['15'] = [0, 0, 0]
df['16'] = [0, 1, 1]
df['17'] = [1, 1, 0]
df['18'] = [0, 0, 0]
df['19'] = [0, 0, 0]

## df_value_total示例
df1 = pd.DataFrame()
df1['id'] = ['a', 'b', 'c']
df1['0'] = [4, 0, 9]
df1['1'] = [0, 0, 1]
df1['2'] = [1, 1, 3]
df1['3'] = [6, 2, 0]
df1['4'] = [0, 0, 0]

df1['5'] = [0, 5, 0]
df1['6'] = [0, 1, 2]
df1['7'] = [0, 0, 1]
df1['8'] = [0, 0, 3]
df1['9'] = [0, 0, 0]

df1['10'] = [0, 0, 0]
df1['11'] = [0, 0, 1]
df1['12'] = [1, 1, 1]
df1['13'] = [1, 3, 4]
df1['14'] = [9, 0, 0]

df1['15'] = [0, 0, 0]
df1['16'] = [2, 1, 1]
df1['17'] = [1, 1, 4]
df1['18'] = [0, 5, 0]
df1['19'] = [0, 0, 0]

核心思路：用pandas向量化操作替代嵌套循环，通过差分快速定位1的起止位置，大幅提升处理效率。

import pandas as pd
import numpy as np

def generate_df_out(df_one_zero, df_value_total, window_size=5):
    # 初始化输出DataFrame，保留id列，其余列设为0
    df_out = df_one_zero.copy()
    df_out.loc[:, df_out.columns != 'id'] = 0
    
    # 提取数值列（排除id）
    num_cols = df_one_zero.columns[df_one_zero.columns != 'id']
    df_one = df_one_zero[num_cols]
    df_val = df_value_total[num_cols]
    
    # 计算窗口数量
    num_windows = (len(num_cols) + window_size - 1) // window_size
    
    for win_idx in range(num_windows):
        # 确定当前窗口的列范围
        start_col = win_idx * window_size
        end_col = start_col + window_size
        win_cols = num_cols[start_col:end_col]
        win_len = len(win_cols)
        
        if win_len == 0:
            continue
        
        # 获取当前窗口的子DataFrame
        win_one = df_one[win_cols]
        win_val = df_val[win_cols]
        
        # ---------------------- 处理1的开始位置 ----------------------
        # 差分得到1的开始掩码：当前列是1，前一列是0
        start_mask = (win_one.diff(axis=1) == 1)
        # 窗口第0列的1无需处理，置为False
        start_mask.iloc[:, 0] = False
        
        # 提取有效位置并赋值
        start_rows, start_win_cols = np.where(start_mask)
        start_global_cols = start_col + start_win_cols - 1
        valid_start = start_global_cols >= 0
        df_out.loc[start_rows[valid_start], num_cols[start_global_cols[valid_start]]] = (
            win_val.values[start_rows[valid_start], start_win_cols[valid_start]-1] - 
            win_val.values[start_rows[valid_start], start_win_cols[valid_start]]
        )
        
        # ---------------------- 处理1的结束位置 ----------------------
        # 差分得到1的结束掩码：当前列是1，后一列是0
        end_mask = (win_one.diff(axis=1, periods=-1) == -1)
        # 窗口最后一列的1无需处理，置为False
        end_mask.iloc[:, -1] = False
        
        # 提取有效位置并赋值
        end_rows, end_win_cols = np.where(end_mask)
        end_global_cols = start_col + end_win_cols + 1
        valid_end = end_global_cols < len(num_cols)
        df_out.loc[end_rows[valid_end], num_cols[end_global_cols[valid_end]]] = (
            win_val.values[end_rows[valid_end], end_win_cols[valid_end]+1] - 
            win_val.values[end_rows[valid_end], end_win_cols[valid_end]]
        )
    
    return df_out

# 测试示例
if __name__ == "__main__":
    df_out = generate_df_out(df, df1)
    print(df_out)

1. 向量化替代循环：用diff()方法快速生成1的起止位置掩码，避免逐行逐列遍历，处理数千行/列数据时效率提升明显。
2. 精准边界处理：自动跳过窗口首尾的无效位置（如窗口第0列开始、最后一列结束的情况），完全符合需求。
3. 全局索引映射：将窗口内的列索引转换为全局索引，确保赋值到df_out的正确位置。
4. 状态自动追踪：无需手动维护start_idx这类状态变量，通过差分掩码直接定位目标位置，逻辑更清晰。

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