不同大小DataFrame间ID速度对比分类问题求助

需要在不同大小的Pandas DataFrame中对比各ID在不同Condition下的Speed值，边界条件：

• 并非所有ID都存在于每个Condition中
• 同一ID在不同Condition中的出现频率不一致

目标：基于Cond_A的Speed值，为其他Condition的ID标记分类：

• faster：Speed > Cond_A对应ID的Speed + 10%
• slower：Speed < Cond_A对应ID的Speed - 10%
• equal：Speed处于Cond_A对应ID的Speed±10%区间内

数据示例 #

import numpy as np
import pandas as pd

data1 = {
     'ID' : [1, 1, 1, 2, 3, 3, 4, 5],
     'Condition' : ['Cond_A', 'Cond_A', 'Cond_A', 'Cond_A', 'Cond_A', 'Cond_A','Cond_A','Cond_A', ],
     'Speed' : [1.2, 1.05, 1.2, 1.3, 1.0, 0.85, 1.1, 0.85],
     }
df1 = pd.DataFrame(data1)
    
data2 = {
     'ID' : [1, 2, 3, 4, 5, 6],
     'Condition' : ['Cond_B', 'Cond_B', 'Cond_B', 'Cond_B', 'Cond_B', 'Cond_B' ],
     'Speed' : [0.8, 0.55, 0.7, 1.15, 1.2, 1.4],
     }
df2 = pd.DataFrame(data2)
    
data3 = {
     'ID' : [1, 2, 3, 4, 6],
     'Condition' : ['Cond_C', 'Cond_C', 'Cond_C', 'Cond_C', 'Cond_C' ],
     'Speed' : [1.8, 0.99, 1.7, 131, 0.2, ],
     }
df3 = pd.DataFrame(data3)
lst_of_dfs = [df1,df2, df3]
data = pd.concat(lst_of_dfs)

预期结果示例 #

Condition  ID    Speed   Category
0     Cond_A   1    1.150   NaN
1     Cond_A   2    1.300   NaN
2     Cond_A   3    0.925   NaN
3     Cond_A   4    1.100   NaN
4     Cond_A   5    0.850   NaN
5     Cond_B   1    0.800   slower
6     Cond_B   2    0.550   slower
7     Cond_B   3    0.700   slower
8     Cond_B   4    1.150   equal
...

尝试步骤及错误 #

1. 按Condition和ID分组求Speed平均值：

data = data.groupby(["Condition", "ID"]).mean()["Speed"].reset_index()

2. 基于Cond_A的Speed定义±10%阈值：

threshold_upper = data.loc[(data.Condition == 'CondA')]['Speed'] + (data.loc[(data.Condition == 'CondA')]['Speed']*10/100) 
threshold_lower = data.loc[(data.Condition == 'CondA')]['Speed'] - (data.loc[(data.Condition == 'CondA')]['Speed']*10/100)

3. 使用numpy.select标记分类：

conditions = [
    (data.loc[(data.Condition == 'CondB')]['Speed'] >  threshold_upper),
    (data.loc[(data.Condition == 'CondC')]['Speed'] >  threshold_upper),
    ((data.loc[(data.Condition == 'CondB')]['Speed'] <  threshold_upper) &  (data.loc[(data.Condition == 'CondB')]['Speed'] >  threshold_lower)),
    ((data.loc[(data.Condition == 'CondC')]['Speed'] <  threshold_upper) &  (data.loc[(data.Condition == 'CondC')]['Speed'] >  threshold_lower)),
    (data.loc[(data.Condition == 'CondB')]['Speed'] <  threshold_upper),
    (data.loc[(data.Condition == 'CondC')]['Speed'] <  threshold_upper),
    ]

values = [
    'faster',
    'faster',
     'equal', 
     'equal',
     'slower',
     'slower'
      ]

data['Category'] = np.select(conditions, values)

执行后报错：ValueError: Length of values (0) does not match length of index (16)

错误原因分析 #

