Question

Task description:

NOTE: - MUST BE ANSWERED IN PYTHON CODE FORMAT, USING PANDAS & DISPLAY A SCREEN SHOT OF THE UPDATED OUTPUT FROM YOUR CODE.

Please comment out code with description.

You are given a student result data file (result_withoutTotal.csv).

It has columns: ID: student id

Ass1 ~ Ass4: assignment scores (out of 100); weight of ass1, ass2, ass3 and ass4 is 5%, 15%, 5%, and 15%, respectively.

Exam: examination score (out of 120); weight is 60%.

Total score can be calculated using formula:

Total = 5%*(ass1+ass3) + 15%*(ass2+ass4) + 60%*exam

Read students’ result data from file result_withoutTotal.csv - (https://www.dropbox.com/s/w9r0yddyd79bh4n/result_withoutTotal.csv?dl=0)

add:

Total column: Total = 5%*(ass1+ass3) + 15%*(ass2+ass4) + 60%*exam.

The max Total score is 100. Reduce the Total to 100 if the calculated Total >100.

Final column: Final = Total score rounded to the nearest integer.

To pass the unit, a student must achieve at least 50 of the Total and 40% of Exam which is 48 out of 120 (or Total >= 50 and Exam >= 48).

If a student failed the hurdle (Exam >= 48), the max Final is 44. No change to Final score if Final <44.

Grade column: N (Final <=49.45), P (49.45 < Final <=59.45), C (59.45 < Final <=69.45), D (69.45 < Final <=79.45) and HD (79.45 < Final).

Display the following:

1. The result data file with the 3 new columns

2. The students with exam score < 48

3. The students with exam score > 100

////////////////////////////////////////////////////

NOTE: - MUST BE ANSWERED IN PYTHON CODE FORMAT, USING PANDAS & DISPLAY A SCREEN SHOT OF THE UPDATED OUTPUT.

Please comment out code with description.

Sample output​​​​:

students with exam score < 48 Ass1 Ass2 Ass3 Ass4 Exam Total Final Grade ID 44 44 3 74.3 54.4 63.0 63. 9 89.8 81.3 82.0 90.4 15 66.3 53.7 53.0 81.9 24 57.7 76.3 71.0 87.7 25 84.7 65.0 73.0 88.9 26 84.7 53.8 75.0 78.1 33 64.2 50. 18.0 0.0 42 81.5 43.8 0.0 0.0 44 71.961.376.0 94.5 47 50.0 71.3 56.0 93.8 54 76.1 50.0 50.0 50.0 60 73.9 53.2 74.0 95.9 78 52.9 53.2 50.0 50.0 31 43.21043N 37 56.545 30 44.305 35 52.035 34 51.370 44 36 49.370 11.610 10.645 38 53.565 44 34 50.465 33 41.105 41 34 50.160 36 42.225 42 ZZZZZZZZZZZZZ 11 44 students with exam score > 100 Ass1 Ass2 Ass Ass4 Exam Total Final Grade ID 11 96.6 93.6 100.0 100.0 98.0 96.0 97.3 100.0 102 106 100.0 100.0 100 100 HD HD 84

Answer 1

Here is the code you asked for.

# importing pandas as pd
import pandas as pd
df_csv = pd.read_csv('result_withoutTotal.csv')
tot=list()
fin=list()
grad=list()
#iterating through the each row in a dataframe
for row in df_csv.iterrows():
    #making it as list to more clear about indexes like we will series data at first index with all the attribute names like Ass1 and Ass2 ,Ass3 and Exams so we can access value with index and name of attribute
    a=list(row)
    #here a[1].Ass1 means accessing ass1 value from dataframe which is in first index of list
    total=((5/100)*(a[1].Ass1+a[1].Ass3))+ ((15/100)*(a[1].Ass2+a[1].Ass4))+((60/100)*a[1].Exam)
    if(total>100):
        total=100
    #finding the final
    if(total>=50 and a[1].Exam>=48):
        final=round(total)
    elif(total<=50 and a[1].Exam<=48):
        final=round(total)
    else:
        final=44
    #finding the grade
    if(final<=49.45):
        grade='N'
    elif(final>49.45 and final<=59.45):
        grade='P'
    elif(final>59.45 and final<=69.45):
        grade='C'
    elif(final>69.45 and final<=79.45):
        grade='D'
    else:
        grade='HD'
    #appending all the values to respective lists
    tot.append(total)
    fin.append(final)
    grad.append(grade)
df_csv['Total']=tot
df_csv['Final']=fin
df_csv['Grade']=grad
#we are making id as a index here
df_csv.set_index(['ID'], inplace = True)
#writing dataframe to csv
df_csv.to_csv('result_withoutTotal.csv', header=True, index=True)
df_res = df_csv[df_csv['Final'] < 48]
print("Students with marks < 48")
print(df_res)
print("Students with marks > 100")
df_res = df_csv[df_csv['Final'] >=100]
print(df_res)

ouputs:

N Students with marks < 48 Assl Ass Ass Ass4 Exam Total Final Grade ID 3 74.3 54.4 63.0 63.9 31 43.210 43.0 89.8 81.3 82.0 90.4 37 56.545 44.0 15 66.3 53.7 53.0 81.9 30 44.305 44.0 24 57.7 76.3 71.0 87.7 35 52.035 44.0 25 84.7 65.0 73.0 88.9 34 51.370 44.0 33 64.2 50.0 18.0 0.0 0 11.610 12.0 42 81.5 43.8 0.0 0.0 0 10.645 11.0 44 71.9 61.3 76.0 94.5 38 53.565 44.0 47 50.0 71.3 56.0 93.8 34 50.465 44.0 54 76.1 50.0 50.0 50.0 33 41.105 41.0 60 73.9 53.2 74.0 95.9 34 50.160 44.0 N 78 52.9 53.2 50.0 50.0 36 42.225 42.0 N Students with marks > 100 Assl Ass2 Ass Ass4 Exam Total Final Grade ID 11 96.6 100.0 98.0 97.3 102 100.0 100.0 HD 84 93.6 100.0 96.0 100.0 106 100.0 100.0 HD ZZZZZZZZZZ

csv before:

59 A B C D E F D Ass1 Ass2 Ass3 Ass4 Exam 1 89.1 50 85 88.9 65 2 95.1 82.5 90.5 94.5 52 3 74.3 54.4 63 63.9 31 4 89.8 81.3 82 90.4 37 5 91.3 98.8 92.5 95.9 6 83.9 82.5 89 98.6 7 81. 950 68.5 95.4 8 50 54.9 50 87.7 9 90.5 65.9 50 72.2 63 10 89 89.9 94 90.3 84 11 96.6 100 98 97.3 102 12 58.8 67.5 72 75.4 51 13 74.7 78.5 50 86.2 14 87.7 8057 73.6 52 15 66.3 53.7 53 81.9 30 16 82.7 77.2 73 95.9 50 17 97.5 96.2 83.5 93.8 18 70.2 86.1 57 76.9 52 19 78.1 84.8 61 89 20 70. 9 50 60 64.6 21 87 59.8 52 98.5 22 91.4 74.7 78 80.6 23 67.9 57 77 75.3 24 57.7 76.3 71 87.7 25 84.7 65 73 88.9 26 84.7 53.8 75 78.1 36 27 88.5 91.5 90 87.5 65 28 79 52.5 65 61.6 71 29 89.2 65 74 94.4 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 28888882 8888888::8289288 55

csv after:

HAWN 7 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 A B C D E F G H D Ass1 Ass2 Ass3 Ass4 Exam Total Final Grade 1 89.1 50 85 88.9 65 68.54 69 C 2 95.1 82.5 90.5 94.5 52 67.03 67C 3 74.3 54.4 63 63.9 31 43.21 43N 4 89.8 81.3 82 90.4 37 56.545 44N 5 91.3 98.8 92.5 95.9 79 85.795 86 HD 6 83.9 82.5 89 98.6 68 76.61 77D 7 81. 9 50 68.5 95.4 59 64.73 65 C 8 50 54.9 50 87.7 51 56.99 57 P 9 90.5 65.9 50 72.2 63 65.54 66 C 10 89 89.9 94 90.3 84 86.58 87 HD 11 96.6 100 98 97.3 102 100 100 HD 12 58.8 67.5 72 75.4 51 58.575 59 P 13 74.7 78.5 50 86.2 66 70.54 71D 14 87.7 80 57 73.6 52 61.475 61 C 15 66.3 53.7 53 81.9 30 44.305 44N 16 82.7 77.2 73 95.9 50 63.75 64C 17 97.5 96.2 83.5 93.8 55 70.55 71D 18 70.2 86.1 57 76.9 52 62.01 62 C 19 78.1 84.8 61 89 68 73.825 74D 20 70.9 50 60 64.6 51 54.335 54 P 21 87 59.8 52 98.5 69 72.095 72 D 22 91.4 74.7 78 80.6 70 73.765 74D 23 67.9 57 77 75.3 59 62.49 62 C 24 57.7 76.3 71 87.7 35 52.035 44 N 25 84.7 65 73 88.9 34 51.37 44N 26 84.7 53.8 75 78.1 36 49.37 49 N 27 88.5 91.5 90 87.5 65 74.775 75 D 28 79 52.5 65 61.6 71 66.915 67 C 29 89.2 65 74 94.4 61 68.67 69 C

Hope you like it,if you have any doubts let me know in comments, thank you