Pandas：如何基于条件语句生成对应成绩列数据

方法一：矢量化操作（推荐，高效） #

Pandas原生适合用矢量化操作处理批量数据转换，比循环效率更高。可以用np.select实现多条件的等级映射：

代码实现 #

import pandas as pd
import numpy as np

# 原始平均分数DataFrame
avg_df = pd.DataFrame.from_records(
    [
        {"Student": "Samantha", "AVG WK A": 104, "AVG WK B": 114},
        {"Student": "Billy", "AVG WK A": 70, "AVG WK B": 92},
    ],
)

# 初始化成绩DataFrame，保留学生姓名列
grade_df = avg_df[["Student"]].copy()

# 遍历所有分数列，生成对应等级列
for col in avg_df.columns:
    if col == "Student":
        continue
    # 获取当前列的分数数据
    scores = avg_df[col]
    # 定义条件与对应等级
    conditions = [
        scores >= 110,
        (scores > 91) & (scores < 110),
        scores <= 91
    ]
    grades = ["A+", "A", "C-"]
    # 映射等级并添加到成绩DataFrame
    grade_col_name = col.replace("AVG WK ", "Week ")
    grade_df[grade_col_name] = np.select(conditions, grades, default="C-")

print(grade_df)

输出结果 #

+----+-----------+----------+----------+
|    | Student   | Week A   | Week B   |
|----+-----------+----------+----------|
|  0 | Samantha  | A        | A+       |
|  1 | Billy     | C-       | A        |
+----+-----------+----------+----------+

方法二：行列循环处理（符合你的需求） #

如果需要显式遍历每行每列赋值，可按以下方式实现：

代码实现 #

import pandas as pd

# 原始平均分数DataFrame
avg_df = pd.DataFrame.from_records(
    [
        {"Student": "Samantha", "AVG WK A": 104, "AVG WK B": 114},
        {"Student": "Billy", "AVG WK A": 70, "AVG WK B": 92},
    ],
)

# 创建空的成绩DataFrame
grade_df = pd.DataFrame({
    "Student": avg_df["Student"],
    "Week A": "",
    "Week B": ""
})

# 遍历每行学生数据
for idx in range(len(avg_df)):
    # 处理Week A成绩
    score_a = avg_df.loc[idx, "AVG WK A"]
    if score_a >= 110:
        grade_df.loc[idx, "Week A"] = "A+"
    elif 91 < score_a < 110:
        grade_df.loc[idx, "Week A"] = "A"
    else:
        grade_df.loc[idx, "Week A"] = "C-"
    
    # 处理Week B成绩
    score_b = avg_df.loc[idx, "AVG WK B"]
    if score_b >= 110:
        grade_df.loc[idx, "Week B"] = "A+"
    elif 91 < score_b < 110:
        grade_df.loc[idx, "Week B"] = "A"
    else:
        grade_df.loc[idx, "Week B"] = "C-"

print(grade_df)

错误原因说明 #

你之前用df.loc[len(df)]导致所有列成绩相同，是因为错误地给整列赋值（比如grade_df["Week A"] = "A"），而非定位到具体行和列。正确做法是用grade_df.loc[idx, "列名"]精准指定单元格赋值。

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