import pandas as pd
file_path = "[Link]"
df = pd.read_excel(file_path)
# Normalize headers (VERY IMPORTANT)
[Link] = [Link]().[Link]()
core_subjects = ["ENG", "MTC", "HIST", "GEO", "CHEM", "BIO", "PHY"]
electives = ["ICT", "ENT", "AGRI", "ART", "CRE", "LUG", "KIS"]
all_subjects = core_subjects + electives
skills = [
"Present coherently",
"Try out innovation",
"Manage time and goals",
"Work in teams",
"Think critically",
"Solve problems"
]
# ===== FUNCTIONS =====
def is_offered(row, subject):
opt1 = str([Link]("OPT1", "")).strip().upper()
opt2 = str([Link]("OPT2", "")).strip().upper()
if opt1 == "" and opt2 == "":
return True
if subject in core_subjects:
return True
return subject in [opt1, opt2]
def calc_x80(mark):
return None if [Link](mark) else (mark / 100) * 80
def calc_total(x20, x80):
if [Link](x20) or [Link](x80):
return None
return x20 + x80
def calc_x3(total):
if [Link](total):
return None
return max((total / 100) * 3, 1.0)
def grade(total, missed):
if missed:
return "X", "Underscore"
if total >= 95:
return "A*", "Exceptional"
elif total >= 85:
return "A", "Outstanding"
elif total >= 70:
return "B", "Satisfactory"
elif total >= 60:
return "C", "Moderate"
elif total >= 33:
return "D", "Basic"
else:
return "E", "Elementary"
# ===== STORE RESULTS =====
results = {}
for subject in all_subjects:
c1 = subject + "1"
c2 = subject + "2"
if c1 not in [Link] or c2 not in [Link]:
continue
results[subject] = {
"3": [],
"4": [],
"5": [],
"G": [],
"D": [],
"S": []
}
for _, row in [Link]():
if not is_offered(row, subject):
results[subject]["3"].append(None)
results[subject]["4"].append(None)
results[subject]["5"].append(None)
results[subject]["G"].append(None)
results[subject]["D"].append(None)
results[subject]["S"].append(None)
continue
m100 = row[c1]
m20 = row[c2]
missed = [Link](m100) or [Link](m20)
if missed:
results[subject]["3"].append(None)
results[subject]["4"].append(None)
results[subject]["5"].append(None)
results[subject]["G"].append("X")
results[subject]["D"].append("Underscore")
results[subject]["S"].append(None)
continue
x80 = calc_x80(m100)
total = calc_total(m20, x80)
x3 = calc_x3(total)
g, d = grade(total, False)
results[subject]["3"].append(x80)
results[subject]["4"].append(total)
results[subject]["5"].append(x3)
results[subject]["G"].append(g)
results[subject]["D"].append(d)
results[subject]["S"].append([Link](skills).sample().values[0])
# ===== BUILD ORDERED COLUMNS =====
new_df = [Link]()
base_cols = ["NAME","AGE","CLASS","GENDER","SFC","HOUSE","OPT1","OPT2"]
for col in base_cols:
if col in [Link]:
new_df[col] = df[col]
# Insert subject blocks in order
for subject in all_subjects:
c1 = subject + "1"
c2 = subject + "2"
if c1 not in [Link] or c2 not in [Link]:
continue
# original marks
new_df[c1] = df[c1]
new_df[c2] = df[c2]
# computed columns immediately AFTER
new_df[subject + "3"] = results[subject]["3"]
new_df[subject + "4"] = results[subject]["4"]
new_df[subject + "5"] = results[subject]["5"]
new_df[subject + "G"] = results[subject]["G"]
new_df[subject + "D"] = results[subject]["D"]
new_df[subject + "S"] = results[subject]["S"]
# save
new_df.to_excel(file_path, index=False)
print("✅ Done: Columns now properly grouped per subject")