如何按event_id分组替换Pandas DataFrame列值并清洗Salary字段

问题说明 #

现有事件维度的pandas DataFrame，每个唯一event_id对应多行关联记录，存在两类数据异常：

• 同event_id分组下的Name/Age/Occupation三列存在缺失值、随机乱码，同组内取值不统一
• Salary列尾部存在多余非数值字符，需要清洗
  处理规则：
• Name/Age/Occupation按event_id分组，用每组内出现频率最高的众数替换该组所有行对应列的取值
• Salary列移除尾部所有非数值字符，保留有效数值

输入样例 #

import pandas as pd
import numpy as np
# 构造测试数据
data = [
    [1, '1_a', 'Jan', 'andrew', '23', '', '13414.12'],
    [2, '1_a', 'Feb', '', pd.NA, 'teacher', '13414.12'],
    [3, '1_a', 'Mar', '___', '', '', '13414.12'],
    [4, '1_a', 'Apr', 'andrew', '23', 'teacher', '13414.12'],
    [5, '1_a', 'May', 'andrew', '24', 'principle', '25000'],
    [6, '1_b', 'Jan', 'Ash', '45', 'scientist', '1975.42_'],
    [7, '1_b', 'Feb', '#$%6', '', 'scientist', '1975.42'],
    [8, '1_b', 'Mar', 'Ash', '45', '^#3a2g4', '1975.42'],
    [9, '1_b', 'Apr', 'Ash', '45', 'scientist', '1975.42']
]
df = pd.DataFrame(data, columns=['ID','event_id','Month','Name','Age','Occupation','Salary'])

实现代码 #

1. 清洗Salary列 #

通过正则提取字符串开头的有效数字（支持整数、小数），自动过滤尾部非数值字符：

# 提取有效数值，移除尾部特殊字符
df['Salary'] = df['Salary'].astype(str).str.extract(r'^(\d+\.?\d*)')[0]
# 转换为数值类型
df['Salary'] = pd.to_numeric(df['Salary'])

2. 预处理无效值 #

将空字符串、含非法字符的乱码统一替换为NaN，避免干扰众数统计：

# 替换空字符串为NaN
df = df.replace('', np.nan)
# 过滤Name列乱码：仅保留全字母的有效值
df.loc[~df['Name'].str.fullmatch(r'[a-zA-Z]+', na=False), 'Name'] = np.nan
# 过滤Age列乱码：仅保留纯数字的有效值
df.loc[~df['Age'].str.fullmatch(r'\d+', na=False), 'Age'] = np.nan
df['Age'] = pd.to_numeric(df['Age'])
# 过滤Occupation列乱码：仅保留全字母的有效值
df.loc[~df['Occupation'].str.fullmatch(r'[a-zA-Z]+', na=False), 'Occupation'] = np.nan

3. 分组填充众数 #

对三个目标列按event_id分组，取每组众数填充整组：

mode_cols = ['Name', 'Age', 'Occupation']
for col in mode_cols:
    # 分组取众数，多众数默认取第一个；如果需要取组内最新时间的有效值，替换对应逻辑即可
    df[col] = df.groupby('event_id')[col].transform(
        lambda x: x.mode().iloc[0] if not x.mode().empty else np.nan
    )

结果说明 #

执行代码后1_b分组会完全匹配期望输出；如果业务规则要求同频次众数取组内按月份排序的最新有效值，调整步骤3的取值逻辑即可匹配1_a分组的输出要求。

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