如何稳健实现DataFrame内跨测量系列的条件数据迁移

需求说明 #

我有一份包含两个测量系列（Series A、Series B）的数据集，需要完成以下操作：

• 提取Series A中Type等于0的行的C、D列数值
• 将这些数值复制到Series B中满足以下条件的行的A、B列：
  • 该行的Name和Day与Series A对应行完全匹配
  • 是该Name+Day组合下首次出现且A、B列不为NaN的行

之前尝试通过获取Series A数据长度做偏移操作，但这种方法依赖固定数据顺序，当数据中有多个Name时会失效，求更稳健的实现方式。

输入数据：

Name    Day       Type     A     B     C     D      Meas_Series
0     Test1   20230101  0        3456  7890  0.123 0.456  Series A
1     Test1   20230101  1        6789  1234  nan   nan    Series A
2     Test1   20230101  2        8901  2345  nan   nan    Series A
3     Test1   20230102  0        2345  6789  0.345 0.678  Series A
4     Test1   20230102  1        5678  9012  nan   nan    Series A
5     Test1   20230102  2        3456  7890  nan   nan    Series A
6     Test1   20230101  99       3456  7890  nan   nan    Series B
7     Test1   20230101  99       nan   nan   nan   nan    Series B
8     Test1   20230101  99       nan   nan   nan   nan    Series B
9     Test1   20230102  99       2345  6789  nan   nan    Series B
10    Test1   20230102  99       nan   nan   nan   nan    Series B
11    Test1   20230102  99       nan   nan   nan   nan    Series B

预期输出：

Name    Day       Type     A     B     C     D      Meas_Series
0     Test1   20230101  0        3456  7890  0.123 0.456  Series A
1     Test1   20230101  1        6789  1234  nan   nan    Series A
2     Test1   20230101  2        8901  2345  nan   nan    Series A
3     Test1   20230102  0        2345  6789  0.345 0.678  Series A
4     Test1   20230102  1        5678  9012  nan   nan    Series A
5     Test1   20230102  2        3456  7890  nan   nan    Series A
6     Test1   20230101  99       0.123 0.456 nan   nan    Series B
7     Test1   20230101  99       nan   nan   nan   nan    Series B
8     Test1   20230101  99       nan   nan   nan   nan    Series B
9     Test1   20230102  99       0.345 0.678 nan   nan    Series B
10    Test1   20230102  99       nan   nan   nan   nan    Series B
11    Test1   20230102  99       nan   nan   nan   nan    Series B

解决方案 #

以下是基于Pandas的稳健实现方案，核心是通过分组匹配而非依赖数据顺序来完成操作：

步骤1：提取Series A的目标数据 #

先筛选出Series A中Type=0的行，保留关键列并将C、D重命名为A、B，方便后续合并匹配：

import pandas as pd
import numpy as np

# 假设df是你的原始数据框
a_target = df[(df['Meas_Series'] == 'Series A') & (df['Type'] == 0)][['Name', 'Day', 'C', 'D']].rename(columns={'C':'A', 'D':'B'})

步骤2：标记Series B的目标行 #

在Series B中，按Name和Day分组，标记每组中首次出现且A、B列不为空的行：

b_data = df[df['Meas_Series'] == 'Series B'].copy()
# 用cumsum标记每组第一个符合A、B非空条件的行
b_data['is_target'] = b_data.groupby(['Name', 'Day']).apply(
    lambda x: (x['A'].notna() & x['B'].notna()).cumsum() == 1
).reset_index(level=[0,1], drop=True)

步骤3：合并数据并更新原始表格 #

将提取的Series A目标数据与Series B的标记行进行合并，然后更新原始数据框的对应值：

# 合并匹配Name和Day的行
merged = b_data[b_data['is_target']].merge(a_target, on=['Name', 'Day'], how='left', suffixes=('_orig', '_new'))
# 更新原始数据框的A、B列
df.loc[merged.index, ['A', 'B']] = merged[['A_new', 'B_new']].values

完整代码示例 #

import pandas as pd
import numpy as np

# 构造输入数据（实际使用时替换为你的数据读取逻辑）
data = {
    'Name': ['Test1']*12,
    'Day': [20230101]*3 + [20230102]*3 + [20230101]*3 + [20230102]*3,
    'Type': [0,1,2,0,1,2,99,99,99,99,99,99],
    'A': [3456,6789,8901,2345,5678,3456,3456,np.nan,np.nan,2345,np.nan,np.nan],
    'B': [7890,1234,2345,6789,9012,7890,7890,np.nan,np.nan,6789,np.nan,np.nan],
    'C': [0.123,np.nan,np.nan,0.345,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
    'D': [0.456,np.nan,np.nan,0.678,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],
    'Meas_Series': ['Series A']*6 + ['Series B']*6
}
df = pd.DataFrame(data)

# 步骤1：提取Series A目标数据
a_target = df[(df['Meas_Series'] == 'Series A') & (df['Type'] == 0)][['Name', 'Day', 'C', 'D']].rename(columns={'C':'A', 'D':'B'})

# 步骤2：标记Series B目标行
b_data = df[df['Meas_Series'] == 'Series B'].copy()
b_data['is_target'] = b_data.groupby(['Name', 'Day']).apply(
    lambda x: (x['A'].notna() & x['B'].notna()).cumsum() == 1
).reset_index(level=[0,1], drop=True)

# 步骤3：合并更新
merged = b_data[b_data['is_target']].merge(a_target, on=['Name', 'Day'], how='left', suffixes=('_orig', '_new'))
df.loc[merged.index, ['A', 'B']] = merged[['A_new', 'B_new']].values

# 打印结果
print(df.to_string(index=False))

方案优势 #

• 完全基于Name和Day的分组匹配，不依赖数据的排列顺序
• 支持多Name场景，新增不同Name的数据也能正确匹配
• 逻辑清晰，每一步都有明确的筛选和标记，便于维护和调试

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