Calc_R.py

# Compute R = 0.6*P + 0.3*C + 0.1*E from Cal_Inputs and save to Output
import argparse
from pathlib import Path
import pandas as pd

# ---- Defaults (edit if you want to hardcode paths) ----
DEFAULT_INPUT  = Path(r"E:\Academic 7th Sem\Final Year Project\RAG\Data_Anotation\Dataset\Cal_Inputs.xlsx")
DEFAULT_SHEET  = "Cal_Inputs"   # if not found, first sheet will be used
DEFAULT_OUTPUT = Path(r"E:\Academic 7th Sem\Final Year Project\RAG\Data_Anotation\Output\Cal_Inputs_with_R.xlsx")

def find_cols(df, needed=("P","C","E")):
    """Return a dict mapping 'P','C','E' to actual column names (case-insensitive)."""
    lc = {c.lower().strip(): c for c in df.columns}
    out = {}
    for k in needed:
        key = k.lower()
        if key in lc:
            out[k] = lc[key]
        else:
            raise SystemExit(f"ERROR: Column '{k}' not found. Columns present: {list(df.columns)}")
    return out

def main(inp: Path, outp: Path, sheet: str | None):
    if inp.is_dir():
        # If a folder is provided, try to find a file with 'Cal_Inputs' in the name
        cands = sorted([p for p in inp.glob("*.xlsx") if "cal_inputs" in p.stem.lower()])
        if not cands:
            raise SystemExit(f"ERROR: No '*.xlsx' with 'Cal_Inputs' found in folder: {inp}")
        inp = cands[0]

    if not inp.exists():
        raise SystemExit(f"ERROR: Input file not found: {inp}")

    # Load workbook and pick sheet
    xls = pd.ExcelFile(inp, engine="openpyxl")
    if sheet and sheet in xls.sheet_names:
        df = xls.parse(sheet)
    elif DEFAULT_SHEET in xls.sheet_names:
        df = xls.parse(DEFAULT_SHEET)
    else:
        df = xls.parse(xls.sheet_names[0])  # fallback: first sheet

    # Ensure columns exist and are numeric
    colmap = find_cols(df, ("P","C","E"))
    for k, col in colmap.items():
        df[col] = pd.to_numeric(df[col], errors="coerce")

    # Compute R
    P, C, E = df[colmap["P"]], df[colmap["C"]], df[colmap["E"]]
    df["R"] = (0.6 * P + 0.3 * C + 0.1 * E).round(4)

    # Save
    outp.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(outp, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="Cal_Inputs_with_R")

    # Small console preview
    print(f"✅ Wrote: {outp}")
    print(df[[colmap["P"], colmap["C"], colmap["E"], "R"]].head(10).to_string(index=False))

if __name__ == "__main__":
    ap = argparse.ArgumentParser(description="Compute R = 0.6*P + 0.3*C + 0.1*E from Cal_Inputs")
    ap.add_argument("-i", "--input",  help="Input Excel path or folder", default=str(DEFAULT_INPUT))
    ap.add_argument("-o", "--output", help="Output Excel path", default=str(DEFAULT_OUTPUT))
    ap.add_argument("-s", "--sheet",  help="Sheet name (default: Cal_Inputs or first sheet)", default=DEFAULT_SHEET)
    args = ap.parse_args()
    main(Path(args.input), Path(args.output), args.sheet)