基于Group by与条件判断的分组内成本分类需求

原始数据 #

分组分类规则 #

按Nombre字段分组，每组内按以下规则标记：

• 若COST Cost等于0.00%，标记为 INSTITUCIONAL
• 若COST Cost为分组内最小值，标记为 barata
• 若COST Cost大于分组内最小值，标记为 cara
• 若COST Cost为分组内最大值，标记为 super cara

方案1：SQL实现 #

利用窗口函数计算分组内的最小/最大成本，通过CASE WHEN完成条件判断：

SELECT 
    Nombre,
    Class,
    TOTAL,
    `COST Cost`,
    CASE
        WHEN `COST Cost` = '0.00%' THEN 'INSTITUCIONAL'
        WHEN `COST Cost` = MIN(`COST Cost`) OVER (PARTITION BY Nombre) THEN 'barata'
        WHEN `COST Cost` = MAX(`COST Cost`) OVER (PARTITION BY Nombre) THEN 'super cara'
        WHEN `COST Cost` > MIN(`COST Cost`) OVER (PARTITION BY Nombre) THEN 'cara'
    END AS Category
FROM your_table
ORDER BY Nombre, `COST Cost`;

方案2：Python Pandas实现 #

先转换成本为数值类型，再计算分组统计值，最后应用条件规则：

import pandas as pd

# 构造原始数据
df = pd.DataFrame([
    ["Biotechnology Discovery", "A", "1.82%", "0.75%"],
    ["Biotechnology Discovery", "C", "2.40%", "1.00%"],
    ["Biotechnology Discovery", "N", "2.57%", "1.25%"],
    ["Biotechnology Discovery", "I", "1.15%", "0.00%"],
    ["Global Growth", "A", "1.89%", "0.75%"],
    ["Global Growth", "N", "2.64%", "1.25%"],
    ["Global Growth", "I", "1.15%", "0.00%"]
], columns=["Nombre", "Class", "TOTAL", "COST Cost"])

# 转换成本为数值以便计算
df["cost_num"] = df["COST Cost"].str.rstrip("%").astype(float)

# 计算每组的最小、最大成本
group_metrics = df.groupby("Nombre")["cost_num"].agg(["min", "max"]).reset_index()
df = df.merge(group_metrics, on="Nombre")

# 应用分类规则
df["Category"] = df.apply(
    lambda x: "INSTITUCIONAL" if x["cost_num"] == 0
    else "barata" if x["cost_num"] == x["min"]
    else "super cara" if x["cost_num"] == x["max"]
    else "cara",
    axis=1
)

# 提取最终结果列
final_result = df[["Nombre", "Class", "TOTAL", "COST Cost", "Category"]]
print(final_result)

最终输出结果 #

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