Calculations on extracted data from excel using python

Question

i want to calculate the average score and GPA of grades i extract from an excel file. currently, i'm able to grab the column from the excel file and i figured out how to create lists based on manual input. however, i would like to take the column of number grades from excel and use those as inputs (instead of manual input) in order to convert them into GPAs and calculate the cumulative GPA. i obviously want to skip the courses without a score available. how do i use the extracted data from excel in GPA calculation? i'm new to python and working with excel so anything helps. thank you

Course                      Scores
Art II                        93
Spanish II                   100
Algebra II Trig Honors        96
Christian Scriptures          99
Chemistry   
American History Honors       87
Phys Ed
Chemistry II                  92

python code

df3 = pd.read_excel('file.xlsx')
scores = df3[['Scores']]
print(scores)

letters = []
points = []
score = float(input("enter: "))
if(score < 101):
    letters.append('A')
    points.append(4.0)
elif(score < 90):
    letters.append('B')
    points.append(3.0)
elif(score < 80):
    letters.append('C')
    points.append(2.0)
elif(score < 70):
    letters.append('D')
    points.append(1.0)
elif(score < 60):
    letters.append('F')
    points.append(0.0)

print(letters)
print(points)

Reverse the order of your buckets, start at 60, then 70, etc. Currently all numbers are less than 101, so the will go into that bucket first. — Trenton McKinney
– Trenton McKinney, Commented Aug 5, 2020 at 6:54

jezrael · Accepted Answer · 2020-08-05 07:06:10Z

5

I believe you need cut with mean, because categorical is necessary convert to floats:

bins = [0,60,70,80,90,101]
df3['letters'] = pd.cut(df3['Scores'], bins=bins, labels=list('FDCBA'))
df3['points'] = pd.cut(df3['Scores'], bins=bins, labels=[0.0,1.0,2.0,3.0,4.0])

mean = df3['points'].astype(float).mean()
print (mean)
3.8333333333333335

print (df3)
                    Course  Scores letters points
0                   Art II    93.0       A    4.0
1               Spanish II   100.0       A    4.0
2   Algebra II Trig Honors    96.0       A    4.0
3     Christian Scriptures    99.0       A    4.0
4                Chemistry     NaN     NaN    NaN
5  American History Honors    87.0       B    3.0
6                  Phys Ed     NaN     NaN    NaN
7             Chemistry II    92.0       A    4.0

edited Aug 5, 2020 at 7:06

answered Aug 5, 2020 at 6:58

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

J. Doe Over a year ago

this worked great but do you mind explaining the first three lines? i'll also need it to do the opposite (convert letter grades to scores and GPAs), so i think it would help

jezrael Over a year ago

@J.Doe - For first question cut is used for Bin values into discrete intervals. For second, opposite operation it is more problematic, because e.g. A is from 91 to 100, so what is expected output?

J. Doe Over a year ago

so i can't use the same logic here if i wanted to convert letter grades to numbers and GPAs? for example, A -> 90-100 / 4.0, B -> 89-80 / 3.0, C = 79-70 -> 2.0, etc.

jezrael Over a year ago

@J.Doe - I think my main problem is GPA, unfortuantely never use it. Can you give me formula, some more info?

jezrael Over a year ago

@J.Doe - OK, I think I got you. I think here the best is use map by dictionary like df3['points1'] = df3.letters.map({'A':4.0, 'B':3.0, 'C':2.0, 'D':1.0, 'F':0.0}), and similar for Scores like df3['Scores1'] = df3.letters.map({'A':'100-90', 'B':'89-80'}), add next values.

|

Collectives™ on Stack Overflow

Calculations on extracted data from excel using python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related