Question

Hi - Some help create a short program that uses a pandas DataFrame (solution MUST use a DataFrame) to do the following. Please include comments /notes to explain what is going on. Solution does not need to define functions

two csv files are included (grades01.csv): raw marks of students taking an examination; and a further csv file (rubric01.csv) summarizing the maximum mark available for each of the six questions.

Structure of grades01:

Student ID,Question 1,Question 2,Question 3,Question 4,Question 5,Question 6

205842,6.5,6.5,9.5,5.5,3.5,9.5

……..etc...

Structure of rubric01.csv

Question,Max score

Question 1, 10

……. etc...

task is to write a small program that loads the data into Python and displays some information about the data.

Task details

• Begin by prompting the user to enter a csv file containing the students' IDs and their raw mark for each of six questions, with the prompt Enter grades file:
• Program should then prompt the user to enter a csv file containing details of the maximum mark available for each of the questions, with the prompt Enter rubric file:
• Your program should then prompt the user to select one of five options with the menu:

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice:

• If the user selects 1, the program should display, by printing a pandas DataFrame, the mean score for each of the questions (see Sample interactions)
• If the user selects 2, the program should display, by printing a pandas DataFrame, the 25%, 50%, and 75% quantiles for each of the questions (see Sample interactions)
• If the user selects 3, the program should display, by printing a pandas DataFrame, the sum of the scores for each Student ID (see Sample interactions)
• If the user selects 4, the program should display, by printing a pandas DataFrame, the percentage grade each student obtained for each question. The percentage grade is given by:

percentage_grade = 100 * raw_grade / max_score

where raw_grade is the mark given in the grades file, and max_score is given in the rubric file.

• If the user selects 5, the program should display, by printing a pandas DataFrame, the Student IDs, arranged by:
  • Decreasing average percentage grade, and, if equal,
  • Increasing Student ID

If all questions have the same maximum score (as in rubric01.csv) then sorting by average percentage grade is the same as sorting by sum of scores.

• After printing the user selected option, the program should end

If printing out your DataFrame results in ellipses (...), [especially when testing] try using the to_string() method, e.g.: print(df.to_string())

• assume that
  • valid entries are always made
  • the grades files and rubric files follow the same structure (i.e. six questions, fifty students, data in floats rounded to 1 d.p.)
  • the outputs from the built-in pandas functions do not need formatting

Output expected (in terms of column headers etc):

Enter grades file: grades01.csv

Enter rubric file: rubric01.csv

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice: 1

            Mean

Question 1 x.xx

Question 2 y.xx

Question 3 x.xx

Question 4 6.11

Question 5 6.69

Question 6 6.74

Enter grades file: grades01.csv

Enter rubric file: rubric01.csv

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice: 2

              25% Median    75%

Question 1 4.500    7.00 8.500

Question 2 5.000    6.75 8.375

Question 3 5.125    6.00 8.375

Question 4 5.000    6.00 7.375

Question 5 4.625    7.00 8.500

Question 6 5.125    7.00 8.500

Enter grades file: grades01.csv

Enter rubric file: rubric01.csv

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice: 3

    Student ID Total

0       205842   41.0

1       280642   44.5

2       289179   46.0

3       264178   33.0

4       215754   38.5

5       219697   37.5

6       288204   43.5

...

Enter grades file: grades01.csv

Enter rubric file: rubric01.csv

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice: 4

    Student ID Question 1 Question 2 Question 3 Question 4 Question 5 Question 6

0       205842        65.0        65.0        95.0        55.0        35.0        95.0

1       280642        85.0        95.0        35.0        95.0        40.0        95.0

2       289179        70.0        95.0        90.0        95.0        70.0        40.0

3       264178        90.0        35.0        55.0        50.0        55.0        45.0

4       215754        55.0        95.0        50.0        50.0        40.0        95.0

5       219697        50.0        75.0        80.0        45.0        90.0        35.0

6       288204        40.0        55.0        65.0        95.0        90.0        90.0

...

Enter grades file: grades01.csv

Enter rubric file: rubric01.csv

1. Print question average (mean)

2. Print question average (quantiles)

3. Print student totals

4. Print student percentages

5. Print student ranking

Please enter your choice: 5

    Student ID

10      286523

45      291465

18      276254

24      283567

2       289179

30      250254

1       280642

...

Answer 1

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd # import pandas library


# In[2]:


grade_file=input('please enter grade file:') # take input name of the grade file
rubric_file=input('please enter rubric file:') # take input name of the rubric file


# In[3]:


df_grade=pd.read_csv("C:/Users/RV/"+grade_file) # open grade and rubric file
df_rubric=pd.read_csv("C:/Users/RV/"+rubric_file)


# In[5]:


print("1. Print question average (mean) \n2. Print question average (quantiles)\n3. Print student totals\n4. Print student percentages\n5. Print student ranking")
flag=input("Please enter your choice: ")


# In[6]:


