Pandas：按指定字段分组后选取Par4最小且Par5最大的行

需求说明 #

现有包含par1、par2、par3、par4、par5、par6、par7列的Pandas DataFrame df，需按par1、par2、par3分组，筛选出每组中par4取最小值且par5取最大值的行。

示例输入 #

Par1,Par2,Par3,Par4,Par5,Par6,Par7
8,7,0,5,1.5,16.66,20.55
8,7,0,10,1.5,21.64,26.32
8,7,0,15,1.5,26.62,32.58
8,7,0,20,1.5,31.62,37.17
8,7,0,5,3,16.66,27.67
8,7,0,10,3,21.64,36.39
8,7,0,15,3,26.62,46.95
8,7,0,20,3,31.62,54.05

期望输出 #

Par1,Par2,Par3,Par4,Par5,Par6,Par7
8,7,0,5,3,16.66,27.67

该行符合要求是因为par4为5（组内最小值），par5为3（组内最大值）。

实现方法 #

方法一：聚合后合并筛选（高效）

先计算每组的par4最小值和par5最大值，再与原表合并筛选，适合大数据量场景：

import pandas as pd

# 构造示例数据（实际使用时替换为你的df）
data = {
    'Par1': [8]*8,
    'Par2': [7]*8,
    'Par3': [0]*8,
    'Par4': [5,10,15,20,5,10,15,20],
    'Par5': [1.5,1.5,1.5,1.5,3,3,3,3],
    'Par6': [16.66,21.64,26.62,31.62,16.66,21.64,26.62,31.62],
    'Par7': [20.55,26.32,32.58,37.17,27.67,36.39,46.95,54.05]
}
df = pd.DataFrame(data)

# 计算每组的统计值
group_stats = df.groupby(['Par1', 'Par2', 'Par3']).agg(
    min_par4=('Par4', 'min'),
    max_par5=('Par5', 'max')
).reset_index()

# 合并筛选
result = df.merge(group_stats, on=['Par1', 'Par2', 'Par3'])
result = result[(result['Par4'] == result['min_par4']) & (result['Par5'] == result['max_par5'])]

# 移除中间辅助列
result = result.drop(columns=['min_par4', 'max_par5'])

print(result)

方法二：分组apply筛选（直观）

通过groupby.apply逐组处理，逻辑直观但大数据量下效率较低：

import pandas as pd

# 构造示例数据
data = {
    'Par1': [8]*8,
    'Par2': [7]*8,
    'Par3': [0]*8,
    'Par4': [5,10,15,20,5,10,15,20],
    'Par5': [1.5,1.5,1.5,1.5,3,3,3,3],
    'Par6': [16.66,21.64,26.62,31.62,16.66,21.64,26.62,31.62],
    'Par7': [20.55,26.32,32.58,37.17,27.67,36.39,46.95,54.05]
}
df = pd.DataFrame(data)

def filter_group(group):
    min_p4 = group['Par4'].min()
    max_p5 = group['Par5'].max()
    return group[(group['Par4'] == min_p4) & (group['Par5'] == max_p5)]

result = df.groupby(['Par1', 'Par2', 'Par3']).apply(filter_group).reset_index(drop=True)

print(result)

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