CODEWITHSUNDEEP
pythonexcelpandas
J. Doe
J. Doe

Reputation: 269

Calculations on extracted data from excel using python

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)

Upvotes: 3

Views: 892

Answers (1)

jezrael
jezrael

Reputation: 862511

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

Upvotes: 5

Related Questions