Instructions
Requirements and Specifications
- The python program must have a login page. The credentials of the users must be stored in a MS EXCEL file. You must create a list of users in an EXCEL FILE who can log in the system.
- Upon Login, your program must display a sample Welcome Screen as shown below:
- Display all lecturers
- Display all students
- Display list of lecturers with their respective modules.
- Search a student, using his/her name.
- Generate a dictionary of MARKS with RANDOM VALUES.
- Display each student with the his/her marks obtained in each module.
- Using the dictionary created in section 4, store all these details in a new spreadsheet in the EXCEL FILE. The new spreadsheet is called MARKS.
- Compute the average marks obtained by each student.
- Find the student who scored the highest set of marks, that is, to find the student who has the highest average mark.
- Your program should allow the user to add new modules, new students and new lecturers. This new information is all stored in their respective worksheet in the file File_coursework2022.
- As you can see, the Welcome screen displays a menu of options. Each option is a functionality that your program must do.
- A python file named File_coursework2022 is given to you. The file contains three (3) spreadsheets:
- data_modules contains details of modules in a course.
- data_students contains details of students.
- data_lecturers contains details of four (4) lecturers. Each lecturer will teach a series of module(s).
- Consider menu 5.
- Create a dictionary with its key as TUPLE and value as a LIST.
- The remaining sections depends on the dictionary created in menu 4.
- Your program will stop ONLY when the user wants to QUIT the program.
- After the program executes a specific option entered by the user, the program must display the menu again.
- A short video is annexed with this document for demonstration purposes.
- This coursework should be done individually.
INFORMATION SYSTEM FOR UDM EXAMINATION UNIT
Menu:
The user selects a specific option by entering the corresponding number next to each option, and the program will process and display the information requested.
This menu is related to marks of each student. Each student did 3 ICAs for each module. Each ICA carries 100 marks. This means that a module will carry 300 marks.
The dictionary must have the following format:
( 'student code', 'module code' ) : [ 0, 0, 0 ]
Now, since there are 10 students and 5 modules, this means that the dictionary for marks must have 50 entries.
Each student will have 5 entries as each student will score marks for each module.
The default marks scored by a student for a module is zero (0).
Generate random values for each ICA of each module. The random values should be inserted at runtime.
Example:
( 's1', 'm1' ) : [ 72, 18, 26 ]
This means that your program must generate 150 random values that is 3 random values per student. The random values should be between 0 and 100.
Display the resulting dictionary for marks.
Source Code
import numpy as np
import pandas as pd
import random
# Define the name of the file containing the login credentials
LOGIN_CREDENTIALS_FILE = 'LoginCredentials.xlsx'
# Define the name of the file containung students, lectures, etc
DATA_FILE = 'File_coursework2022.xlsx'
def menu():
"""
This function will display the menu to user and will prompt for an option
The function will keep prompting user until s/he enters a valid option
:return: int
"""
print("INFORMATION SYSTEM FOR UDM EXAMINATION UNIT")
print("Menu:")
print("1) Display all lecturers")
print("2) Display all students")
print("3) Display list of lecturers with their respective modules")
print("4) Search a student, using his/her name")
print("5) Generate a dictionary of MARKS with RANDOM VALUES")
print("6) Display each student with the his/her marks obtained in each module")
print("7) Save marks to a file")
print("8) Compute average marks obtained by each student")
print("9) Find student who scored the highest set of marks")
print("10) Add module")
print("11) Add student")
print("12) Add lecturer")
print("13) Exit")
# Now ask for option
while True:
try:
option = int(input("Enter option: "))
if option >= 1 and option <= 13:
return option
else:
print("Please enter a valid menu option between 1 and 13.")
except:
print("Please enter a valid menu option.")
if __name__ == '__main__':
# Variable to know if the user is logged in or not
logged_in = False
# Read login credentials
login_credentials = pd.read_excel(LOGIN_CREDENTIALS_FILE).to_numpy().tolist()
# Read Modules
modules = pd.read_excel(DATA_FILE, 'data_modules').to_numpy().tolist()
# Read Students
students = pd.read_excel(DATA_FILE, 'data_students').to_numpy().tolist()
# Read LEcturers
lecturers = pd.read_excel(DATA_FILE, 'data_lectures').to_numpy().tolist()
# Now, create the dictionary that will contains (student,module) as keys
student_grades = dict()
for student in students:
student_code = student[0]
for module in modules:
module_code = module[0]
key = (student_code, module_code)
# A the beginning, the grades are zero
student_grades[key] = [0, 0, 0]
#student_grades[key] = [random.randint(0,100) for i in range(3)]
# Create a dict that will store the averages of each student
averages = dict()
# Begin with program
running = True
while running:
if not logged_in: # Log in
username = input('Enter username: ')
password = input('Enter password: ')
# Check if username and password exists
for user in login_credentials:
if user[0] == username and user[1] == password:
logged_in = True
break
if not logged_in: # If the variable is still false, it means user entered an invalid username/password
print("Username and/or password are incorrect.")
else:
# Display menu
option = menu()
print()
if option == 1: # Display lecturers
print("{:<10s} {:>10s} {:>10s}".format("Lecturer ID", "Lecturer Name", "Module Code"))
print("{:<10s} {:>10s} {:>10s}".format("-----------", "-------------", "-----------"))
for lecturer in lecturers:
print("{:<10} {:>10s} {:>10s}".format(lecturer[0], lecturer[1], lecturer[2]))
elif option == 2: # Display students
print("{:<5s} {:>15s}".format("Student Code", "Student Full Name"))
print("{:<5s} {:>15s}".format("-----------", "-----------------"))
for student in students:
print("{:<5} {:>15s}".format(student[0], student[1]))
elif option == 3: # Lecturer with module
print("{:<10s} {:>15s} {:>40s}".format("Lecturer ID", "Lecturer Name", "Module"))
print("{:<10s} {:>15s} {:>40s}".format("-----------", "-------------", "------"))
for lecturer in lecturers:
# Get module codes
modules_code = lecturer[2].split(",")
for module_code in modules_code:
# Get module name with this code
mod_name = ''
for module in modules:
if module[0] == module_code:
mod_name = module[1]
break
print("{:<10} {:>15s} {:>40s}".format(lecturer[0], lecturer[1], mod_name))
elif option == 4: # Search student using name
name = input('Enter student name: ')
found = False
for student in students:
if student[1] == name:
print("{:<5s} {:>15s}".format("Student Code", "Student Full Name"))
print("{:<5s} {:>15s}".format("-----------", "-----------------"))
print("{:<5s} {:>15s}".format(student[0], student[1]))
found = True
break
if not found: # Not student with that name found
print("There is no student with that name.")
elif option == 5: # generate random marks
for student in students:
student_code = student[0]
for module in modules:
module_code = module[0]
key = (student_code, module_code)
student_grades[key] = [random.randint(0,100) for i in range(3)]
print("Random marks generated.")
elif option == 6: # Display each student with marks
print("{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("Student Name", "Module Name", "Mark 1", "Mark 2", "Mark 3"))
print("{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("------------", "-----------", "------", "------", "------"))
for key in student_grades:
student_code, module_code = key
marks = student_grades[key]
# Get student name
student_name = ''
for student in students:
student_name = student[1]
break
# Get module name
module_name = ''
for module in modules:
if module[0] == module_code:
module_name = module[1]
break
print("{:<15s} {:>15s} {:>10d} {:>10d} {:>10d}".format(student_name, module_code, marks[0], marks[1], marks[2]))
elif option == 7: # Save to file
# Create a dataframe
data = list()
for key in student_grades:
student_code, module_code = key
marks = student_grades[key]
data.append([student_code, module_code, marks[0], marks[1], marks[2]])
df = pd.DataFrame(data = data, columns = ['Student Code', 'Module Code', 'Mark 1', 'Mark 2', 'Mark 3'])
df.to_excel('MARKS.xlsx', index=False)
print("Data saved to MARKS.xlsx")
elif option == 8: # Compute average marks
print(
"{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("Student Name", "Module Name", "Mark 1", "Mark 2",
"Mark 3", "Average"))
print(
"{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("------------", "-----------", "------", "------",
"------", "-------"))
for key in student_grades:
student_code, module_code = key
marks = student_grades[key]
# Get student name
student_name = ''
for student in students:
if student[0] == student_code:
student_name = student[1]
break
# Get module name
module_name = ''
for module in modules:
if module[0] == module_code:
module_name = module[1]
break
avg = sum(marks)/3
averages[student_name] = avg
print("{:<15s} {:>15s} {:>10d} {:>10d} {:>10d} {:>10.2f}".format(student_name, module_code, marks[0], marks[1], marks[2], avg))
elif option == 9: # Find student who scores highest set
if len(averages) > 0:
# get index of max average
index = np.argmax(list(averages.values()))
avg = list(averages.values())[index]
student_name = list(averages.keys())[index]
# Display
print("The student with the highest average is {0} with an average of {1:.2f}".format(student_name, avg))
else:
print("You must compute the student averages first (option 8).")
elif option == 10: # Add module
module_code = input('Enter module code: ')
module_name = input('Enter module name: ')
modules.append([module_code, module_name])
print(f"Module {module_name} ({module_code}) added.")
# Save this new info into File_coursework2022.xlsx
with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a', if_sheet_exists="replace") as writer:
# Now, write sheets
data_modules = pd.DataFrame(data=modules, columns = ['Module Code', 'Module Name'])
data_modules.to_excel(writer, sheet_name = 'data_modules', index = False)
data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])
data_students.to_excel(writer, sheet_name = 'data_students', index=False)
data_lectures = pd.DataFrame(data=lecturers, columns=['Lecturer ID', 'Lecturer Name', 'Module_code'])
data_lectures.to_excel(writer, sheet_name = 'data_lectures', index=False)
writer.save()
elif option == 11: # Add student
student_code = input('Enter student code: ')
student_name = input('Enter student name: ')
# Add student
students.append([student_code, student_name])
# Now, add grades for this student in the marks dictionary
for module in modules:
module_code = module[0]
key = (student_code, module_code)
student_grades[key] = [0, 0, 0]
print(f"Student {student_name} ({student_code}) added.")
# Save this new info into File_coursework2022.xlsx
with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a',
if_sheet_exists="replace") as writer:
# Now, write sheets
data_modules = pd.DataFrame(data=modules, columns=['Module Code', 'Module Name'])
data_modules.to_excel(writer, sheet_name='data_modules', index=False)
data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])
data_students.to_excel(writer, sheet_name='data_students', index=False)
data_lectures = pd.DataFrame(data=lecturers,
olumns=['Lecturer ID', 'Lecturer Name', 'Module_code'])
data_lectures.to_excel(writer, sheet_name='data_lectures', index=False)
writer.save()
elif option == 12: # Add Lecturer
lecturer_id = input('Enter lecturer id: ')
lecturer_name = input('Enter lecturer name: ')
lecturer_modules = input('Enter module codes sepparated by comma (e.g: m1, m2): ')
# Add lecturer
lecturers.append([lecturer_id, lecturer_name, lecturer_modules])
print(f'Lecturer {lecturer_name} ({lecturer_id}) for modules {lecturer_modules} added.')
# Save this new info into File_coursework2022.xlsx
with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a',
if_sheet_exists="replace") as writer:
# Now, write sheets
data_modules = pd.DataFrame(data=modules, columns=['Module Code', 'Module Name'])
data_modules.to_excel(writer, sheet_name='data_modules', index=False)
data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])
data_students.to_excel(writer, sheet_name='data_students', index=False)
data_lectures = pd.DataFrame(data=lecturers,
columns=['Lecturer ID', 'Lecturer Name', 'Module_code'])
data_lectures.to_excel(writer, sheet_name='data_lectures', index=False)
writer.save()
elif option == 13: # Exit
print("Good bye!")
running = False
print()