scaled marks

import pandas as pd
from sklearn.preprocessing import MinMaxScaler

# ---------- CONFIG ----------
INPUT_FILE = "grade.csv"
OUTPUT_FILE = "grade_scaled.csv"

# ---------- GRADE RANGES ----------
GRADE_RANGES = {
    "AA": (91, 100),
    "AB": (81, 90),
    "BB": (71, 80),
    "BC": (61, 70),
    "CC": (51, 60),
    "CD": (41, 50),
    "DD": (31, 40),
    "I": (0, 30),
}

# ---------- READ CSV ----------
df = pd.read_csv(INPUT_FILE)

# Clean column names
df.columns = [c.strip() for c in df.columns]

# Clean Grade column
df["Grade"] = df["Grade"].astype(str).str.strip().str.upper()

# Convert Sum to numeric
df["Sum"] = pd.to_numeric(df["Sum"], errors="coerce")

# ---------- SORT BY SUM ----------
df = df.sort_values(by="Sum", ascending=False).reset_index(drop=True)

# ---------- SCALE WITHIN EACH GRADE ----------
df["Scaled"] = 0.0

for grade, (low, high) in GRADE_RANGES.items():
    # Subset for this grade
    mask = df["Grade"] == grade
    subset = df.loc[mask, "Sum"]

    if len(subset) == 0:
        continue

    # If all values same → assign midpoint
    if subset.min() == subset.max():
        scaled_vals = [round((low + high) / 2, 2)] * len(subset)

    else:
        scaler = MinMaxScaler(feature_range=(low, high))
        scaled_vals = scaler.fit_transform(subset.values.reshape(-1, 1)).flatten()
        scaled_vals = [round(x, 2) for x in scaled_vals]

    df.loc[mask, "Scaled"] = scaled_vals

# ---------- SAVE ----------
df.to_csv(OUTPUT_FILE, index=False)

print(f"Scaled file saved as: {OUTPUT_FILE}")

#Sl,Roll,Name,Sum,Grade
#1,1301AI04,Sukar,99.296,AA
#2,1301AI30,M Singh,96.509,AA
#3,1301AI16,K K,91.364,AA