GymInf/analyseNotes.py at master · danjane/GymInf · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
import examNotes
import xlsxwriter
import linkComments
import os
import datetime


def first_semester(date_string):
    date = datetime.datetime.strptime(date_string, "%d%b%Y")
    if date.month < 6:
        current_year = date.year
    else:
        current_year = date.year + 1
    jan31 = datetime.datetime(current_year, 1, 20)
    return date < jan31


def dump_all(cfg_path, output_file):
    cfg, courses, df = linkComments.load_data_from_config_path(cfg_path)
    if not output_file:
        output_file = str(cfg.exams.averages_output)

    workbook = xlsxwriter.Workbook(output_file)

    for course in courses.keys():
        exam_folder = os.path.join(str(cfg.exams.exam_root), course)
        notes, exam_names, exam_files = examNotes.merge_notes_for_one_course(exam_folder, list(courses[course].keys()))
        exam_dates = notes.columns
        student_codes = list(courses[course].keys())
        spaces_before_average = 1

        num_exams = len(exam_dates)
        num_students = len(student_codes)

        noted_exams = identify_noted_exams_if_possible(exam_files, cfg)

        worksheet = workbook.add_worksheet(course)
        formats = examNotes.create_workbook_formats(workbook)

        averages = ["NIP", "S1", "S2"]

        num_averages = len(averages)
        weights_nip = [int(first_semester(d)) for d in exam_dates]
        weights_s1 = [n if first_semester(d) else 0 for d, n in zip(exam_dates, noted_exams)]
        weights_s2 = [n if not first_semester(d) else 0 for d, n in zip(exam_dates, noted_exams)]
        weights = [weights_nip, weights_s1, weights_s2]

        headers = ["Date", "Name"] + [h + " weight" for h in averages]

        worksheet.write_column(0, 0, headers)
        worksheet.write_row(0, 1, exam_dates)
        worksheet.write_row(1, 1, exam_names)
        for i in range(num_averages):
            worksheet.write_row(2+i, 1, weights[i])

        row_for_notes = len(headers) + 1
        for i, student_code in enumerate(student_codes):
            worksheet.write(i+row_for_notes, 0, student_code)
            for j, note in enumerate(notes.loc[student_code]):
                if note > 0:
                    worksheet.write(i+row_for_notes, j+1, note)
                else:
                    worksheet.write(i + row_for_notes, j + 1, -100, formats["red"])

        worksheet.write_row(row_for_notes - 1, num_exams + spaces_before_average + 2, averages)
        worksheet.write_column(row_for_notes, num_exams + spaces_before_average + 1, student_codes)
        for i in range(num_averages):
            weight_range = xlsxwriter.utility.xl_range_abs(i + 2, 1, i + 2, num_exams)
            formula = f'=IF(SUM({weight_range})>0,SUM({weight_range}),1)'
            worksheet.write_formula(row_for_notes - 2, num_exams + spaces_before_average + 2 + i, formula)
            sum_weight_range = xlsxwriter.utility.xl_rowcol_to_cell(
                row_for_notes - 2, num_exams + spaces_before_average + 2 + i, row_abs=True)

            for j in range(num_students):
                note_range = xlsxwriter.utility.xl_range(row_for_notes + j, 1, row_for_notes + j, num_exams)
                formula = f'=SUMPRODUCT({weight_range}, {note_range})/{sum_weight_range}'
                worksheet.write_formula(
                    row_for_notes + j, num_exams + spaces_before_average + 2 + i, formula, formats["round1b"])

        average_range = xlsxwriter.utility.xl_range(
            row_for_notes, num_exams + spaces_before_average + 2, row_for_notes + num_students - 1,
            num_exams + spaces_before_average + 2 + num_averages - 1)
        worksheet.conditional_format(average_range, formats["failing"])

        worksheet.write(row_for_notes - 2, num_exams + 2*spaces_before_average + num_averages + 2,
                        "Rounded Notes", formats["round1b"])
        for i, header in enumerate(["S1", "S2", "EOY"]):
            worksheet.write(row_for_notes - 1, num_exams + 3*spaces_before_average + num_averages + 1 + i,
                            header, formats["round1b"])

        for j in range(num_students):
            s_positions = []
            for s in [1, 2]:
                s_position_old = xlsxwriter.utility.xl_rowcol_to_cell(row_for_notes + j,
                                                                      num_exams + spaces_before_average + 2 + s)
                formula = f'ROUND({s_position_old}*10)/10'
                s_position_new = num_exams + 2*spaces_before_average + num_averages + 1 + s
                worksheet.write_formula(
                    row_for_notes + j, s_position_new, formula, formats["round1b"])
                s_positions.append(xlsxwriter.utility.xl_rowcol_to_cell(row_for_notes + j, s_position_new))

            formula = f'=ROUND(AVERAGE({s_positions[0]},{s_positions[1]})*10)/10'
            worksheet.write_formula(
                row_for_notes + j, num_exams + 2 * spaces_before_average + num_averages + 4,
                formula, formats["round1b"])
        worksheet.conditional_format(
            row_for_notes, num_exams + 2 * spaces_before_average + num_averages + 2,
            row_for_notes + num_students - 1, num_exams + 3 * spaces_before_average + 2 * num_averages,
            formats["failing"])

    workbook.close()
    return output_file


def identify_noted_exams_if_possible(exam_files, cfg):
    if cfg.exams.noted_exams:
        noted_exams = parse_noted_exam_weights(cfg.exams.noted_exams)
        return [noted_exams.get(f, 0) for f in exam_files]
    else:
        return [1.0] * len(exam_files)


def parse_noted_exam_weights(noted_exam_lines):
    exam_weights = {}
    for line in noted_exam_lines:
        exam_file, *weight = [part.strip() for part in line.split(",")]
        exam_weights[exam_file] = float(weight[0]) if weight else 1.0
    return exam_weights