如何基于Pandas DataFrame的Grade与Score实现两表映射并扩展字段？

问题背景 #

现有两个Pandas DataFrame（DF1和DF2），结构如下：

DF1：

Max Score                           Sub parameters Grading Score
0          3                                 Greeting     Yes     3
1          7                                Listening     Yes     7
2          7            Comprehension and Application     Yes     7
3          5                     Appropriate Response     Yes     5
4          4                             Paraphrasing     Yes     4
5          7  Educating customer/Setting Expectations     Yes     7
6          4                          Professionalism     Yes     4
7          4                                  Empathy     Yes     4
8          4                      Ownership/Assurance     Yes     4
9          4                  Followed Hold Procedure     Yes     4
10         3                                  Closure     Yes     3
11         8         Sentence construction/Word order     Yes     8
12         8                   Pronunciation/Chunking     Yes     8
13         8               Fluency & Lexical resource     Yes     8
14         8                        Tone & Intonation     Yes     8
15         8                           Rate of Speech     Yes     8
16         8                                  Diction     Yes     8

DF2：

Grade  3  7.0  4.0  5.0  2.0  8.0
0              Yes  3  7.0  4.0  5.0  2.0  8.0
1  Yes/Improvement  1  4.0  2.0  3.0  1.0  4.0
2               No  0  0.0  0.0  0.0  0.0  0.0

需要实现：针对DF1每条记录，根据Score值，从DF2的Yes/Improvement、No行中提取对应列的数值，把这些Grade标识和对应得分作为新字段添加到结果表中。

解决方案 #

直接上代码和步骤说明：

1. 先预处理DF2，统一列名类型并构建映射表

import pandas as pd

# 如果是已有的DF1/DF2，跳过构造数据这一步，直接处理
# 构造示例数据（如果已有数据可忽略）
data_df1 = {
    'Max Score': [3,7,7,5,4,7,4,4,4,4,3,8,8,8,8,8,8],
    'Sub parameters': ['Greeting', 'Listening', 'Comprehension and Application', 'Appropriate Response', 'Paraphrasing', 'Educating customer/Setting Expectations', 'Professionalism', 'Empathy', 'Ownership/Assurance', 'Followed Hold Procedure', 'Closure', 'Sentence construction/Word order', 'Pronunciation/Chunking', 'Fluency & Lexical resource', 'Tone & Intonation', 'Rate of Speech', 'Diction'],
    'Grading': ['Yes']*17,
    'Score': [3,7,7,5,4,7,4,4,4,4,3,8,8,8,8,8,8]
}
DF1 = pd.DataFrame(data_df1)

data_df2 = {
    'Grade': ['Yes', 'Yes/Improvement', 'No'],
    '3': [3,1,0],
    '7.0': [7.0,4.0,0.0],
    '4.0': [4.0,2.0,0.0],
    '5.0': [5.0,3.0,0.0],
    '2.0': [2.0,1.0,0.0],
    '8.0': [8.0,4.0,0.0]
}
DF2 = pd.DataFrame(data_df2)

# 预处理DF2：把列名转成整数（DF1的Score是整数，避免类型不匹配）
DF2.columns = ['Grade'] + [int(float(col)) for col in DF2.columns[1:]]
# 把Grade设为索引，方便快速查找对应得分
grade_score_map = DF2.set_index('Grade')

2. 为DF1添加新字段

# 写个小函数，根据每行的Score值提取不同Grade的得分
def fetch_grade_scores(row):
    current_score = row['Score']
    return pd.Series({
        'Grading_Yes': 'Yes',
        'Score_Yes': grade_score_map.loc['Yes', current_score],
        'Grading_Y/I': 'Y/I',
        'Score_Y/I': grade_score_map.loc['Yes/Improvement', current_score],
        'Grading_No': 'No',
        'Score_No': grade_score_map.loc['No', current_score]
    })

# 把新字段和DF1的Sub parameters列合并
result_df = pd.concat([DF1[['Sub parameters']], DF1.apply(fetch_grade_scores, axis=1)], axis=1)

3. 查看结果

print(result_df.head())

输出示例（前5行）：

Sub parameters Grading_Yes  Score_Yes Grading_Y/I  Score_Y/I Grading_No  Score_No
0             Greeting         Yes          3         Y/I          1         No         0
1            Listening         Yes          7         Y/I          4         No         0
2  Comprehension and Application         Yes          7         Y/I          4         No         0
3       Appropriate Response         Yes          5         Y/I          3         No         0
4                Paraphrasing         Yes          4         Y/I          2         No         0

关键说明 #

• 预处理DF2的列名是核心：DF2的列名是带小数点的字符串（比如"7.0"），而DF1的Score是整数，转成一致的整数类型才能准确匹配；
• 用索引构建映射表后，loc可以快速定位到对应Grade和Score的单元格值，效率比循环高；
• 最后用concat合并列，保留需要的Sub parameters字段，同时新增所有Grade相关的得分字段。

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