DataFrame删除CSV数据异常：.drop函数无法生效求助

我是Python新手，正在用MLB（美国职业棒球大联盟）数据做首次EDA（探索性数据分析）项目，目标是构建机器学习模型分析球员薪资与伤病、位置等统计数据的关联。为了排除受合同限制的底薪球员数据，需要删除指定年份对应指定薪资的观测值，但运行代码后DataFrame的内容和维度完全没变化，drop函数始终没生效，求解决。

原代码如下：

#2000: 200,000
#2001: 200,000
#2002: 300,000
#2003: $300,000
#2004: $300,000
#2005: $316,000
#2006: $327,000
#2007: $380,000
#2008: $390,000
#2009: $400,000
#2010: $400,000
#2011: $414,000
#2012: $480,000
#2013: $490,000
#2014: $500,000
#2015: $507,500
#2016: $507,500

import operator
import functools
import dataclasses

@dataclasses.dataclass
class SalaryItem:
    salary: int
    years: list

items = (
    SalaryItem(200000, ['2000', '2001','2002']),
    SalaryItem(300000, ['2003', '2004']),
    SalaryItem(316000, ['2005']),
    SalaryItem(327000, ['2006']),
    SalaryItem(380000, ['2007']),
    SalaryItem(390000, ['2008']),
    SalaryItem(400000, ['2009', '2010']),
    SalaryItem(414000, ['2011']),
    SalaryItem(480000, ['2012']),
    SalaryItem(490000, ['2013']),
    SalaryItem(500000, ['2014']),
    SalaryItem(507500, ['2015' , '2016']),
)

for item in items:
    Remove = SalariesCSV[
        (SalDf['salary'] == item.salary) &
        functools.reduce(operator.or_, (SalariesCSV['yearID'] == year for year in item.years))
    ].index
    SalariesCSV.drop(Remove, inplace=True)
    
SalariesCSV = SalariesCSV.drop(Remove)

print(SalariesCSV)

问题排查 #

1. 变量名混用：筛选条件里用了SalDf['salary']，但实际操作的DataFrame是SalariesCSV，这会导致筛选结果为空，自然删不掉任何行。
2. 重复且无效的drop操作：循环内已经用inplace=True完成删除，循环外又执行SalariesCSV = SalariesCSV.drop(Remove)，此时Remove是最后一次循环的结果，大概率为空，完全没必要。
3. 数据类型不匹配：如果yearID列是数值类型（比如int），但代码里用字符串（如'2000'）做匹配，会导致条件不成立；另外原始数据中部分薪资带$和逗号（比如$300,000），如果salary列是字符串类型，和整数300000比较肯定不相等。

修正后的代码 #

第一步：清理数据格式

先处理薪资列和年份列的类型，确保匹配条件有效：

# 清理薪资列：去掉$和逗号，转换为整数
SalariesCSV['salary'] = SalariesCSV['salary'].replace('[\$,]', '', regex=True).astype(int)
# 统一年份列类型为字符串（和items里的年份格式一致）
SalariesCSV['yearID'] = SalariesCSV['yearID'].astype(str)

第二步：修正循环逻辑

统一变量名，去掉多余操作：

import operator
import functools
import dataclasses

@dataclasses.dataclass
class SalaryItem:
    salary: int
    years: list

items = (
    SalaryItem(200000, ['2000', '2001','2002']),
    SalaryItem(300000, ['2003', '2004']),
    SalaryItem(316000, ['2005']),
    SalaryItem(327000, ['2006']),
    SalaryItem(380000, ['2007']),
    SalaryItem(390000, ['2008']),
    SalaryItem(400000, ['2009', '2010']),
    SalaryItem(414000, ['2011']),
    SalaryItem(480000, ['2012']),
    SalaryItem(490000, ['2013']),
    SalaryItem(500000, ['2014']),
    SalaryItem(507500, ['2015' , '2016']),
)

for item in items:
    remove_indices = SalariesCSV[
        (SalariesCSV['salary'] == item.salary) &
        functools.reduce(operator.or_, (SalariesCSV['yearID'] == year for year in item.years))
    ].index
    SalariesCSV.drop(remove_indices, inplace=True)

print(SalariesCSV)

更简洁的写法（可选）

可以把所有(年份,薪资)对整理成列表，用isin一次性筛选删除，避免循环：

# 生成所有需要删除的(年份,薪资)组合
salary_year_pairs = []
for item in items:
    for year in item.years:
        salary_year_pairs.append( (year, item.salary) )

# 生成掩码，筛选出不需要删除的行
mask = SalariesCSV[['yearID', 'salary']].apply(tuple, axis=1).isin(salary_year_pairs)
SalariesCSV = SalariesCSV[~mask]

print(SalariesCSV)

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