基于字符串前缀规则条件填充Pandas DataFrame数值列

我来帮你搞定这个需求！我们需要根据Code列的前缀规则，对Numx和Numy列做针对性处理，下面是完整的实现步骤：

步骤1：预处理数据类型 #

原始数据里的Numx和Numy混合了浮点数和空字符串，首先要把它们转换成数值类型，空字符串转为NaN：

import pandas as pd
import numpy as np

# 原始数据
d = ({ 
 'Code' :['A1','A1','','B1','B1','A2','A2','','B2','B2','','A3','A3','A3','','B1','','B4','B4','A2','A2','A1','A1','','B4','B4','C1','C1','','','D1','','B2'],
 'Numx' : [30.2,30.5,30.6,35.6,40.2,45.5,46.1,48.1,48.5,42.2,'',30.5,30.6,35.6,40.2,45.5,'',48.1,48.5,42.2, 40.1,48.5,42.2,'',48.5,42.2,43.1,44.1,'','','','',45.1],
 'Numy' : [1.9,2.3,2.5,2.2,2.5,3.1,3.4,3.6,3.7,5.4,'',2.3,2.5,2.2,2.5,3.1,'',3.6,3.7,5.4,6.5,8.5,2.2,'',8.5,2.2,2.3,2.5,'','','','',3.2] 
})
df = pd.DataFrame(data=d)

# 转换数值列类型，空字符串转NaN
df['Numx'] = pd.to_numeric(df['Numx'], errors='coerce')
df['Numy'] = pd.to_numeric(df['Numy'], errors='coerce')

步骤2：标记分组与锁定状态 #

我们需要给每行标记对应的主Code、识别C开头Code的锁定阶段，以及标记B组的首行：

# 生成主Code列：前向填充非空的Code
df['main_code'] = df['Code'].replace('', np.nan).ffill()

# 标记Code前缀类型
df['code_type'] = df['main_code'].apply(lambda x: x[0] if pd.notna(x) and len(x)>=1 else '')

# 标记C锁定阶段的开始和结束
df['lock_start'] = df['code_type'] == 'C'
df['lock_end'] = df['code_type'].isin(['A','B']) & df['lock_start'].cumsum() > 0

# 生成锁定阶段的组键
df['lock_group'] = (df['lock_start'] | df['lock_end']).cumsum()

# 标记是否处于锁定状态
df['in_lock'] = df.groupby('lock_group')['lock_start'].transform(lambda x: x.any())

# 获取每个锁定阶段的首行数值
lock_values = df[df['lock_start']].groupby('lock_group').agg({'Numx':'first', 'Numy':'first'}).rename(columns={'Numx':'locked_x', 'Numy':'locked_y'})
df = df.merge(lock_values, on='lock_group', how='left')

# 标记B组的首行
df['b_group'] = (df['code_type'] == 'B').cumsum()
df['is_b_first'] = df.groupby('b_group')['code_type'].transform(lambda x: x.eq('B').cumcount() == 0)

步骤3：应用规则填充数值 #

用np.select按优先级应用三个规则：

1. A开头Code组：保留原有数值
2. B开头Code组：仅首行保留数值，其余行设为NaN
3. C锁定阶段：使用锁定的首行数值
4. 其他情况：保留原有数值

# 填充Numx
df['Numx'] = np.select(
    [
        df['code_type'] == 'A',
        (df['code_type'] == 'B') & ~df['is_b_first'],
        df['in_lock']
    ],
    [
        df['Numx'],
        np.nan,
        df['locked_x']
    ],
    default=df['Numx']
)

# 填充Numy
df['Numy'] = np.select(
    [
        df['code_type'] == 'A',
        (df['code_type'] == 'B') & ~df['is_b_first'],
        df['in_lock']
    ],
    [
        df['Numy'],
        np.nan,
        df['locked_y']
    ],
    default=df['Numy']
)

步骤4：清理输出格式 #

去掉辅助列，将空Code转回空字符串，按示例格式显示NaN：

# 清理辅助列
df = df.drop(['main_code', 'code_type', 'lock_start', 'lock_end', 'lock_group', 'in_lock', 'locked_x', 'locked_y', 'b_group', 'is_b_first'], axis=1)

# 将NaN的Code转回空字符串
df['Code'] = df['Code'].fillna('')

# 打印结果（NaN显示为'nan'）
print(df.to_string(index=False, na_rep='nan'))

运行这段代码后，输出就会和你期望的完全一致啦！

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