0

I’m working on a GPA calculator and I have a list of each grade a student has in their classes. I need to convert the letter grade (A+, B-, etc.) to it’s corresponding numerical value (4.3, 2.7, etc.) this is what i have so far:

import pandas as pd
import openpyxl

df = pd.read_excel('/Users/mac/Downloads/Data Doc.xlsx')

values = {'A+':'4.3', 'A':'4', 'A-':'3.7', 'B+':'3.3', 'B':'3', 'B-':'2.7', 'C+':'2.3', 'C':'2', 'C-':'1.7', 'D+':'1.3', 'D':'1', 'D-':'.7', 'F':'0'}
l = df.values.tolist()

Thank you for the help in advance

Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
user19066051
  • 3
  • 1

1 Answers1

1

You can use Series.map to generate numeric grades based on the alpha ones. For example:

import pandas as pd
df = pd.DataFrame({ 'name' : ['John', 'Ellie', 'Bill'], 'grade': ['A', 'A+', 'C-'] } )
values = {'A+':'4.3', 'A':'4', 'A-':'3.7', 'B+':'3.3', 'B':'3', 'B-':'2.7', 'C+':'2.3', 'C':'2', 'C-':'1.7', 'D+':'1.3', 'D':'1', 'D-':'.7', 'F':'0'}
df['numgrade'] = df['grade'].map(values)

Output:

    name grade numgrade
0   John     A        4
1  Ellie    A+      4.3
2   Bill    C-      1.7

If you don't want to keep the alpha values, you can simply use replace:

df = pd.DataFrame({ 'name' : ['John', 'Ellie', 'Bill'], 'class 1': ['A', 'A+', 'C-'], 'class 2': ['A', 'B+', 'C+'] } )
values = {'A+':'4.3', 'A':'4', 'A-':'3.7', 'B+':'3.3', 'B':'3', 'B-':'2.7', 'C+':'2.3', 'C':'2', 'C-':'1.7', 'D+':'1.3', 'D':'1', 'D-':'.7', 'F':'0'}
df = df.replace(values)

This will replace across the entire dataframe. Output:

    name class 1 class 2
0   John       4       4
1  Ellie     4.3     3.3
2   Bill     1.7     2.3

Otherwise, you could iterate over the grade columns (using filter to select only the ones with class at the beginning) e.g.

df = pd.DataFrame({ 'name' : ['John', 'Ellie', 'Bill'], 'class 1': ['A', 'A+', 'C-'], 'class 2': ['A', 'B+', 'C+'] } )
values = {'A+':'4.3', 'A':'4', 'A-':'3.7', 'B+':'3.3', 'B':'3', 'B-':'2.7', 'C+':'2.3', 'C':'2', 'C-':'1.7', 'D+':'1.3', 'D':'1', 'D-':'.7', 'F':'0'}
for col in df.filter(regex='^class').columns:
    df[col + ' num'] = df[col].map(values)

Output:

    name class 1 class 2 class 1 num class 2 num
0   John       A       A           4           4
1  Ellie      A+      B+         4.3         3.3
2   Bill      C-      C+         1.7         2.3
Nick
  • 138,499
  • 22
  • 57
  • 95