如何用Pandas的groupby、diff和map函数清洗数据框Unknown值

我有一个包含汽车轮胎更换数据的DataFrame（属于更大数据集的一部分），包含Plate、Make、Type、Car_Miles、Tire_Miles和Change列，原始数据如下：

Plate   Make    Type    Car_Miles   Tire_Miles  Change
0   ABC-123 Toyota  Comfort 387         387         Yes
1   ABC-123 Toyota  Comfort 15698       15311       No
2   ABC-123 Toyota  Comfort 25083       9385        No
3   ABC-123 Toyota  Comfort 37229       36842       Yes
4   ABC-123 Toyota  Comfort 50971       13742       No
5   ABC-123 Toyota  Comfort 85445       48216       Unknown
6   ABC-123 Toyota  Comfort 105870      20425       No
7   ABC-123 Toyota  Comfort 120454      14584       No
8   ABC-123 Toyota  Comfort 135878      50433       Unknown
9   DEF-456 Honda   Sport   507         507         Yes
10  DEF-456 Honda   Sport   18796       18289       No
11  DEF-456 Honda   Sport   22879       22372       Unknown
12  DEF-456 Honda   Sport   38674       15795       Unknown
13  DEF-456 Honda   Sport   48678       25799       Unknown
14  DEF-456 Honda   Sport   55879       7201        No
15  DEF-456 Honda   Sport   65321       16643       Yes
16  DEF-456 Honda   Sport   78526       13205       No

我希望通过Change列计算/验证轮胎里程：

• 当Change为Yes时，轮胎里程是当前行Car_Miles与上一个Change为Yes行的Car_Miles之差
• 当Change为No时，轮胎里程是当前行与上一行的Car_Miles之差

但Change列的Unknown值会导致现有代码计算出错误结果（比如倒数第4行的Calculated_Tire_Miles为10004.0），现有计算结果如下：

Plate   Make    Type    Car_Miles   Tire_Miles  Change  Calculated_Tire_Miles
0   ABC-123 Toyota  Comfort 387         387         Yes     NaN
1   ABC-123 Toyota  Comfort 15698       15311       No      15311.0
2   ABC-123 Toyota  Comfort 25083       9385        No      9385.0
3   ABC-123 Toyota  Comfort 37229       36842       Yes     36842.0
4   ABC-123 Toyota  Comfort 50971       13742       No      13742.0
5   ABC-123 Toyota  Comfort 85445       48216       Unknown 48216.0
6   ABC-123 Toyota  Comfort 105870      20425       No      20425.0
7   ABC-123 Toyota  Comfort 120454      14584       No      14584.0
8   ABC-123 Toyota  Comfort 135878      50433       Unknown 50433.0
9   DEF-456 Honda   Sport   507         507         Yes     NaN
10  DEF-456 Honda   Sport   18796       18289       No      18289.0
11  DEF-456 Honda   Sport   22879       22372       Unknown 22372.0
12  DEF-456 Honda   Sport   38674       15795       Unknown 15795.0
13  DEF-456 Honda   Sport   48678       25799       Unknown 10004.0
14  DEF-456 Honda   Sport   55879       7201        No      7201.0
15  DEF-456 Honda   Sport   65321       16643       Yes     16643.0
16  DEF-456 Honda   Sport   78526       13205       No      13205.0

我的目标是清洗Change列，生成Corrected_Change列，最终结果如下：

Plate   Make    Type    Car_Miles   Tire_Miles  Change  Corrected_Change
0   ABC-123 Toyota  Comfort 387         387         Yes     Yes
1   ABC-123 Toyota  Comfort 15698       15311       No      No
2   ABC-123 Toyota  Comfort 25083       9385        No      No
3   ABC-123 Toyota  Comfort 37229       36842       Yes     Yes
4   ABC-123 Toyota  Comfort 50971       13742       No      No
5   ABC-123 Toyota  Comfort 85445       48216       Unknown Yes
6   ABC-123 Toyota  Comfort 105870      20425       No      No
7   ABC-123 Toyota  Comfort 120454      14584       No      No
8   ABC-123 Toyota  Comfort 135878      50433       Unknown Yes
9   DEF-456 Honda   Sport   507         507         Yes     Yes
10  DEF-456 Honda   Sport   18796       18289       No      No
11  DEF-456 Honda   Sport   22879       22372       Unknown Yes
12  DEF-456 Honda   Sport   38674       15795       Unknown No
13  DEF-456 Honda   Sport   48678       25799       Unknown Yes
14  DEF-456 Honda   Sport   55879       7201        No      No
15  DEF-456 Honda   Sport   65321       16643       Yes     Yes
16  DEF-456 Honda   Sport   78526       13205       No      No