df_mean=pd.DataFrame(columns=['Question','Mean'])
if(flag=='1'):
    list1=[]
    list2=[]
    for i in range(1,7):
        list1.append('Question '+str(i))
        list2.append(df_grade['Question '+str(i)].mean())
    df_mean['Question']=list1
    df_mean['Mean']=list2
    print(df_mean)
    


# In[7]:


df_quantile=pd.DataFrame(columns=['Question','25%','Median','75%'])
if(flag=='2'):
    list1=[]
    list2=[]
    list3=[]
    list4=[]
    for i in range(1,7):
        list1.append('Question '+str(i))
        list2.append(df_grade['Question '+str(i)].quantile(q=0.25))
        list3.append(df_grade['Question '+str(i)].quantile(q=0.5))
        list4.append(df_grade['Question '+str(i)].quantile(q=0.75))
    df_quantile['Question']=list1
    df_quantile['25%']=list2
    df_quantile['Median']=list3
    df_quantile['75%']=list4
    print(df_quantile)
    


# In[8]:


df_sum=pd.DataFrame(columns=['Student ID','sum'])
if(flag=='3'):
    list1=[]
    for i in range(len(df_grade['Student ID'])):
        list1.append(df_grade.iloc[i][1]+df_grade.iloc[i][2]+df_grade.iloc[i][3]+df_grade.iloc[i][4]+df_grade.iloc[i][5])
    df_sum['Student ID']=df_grade['Student ID']
    df_sum['sum']=list1
    print(df_sum)
    


# In[9]:


import copy
df_per=copy.copy(df_grade)
if(flag=='4'):
    df_per.iloc[:,1:]=df_per.iloc[:,1:]*100/10 # as max score of all question is 10
    print(df_per)


# In[12]:


df_sort_by_sum=copy.copy(df_sum)
if(flag=='5'):
    df_sort_by_sum=df_sort_by_sum.sort_values(by=['sum'],ascending=False) # as all question has same max score in rubric file
    print(df_sort_by_sum)

I have make a grade file and rubric file to do the task and here is the result I get.

Output:

1st Run:

please enter grade file:grades01.csv
please enter rubric file:rubric01.csv

1. Print question average (mean) 
2. Print question average (quantiles)
3. Print student totals
4. Print student percentages
5. Print student ranking
Please enter your choice: 1

Question      Mean
0  Question 1  6.533333
1  Question 2  6.833333
2  Question 3  7.041667
3  Question 4  6.333333
4  Question 5  5.958333
5  Question 6  8.041667

2nd Run:

please enter grade file:grades01.csv
please enter rubric file:rubric01.csv

1. Print question average (mean) 
2. Print question average (quantiles)
3. Print student totals
4. Print student percentages
5. Print student ranking
Please enter your choice: 2

  Question    25%  Median    75%
0  Question 1  5.375    6.45  8.125
1  Question 2  5.500    7.00  8.500
2  Question 3  6.250    7.25  8.500
3  Question 4  5.750    6.50  7.000
4  Question 5  5.000    5.50  7.250
5  Question 6  6.750    9.00  9.500

3rd Run:

please enter grade file:grades01.csv
please enter rubric file:rubric01.csv

1. Print question average (mean) 
2. Print question average (quantiles)
3. Print student totals
4. Print student percentages
5. Print student ranking
Please enter your choice: 3

 Student ID   sum
0       205842  28.5
1       205843  32.5
2       205844  33.5
3       205845  34.5
4       205846  31.5
5       205847  33.5
6       205848  28.0
7       205849  37.0
8       205850  38.0
9       205851  38.0
10      205852  25.0
11      205853  32.4

4th Run:

please enter grade file:grades01.csv
please enter rubric file:rubric01.csv

1. Print question average (mean) 
2. Print question average (quantiles)
3. Print student totals
4. Print student percentages
5. Print student ranking
Please enter your choice: 4

Student ID Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 0 205842 65.0 85.0 70.0 50 35.0 90.0 1 205843 75.0 75.0 75.0 50 50.0 50.0 2 205844 55.0 85.0 85.0 80 50.0 50.0 3 205846 35.0 95.0 65.0 70 80.0 70.0 4 205840 85.0 35.0 45.0 80 70.0 60.0 5 205847 95.0 55.0 55.0 60 70.0 80.0 6 205848 80.0 65.0 35.0 40 60.0 95.0 7 205849 60.0 75.0 95.0 60 80.0 95.0 00 205850 50.0 85.0 85.0 70 90.0 95.0 9 205851 90.0 95.0 75.0 70 50.0 95.0 10 205852 30.0 35.0 65.0 70 50.0 95.0 11 205853 04.0 55.0 95.0 80 30.0 90.0

5th Run:

please enter grade file:grades01.csv
please enter rubric file:rubric01.csv

1. Print question average (mean) 
2. Print question average (quantiles)
3. Print student totals
4. Print student percentages
5. Print student ranking
Please enter your choice: 5

 Student ID   sum
8       205850  38.0
9       205851  38.0
7       205849  37.0
3       205845  34.5
2       205844  33.5
5       205847  33.5
1       205843  32.5
11      205853  32.4
4       205846  31.5
0       205842  28.5
6       205848  28.0
10      205852  25.0