Convert list of phrases into corresponding number-CodePudding

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

CodePudding user response：

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