1. 拼写错误：代码中把Cond_A写成了CondA，导致无法正确筛选Cond_A的数据，threshold_upper和threshold_lower为空序列
2. 索引不匹配：即使拼写正确，直接取Cond_A的Speed系列和其他Condition的行索引不对齐，numpy.select的条件长度和整个data的索引长度不匹配，赋值时就会报错
3. 未处理无Cond_A基准的ID：比如ID6不存在于Cond_A中，需要单独处理这类情况

正确实现步骤 #

1. 提取Cond_A的基准Speed：先计算每个ID在Cond_A下的平均Speed，建立ID到基准值的映射
2. 合并基准数据到主表：让每一行都能拿到对应ID的Cond_A基准值，方便后续计算
3. 计算阈值并分类：用numpy.select统一判断所有非Cond_A的行，同时处理无基准的情况

完整代码 #

import numpy as np
import pandas as pd

# 原始数据构建
data1 = {
     'ID' : [1, 1, 1, 2, 3, 3, 4, 5],
     'Condition' : ['Cond_A', 'Cond_A', 'Cond_A', 'Cond_A', 'Cond_A', 'Cond_A','Cond_A','Cond_A', ],
     'Speed' : [1.2, 1.05, 1.2, 1.3, 1.0, 0.85, 1.1, 0.85],
     }
df1 = pd.DataFrame(data1)
    
data2 = {
     'ID' : [1, 2, 3, 4, 5, 6],
     'Condition' : ['Cond_B', 'Cond_B', 'Cond_B', 'Cond_B', 'Cond_B', 'Cond_B' ],
     'Speed' : [0.8, 0.55, 0.7, 1.15, 1.2, 1.4],
     }
df2 = pd.DataFrame(data2)
    
data3 = {
     'ID' : [1, 2, 3, 4, 6],
     'Condition' : ['Cond_C', 'Cond_C', 'Cond_C', 'Cond_C', 'Cond_C' ],
     'Speed' : [1.8, 0.99, 1.7, 131, 0.2, ],
     }
df3 = pd.DataFrame(data3)
lst_of_dfs = [df1,df2, df3]
data = pd.concat(lst_of_dfs)

# 步骤1：计算Cond_A下每个ID的平均Speed，作为基准
cond_a_benchmark = data[data['Condition'] == 'Cond_A'].groupby('ID')['Speed'].mean().reset_index()
cond_a_benchmark.rename(columns={'Speed': 'Benchmark_Speed'}, inplace=True)

# 步骤2：合并基准数据到主表，每个ID匹配对应的基准值
data = pd.merge(data, cond_a_benchmark, on='ID', how='left')

# 步骤3：计算上下阈值
data['Upper_Threshold'] = data['Benchmark_Speed'] * 1.1
data['Lower_Threshold'] = data['Benchmark_Speed'] * 0.9

# 步骤4：定义分类规则，用numpy.select实现
conditions = [
    # Cond_A本身标记为NaN
    (data['Condition'] == 'Cond_A'),
    # 无基准的ID（比如ID6）标记为NaN
    (data['Benchmark_Speed'].isna()),
    # faster：超过上限
    (data['Speed'] > data['Upper_Threshold']),
    # slower：低于下限
    (data['Speed'] < data['Lower_Threshold']),
    # equal：在区间内
    (data['Speed'] >= data['Lower_Threshold']) & (data['Speed'] <= data['Upper_Threshold'])
]

values = [
    np.nan,
    np.nan,
    'faster',
    'slower',
    'equal'
]

data['Category'] = np.select(conditions, values)

# 可选：整理输出格式，保留需要的列并排序
result = data[['Condition', 'ID', 'Speed', 'Category']].sort_values(by=['Condition', 'ID']).reset_index(drop=True)
print(result)

输出结果说明 #

• Cond_A的所有行Category为NaN
• 无Cond_A基准的ID（如ID6）Category为NaN
• 其他ID按规则正确分类，比如：
  • Cond_B的ID1：Speed=0.8 < 1.15*0.9=1.035 → slower
  • Cond_B的ID4：Speed=1.15 在1.10.9=0.99和1.11.1=1.21之间 → equal
  • Cond_C的ID1：Speed=1.8 >1.15*1.1=1.265 → faster

内容的提问来源于stack exchange，提问作者Paul G.