Pandas Column Values Between Values From Another Dataframe Column

I have two pandas data-frames as follows: import pandas as pd import numpy as np import string size = 5 student_names = [''.join(np.random.choice(list(string.ascii_lowercase), s

Solution 1:

Use cut with dynamic values bins and labels from df2 columns, also is added right=False for left closed bins:

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 new0           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+

Like @Henry Yik commented here is possible use merge_asof:

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+        501           lckw     53  D+            D+        502           dkfa     53  D+            D+        503           nsep     61   C             C        604           wbar     62   C             C        605           apzt     67  C+            C+        656           pjao     68  C+            C+        657           nccg     70  B-            B-        708           lmug     71  B-            B-        709           cbmi     72  B-            B-        7010          ntqg     75   B             B        7511          hjdu     77   B             B        7512          fxzs     84  B+            B+        8013          ouhl     89  A-            A-        8514          whbl     91   A             A        9015          cuhc     91   A             A        9016          erxd     94   A             A        9017          upyq     98  A+            A+        9518          rtkz     99  A+            A+        9519          gbvw     99  A+            A+        95