Pandas排序后的数据框：基于连续正确拼写修正拼写错误

原始数据 #

Bevonce,2008,296853
Beyonce,2007,1210744
Beyonce,2007,1222003
Beyonce,2007,1222003
Beyonce,2007,1222007
Beyoncel,2007,1222002
Nicki Mina,2015,2717068
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2743565
Nicki Minajl,2015,2744974
Nicki Minal,2015,2741562
Nickl Minaj,2015,2741867

目标清洗后数据 #

Beyonce,2008,296853
Beyonce,2007,1210744
Beyonce,2007,1222003
Beyonce,2007,1222003
Beyonce,2007,1222007
Beyonce,2007,1222002
Nicki Minaj,2015,2717068
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2743565
Nicki Minaj,2015,2744974
Nicki Minaj,2015,2741562
Nicki Minaj,2015,2741867

错误类型说明 #

• 单个字符错误
• 多一个字符
• 少一个字符
• 以上两种情况的组合

清洗规则 #

仅当某拼写变体的前后存在至少3行连续相同的第一列值（视为正确拼写）时，才以该连续值为基准修正变体。例如：

• Bevonce 需以其后4行的 Beyonce 为基准修正
• Beyoncel 需以其前4行的 Beyonce 为基准修正

注：DataFrame始终按第一列排序。

实现代码（Pandas） #

import pandas as pd

# 读取原始数据
df = pd.read_csv(pd.compat.StringIO("""Bevonce,2008,296853
Beyonce,2007,1210744
Beyonce,2007,1222003
Beyonce,2007,1222003
Beyonce,2007,1222007
Beyoncel,2007,1222002
Nicki Mina,2015,2717068
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2741567
Nicki Minaj,2015,2743565
Nicki Minajl,2015,2744974
Nicki Minal,2015,2741562
Nickl Minaj,2015,2741867"""), header=None, names=['Artist', 'Year', 'Value'])

# 标记连续相同值的分组
df['group_id'] = (df['Artist'] != df['Artist'].shift()).cumsum()
# 计算每个分组的行数
group_sizes = df.groupby('group_id')['Artist'].count()
df['group_size'] = df['group_id'].map(group_sizes)

# 定义拼写修正逻辑
def correct_artist(row):
    if row['group_size'] >= 3:
        return row['Artist']
    # 检查前一个分组是否符合修正条件
    prev_group = row['group_id'] - 1
    if prev_group in group_sizes and group_sizes[prev_group] >= 3:
        return df[df['group_id'] == prev_group]['Artist'].iloc[0]
    # 检查后一个分组是否符合修正条件
    next_group = row['group_id'] + 1
    if next_group in group_sizes and group_sizes[next_group] >= 3:
        return df[df['group_id'] == next_group]['Artist'].iloc[0]
    # 不符合条件则保留原内容
    return row['Artist']

# 应用修正并清理临时列
df['Artist'] = df.apply(correct_artist, axis=1)
df = df.drop(['group_id', 'group_size'], axis=1)

# 输出清洗后的数据
print(df.to_csv(index=False, header=False))

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