现有计算轮胎里程的代码如下：

import pandas as pd
import numpy as np

df = pd.read_csv('Stack_stuff.csv')
m = df.Change.map(dict(Yes=1, No=0)).astype(bool)
df.insert(6, 'Calculated_Tire_Miles', df.groupby(['Plate','Make','Type'])['Car_Miles'].diff().mask(m, df['Car_Miles'].where(m.astype(bool)).ffill().diff()))
df.loc[~(df['Calculated_Tire_Miles'] > 0), 'Calculated_Tire_Miles']=np.nan

请指点实现修正Change列的方向。

核心规律分析 #

观察目标Corrected_Change列的逻辑：

• 原始Change为Yes/No的，修正后保持不变
• 原始Change为Unknown的，判断标准是当前行的Tire_Miles是否等于当前行Car_Miles与上一个有效轮胎起点（即上一个Corrected_Change为Yes的行）的Car_Miles之差：
  • 若相等，说明这是一次轮胎更换，修正为Yes，同时更新轮胎起点
  • 若不相等，说明是未更换轮胎的常规里程记录，修正为No

实现步骤 #

1. 按Plate、Make、Type分组，确保每辆车的轮胎更换记录独立处理
2. 对每个分组，先复制原始Change值作为修正列的基础
3. 跟踪分组内上一个Yes行的Car_Miles，遍历每行处理Unknown值：
   • 若当前行是Yes，更新上一个Yes的里程值
   • 若当前行是Unknown，计算预期里程差并与Tire_Miles对比，决定修正值

代码实现 #

import pandas as pd
import numpy as np

def correct_change(group):
    # 初始化修正列，复制原始Change值
    corrected = group['Change'].copy()
    # 获取分组内第一个Yes的里程作为初始轮胎起点
    last_yes_miles = group.loc[group['Change'] == 'Yes', 'Car_Miles'].iloc[0]
    
    for idx, row in group.iterrows():
        if row['Change'] == 'Unknown':
            # 计算当前行与上一个轮胎起点的里程差
            expected_miles = row['Car_Miles'] - last_yes_miles
            # 浮点精度容错，避免微小误差影响判断
            if abs(expected_miles - row['Tire_Miles']) < 1e-6:
                corrected.loc[idx] = 'Yes'
                last_yes_miles = row['Car_Miles']  # 更新轮胎起点
            else:
                corrected.loc[idx] = 'No'
        elif row['Change'] == 'Yes':
            # 更新轮胎起点为当前行里程
            last_yes_miles = row['Car_Miles']
    return corrected

# 读取数据
df = pd.read_csv('Stack_stuff.csv')
# 分组应用修正函数
df['Corrected_Change'] = df.groupby(['Plate', 'Make', 'Type'], group_keys=False).apply(correct_change)

后续验证计算 #

用修正后的Corrected_Change列重新计算轮胎里程，即可避免Unknown导致的错误：

# 基于修正后的列生成判断掩码
m_corrected = df['Corrected_Change'].map(dict(Yes=True, No=False))
# 重新计算轮胎里程
df['Calculated_Tire_Miles'] = df.groupby(['Plate','Make','Type'])['Car_Miles'].diff().mask(
    m_corrected, 
    df.groupby(['Plate','Make','Type'])['Car_Miles'].apply(lambda x: x - x.where(m_corrected).ffill())
)
# 过滤无效的非正值
df.loc[~(df['Calculated_Tire_Miles'] > 0), 'Calculated_Tire_Miles'] = np.nan

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