在Python中按子类别计算解决率

步骤说明 #

1. 从原始数据中分离有效数据行（排除总计行），单独提取总计值；
2. 计算Pending C类别下WK 1和WK 2的总和；
3. 根据公式计算两个比率列的分母；
4. 筛选Resolved类别的数据，计算对应的解决率列；
5. 整理得到目标DataFrame。

代码实现 #

import pandas as pd

# 构造原始有效数据的DataFrame
data = [
    ["Pending B", "C", 1, 2],
    ["Pending B", "E", 3, 4],
    ["Pending B", "R", 4, 5],
    ["Pending C", "C", 1, 2],
    ["Pending C", "E", 3, 4],
    ["Pending C", "R", 4, 5],
    ["Resolved", "C", 1, 2],
    ["Resolved", "E", 3, 4],
    ["Resolved", "R", 4, 5]
]
df = pd.DataFrame(data, columns=["Category", "Issue", "WK 1", "WK 2"])

# 提取原始数据中的总计值
total_wk1 = 24
total_wk2 = 33

# 计算Pending C类别的WK1、WK2总和
sum_pending_c_wk1 = df[df["Category"] == "Pending C"]["WK 1"].sum()
sum_pending_c_wk2 = df[df["Category"] == "Pending C"]["WK 2"].sum()

# 计算比率的分母值
denominator_wk1 = total_wk1 - sum_pending_c_wk1
denominator_wk2 = total_wk2 - sum_pending_c_wk2

# 筛选Resolved数据并计算解决率列
resolved_df = df[df["Category"] == "Resolved"].copy()
resolved_df["WK 1(R)"] = resolved_df["WK 1"] / denominator_wk1
resolved_df["WK 2(R)"] = resolved_df["WK 2"] / denominator_wk2

# 查看结果
print(resolved_df)

结果验证 #

运行代码后得到的结果与目标DataFrame完全匹配：

内容的提问来源于stack exchange，提问作者alfred dowuona-owoo