在另一个数据帧列的值之间切换列值

Question

我有两个数据帧，如下所示：

import pandas as pd 
import numpy as np
import string

size = 5

student_names = [''.join(np.random.choice(list(string.ascii_lowercase), size=4)) for i in range(size)]
marks = list(np.random.randint(50, high=100, size=size))
df1 = pd.DataFrame({'Student Names': student_names, 'Total': marks})
grade_leters = ['A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D',
                 'D-', 'F']
grade_minimum_value =  [95, 90, 85, 80, 75, 70, 65, 60, 55, 50, 45, 40, 0]
df2 = pd.DataFrame({'Grade Letters': grade_leters, 'Minimums': grade_minimum_value})

df1

  Student Names  Total
0          cjpv     83
1          iywm     98
2          jhhb     87
3          qwau     70
4          ppai     82

df2

   Grade Letters  Minimums
0             A+        95
1              A        90
2             A-        85
3             B+        80
4              B        75
5             B-        70
6             C+        65
7              C        60
8             C-        55
9             D+        50
10             D        45
11            D-        40
12             F         0

我想把成绩字母作为一个新的列给df1。例如，总成绩为83分的学生cjpv将收到B+的成绩字母，因为83分介于80分（含）和85分（不含）之间。你知道吗

所需输出如下。你知道吗

  Student Names  Total  Grade
0          cjpv     83   B+
1          iywm     98   A+
2          jhhb     87   A-
3          qwau     70   B-
4          ppai     82   B+

提前谢谢。我很抱歉，如果有类似的问题，但是，我找了很长时间后没有找到一个。你知道吗

Answer 1

将^{}与动态值bin和df2列中的标签一起使用，还为左关闭的bin添加了right=False：

np.random.seed(123)
size = 20

student_names = [''.join(np.random.choice(list(string.ascii_lowercase), size=4)) for i in range(size)]
marks = list(np.random.randint(50, high=100, size=size))
df1 = pd.DataFrame({'Student Names': student_names, 'Total': marks})
grade_leters = ['A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D',
                 'D-', 'F']
grade_minimum_value =  [95, 90, 85, 80, 75, 70, 65, 60, 55, 50, 45, 40, 0]
df2 = pd.DataFrame({'Grade Letters': grade_leters, 'Minimums': grade_minimum_value})

---

df2 = df2.sort_values('Minimums')
df1['new'] = pd.cut(df1['Total'], 
                    bins=df2['Minimums'].tolist() + [np.inf], 
                    labels=df2['Grade Letters'],
                    right=False)
print (df1)

   Student Names  Total new
0           nccg     70  B-
1           rtkz     99  A+
2           wbar     62   C
3           pjao     68  C+
4           apzt     67  C+
5           oeaq     51  D+
6           erxd     94   A
7           cuhc     91   A
8           upyq     98  A+
9           hjdu     77   B
10          gbvw     99  A+
11          cbmi     72  B-
12          dkfa     53  D+
13          lckw     53  D+
14          nsep     61   C
15          lmug     71  B-
16          ntqg     75   B
17          ouhl     89  A-
18          whbl     91   A
19          fxzs     84  B+

就像@Henry Yik在这里评论的可能用法^{}：

df1 = pd.merge_asof(df1.sort_values('Total'), df2, left_on='Total', right_on='Minimums')
print (df1)
   Student Names  Total new Grade Letters  Minimums
0           oeaq     51  D+            D+        50
1           lckw     53  D+            D+        50
2           dkfa     53  D+            D+        50
3           nsep     61   C             C        60
4           wbar     62   C             C        60
5           apzt     67  C+            C+        65
6           pjao     68  C+            C+        65
7           nccg     70  B-            B-        70
8           lmug     71  B-            B-        70
9           cbmi     72  B-            B-        70
10          ntqg     75   B             B        75
11          hjdu     77   B             B        75
12          fxzs     84  B+            B+        80
13          ouhl     89  A-            A-        85
14          whbl     91   A             A        90
15          cuhc     91   A             A        90
16          erxd     94   A             A        90
17          upyq     98  A+            A+        95
18          rtkz     99  A+            A+        95
19          gbvw     99  A+            A+        95