如何高效统计按ID分组后各参数列超出规格限的行数？

问题背景 #

现有两个Pandas DataFrame：dfSpecs 存储 dfData 中各参数列的规格限（LSL=下限，USL=上限），两者的参数顺序不一定一致。

示例数据 #

dfSpecs

import pandas as pd

dfSpecs = pd.DataFrame(
  {'param': ['parameter1', 'parameter2', 'parameter3'], 
  'LSL': [0, 10, 200], 
  'USL': [1, 20, 300]}
)

输出：

param       LSL  USL
0  parameter1    0    1
1  parameter2   10   20
2  parameter3  200  300

dfData

dfData = pd.DataFrame(
  {'id': ['foo', 'foo', 'bar', 'bar'], 
  'x': [1, 1, 1, 1], 
  'y': [1, 2, 1, 2],
  'parameter1': [0.5, 0.6, 1.8, 0.4],
  'parameter2': [12, 14, 21, 15],
  'parameter3': [199, 301, 256, 234]}
)

输出：

id  x  y  parameter1  parameter2  parameter3
0  foo  1  1         0.5          12         199
1  foo  1  2         0.6          14         301
2  bar  1  1         1.8          21         256
3  bar  1  2         0.4          15         234

需求 #

生成新DataFrame，按id和param分组，统计参数值超出规格限（小于LSL或大于USL）的行数，预期输出：

id       param  fail_count
0  foo  parameter1           0
1  foo  parameter2           0
2  foo  parameter3           2
3  bar  parameter1           1
4  bar  parameter2           1
5  bar  parameter3           0

原低效实现 #

使用嵌套循环，数据量大时耗时极长：

fails=[]
for id, df in dfData.groupby('id'):
    for i, spec in dfSpecs.iterrows():
        fail_count = len(df[spec['param']].loc[(df[spec['param']]<spec['LSL']) | (df[spec['param']]>spec['USL'])])
        fails.append([id, spec['param'], fail_count])
dfFails = pd.DataFrame(fails, columns=['id','parameter','fail_count'])

高效实现方案 #

方法一：长表转换+合并+分组统计 #

利用Pandas矢量化操作替代循环，代码简洁且性能优异：

# 1. 将dfData转为长表，仅保留id和参数列
df_melt = dfData.melt(
    id_vars=['id'],
    value_vars=dfSpecs['param'],
    var_name='param',
    value_name='value'
)

# 2. 合并规格限数据
df_merged = df_melt.merge(dfSpecs, on='param', how='left')

# 3. 判断是否超规格，分组统计失败次数
df_fails = df_merged.assign(
    is_fail=lambda x: (x['value'] < x['LSL']) | (x['value'] > x['USL'])
).groupby(['id', 'param'])['is_fail'].sum().reset_index(name='fail_count')

方法二：广播运算（适配超大数据集） #

如果数据量极大，可通过广播运算避免长表转换的内存开销：

# 1. 对齐参数列与规格限顺序
data_params = dfData[dfSpecs['param']]
lsl = dfSpecs['LSL'].values
usl = dfSpecs['USL'].values

# 2. 广播计算所有参数是否超规格
is_fail = (data_params < lsl) | (data_params > usl)

# 3. 按id分组求和，再转换为目标格式
df_fails = is_fail.groupby(dfData['id']).sum().unstack().reset_index(name='fail_count')
df_fails.columns = ['id', 'param', 'fail_count']

性能优势说明 #

原嵌套循环本质是Python级别的逐行迭代，Pandas在这类操作中性能极差，数据量越大耗时呈指数增长。上述方案全部采用底层C实现的矢量化操作，彻底避免Python循环，运算速度可提升数倍至数十倍，同时代码更易维护。

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