0_RunSampler.py

# build_sampling_selfcontained.py
import pandas as pd
import argparse, random, os
from datetime import datetime

MICRO_XLSX  = "AMIRA_microinterventionlogs.xlsx"
MICRO_CSV   = "AMIRA_microinterventionlogs.csv"
TIMING_XLSX = "AMIRA_readingandtiminglogs.xlsx"   # <-- prefer this (@Maggie we need the xlsx file so that the odd pronunciation texts get preserved, .csv turns them to ?)
TIMING_CSV  = "AMIRA_readingandtiminglogs.csv"    # fallback if needed

def read_table(path_xlsx, path_csv):
    if os.path.exists(path_xlsx):
        return pd.read_excel(path_xlsx, dtype=str).fillna("")
    if os.path.exists(path_csv):
        try:
            return pd.read_csv(path_csv, dtype=str).fillna("")
        except UnicodeDecodeError:
            return pd.read_csv(path_csv, dtype=str, encoding="latin1").fillna("")
    raise FileNotFoundError(f"Missing input: {path_xlsx} (preferred) or {path_csv}")

def _to_num(s): return pd.to_numeric(s, errors="coerce")

def _clean_times(series, fallback):
    s = _to_num(series).copy()
    fb = float(fallback) if pd.notna(fallback) else 0.0
    for i in range(len(s)):
        v = s.iloc[i]
        if pd.isna(v) or v <= 0.001:
            if i > 0 and pd.notna(s.iloc[i-1]) and s.iloc[i-1] > 0.001:
                s.iloc[i] = s.iloc[i-1]
            elif i < len(s)-1 and pd.notna(s.iloc[i+1]) and s.iloc[i+1] > 0.001:
                s.iloc[i] = s.iloc[i+1]
            else:
                s.iloc[i] = fb
    return s

def build_adjusted_timing(timing_df, aid):
    t = timing_df[timing_df["activity_id"] == aid].copy()
    if t.empty: 
        return t
    for c in ["phrase_index","word_index","AmiraKaldi_Start_Time","AmiraKaldi_End_Time"]:
        t[c] = _to_num(t.get(c))
    t = t.sort_values(["phrase_index","word_index"])
    t["Adjusted_Start"] = 0.0
    t["Adjusted_End"]   = 0.0
    cumulative_offset = 0.0
    last_phrase_end = 0.0
    for pidx, g in t.groupby("phrase_index"):
        g = g.sort_values("word_index")
        phrase_start = g["AmiraKaldi_Start_Time"].min()
        phrase_end   = g["AmiraKaldi_End_Time"].max()
        if pd.isna(phrase_start) or pd.isna(phrase_end): 
            continue
        if pd.notna(pidx) and int(pidx) != 0:
            lag = max(0.0, phrase_start - last_phrase_end)
            cumulative_offset += lag + last_phrase_end
        cs = _clean_times(g["AmiraKaldi_Start_Time"], phrase_start)
        ce = _clean_times(g["AmiraKaldi_End_Time"],   phrase_end)
        t.loc[g.index, "Adjusted_Start"] = cs + cumulative_offset
        t.loc[g.index, "Adjusted_End"]   = ce + cumulative_offset
        last_phrase_end = phrase_end
    return t

def clip_indices_for_session(tdata, clip_seconds, criterion="phrase_start"):
    if tdata.empty: return []
    if criterion == "phrase_start":
        starts = tdata.groupby("phrase_index")["Adjusted_Start"].min().dropna()
        return sorted(set((starts // clip_seconds).astype(int).tolist()))
    # word_overlap
    max_end = _to_num(tdata["Adjusted_End"]).max()
    if pd.isna(max_end): return []
    idxs, max_clip = [], int(max_end // clip_seconds) + 1
    for k in range(max_clip):
        s, e = k*clip_seconds, (k+1)*clip_seconds
        mask = (tdata["Adjusted_Start"] < e) & (tdata["Adjusted_End"] > s)
        if mask.any(): idxs.append(k)
    return idxs

def main(args):
    random.seed(args.seed)

    micro  = read_table(MICRO_XLSX, MICRO_CSV)
    timing = read_table(TIMING_XLSX, TIMING_CSV)

    # required columns (create blanks if missing)
    for df, cols in (
        (micro,  ["activity_id","student_id","phrase_index","word_index","intervention_scope","intervention_type","intervention_word"]),
        (timing, ["activity_id","phrase_index","word_index","Story Word","AmiraW2V_Rec_Word","AmiraKaldi_Rec_Word","AmiraKaldi_Start_Time","AmiraKaldi_End_Time","annotator label","raw_text"]),
    ):
        for c in cols:
            if c not in df.columns: df[c] = ""

    # attach student_id to timing via micro
    id_map = (
        micro[["activity_id","student_id"]]
        .drop_duplicates()
        .query("activity_id != '' and student_id != ''")
    )
    timing = timing.merge(id_map, on="activity_id", how="left")

    # Eligible students with >= min_sessions
    counts = (
        micro[["student_id","activity_id"]].drop_duplicates()
        .query("student_id != '' and activity_id != ''")
        .groupby("student_id")["activity_id"].nunique()
    )
    eligible_students = set(counts[counts >= args.min_sessions].index)

    # sessions per eligible student
    sessions = micro[micro["student_id"].isin(eligible_students)][["student_id","activity_id"]].drop_duplicates()

    # Pick up to sessions_per_student per student
    picked_pairs = []
    for sid, g in sessions.groupby("student_id", sort=False):
        aids = list(g["activity_id"])
        if args.mode == "random":
            random.shuffle(aids)

        if args.sessions_per_student is None or args.sessions_per_student < 0:
            chosen = aids  # use all available sessions
        else:
            chosen = aids[:args.sessions_per_student]  # linear mode already ordered; random mode shuffled above

        for aid in chosen:
            picked_pairs.append((sid, aid))

    pair_stats = []  # each item: {"sid":..., "aid":..., "available": int, "selected": int}
    rows = []
    for sid, aid in picked_pairs:
        tdata = build_adjusted_timing(timing, aid)
        if tdata.empty: 
            continue

        # numeric + sort
        td = tdata.copy()
        td["phrase_index"] = _to_num(td["phrase_index"])
        td["word_index"]   = _to_num(td["word_index"])
        td = td.sort_values(["phrase_index","word_index"])

        # decide clips present
        '''
        clip_idxs = clip_indices_for_session(td, args.clip_seconds, args.criterion)
        if not clip_idxs:
            continue
        if args.clips_per_session is not None and args.clips_per_session >= 0:
            if args.mode == "random":
                clip_idxs = random.sample(clip_idxs, k=min(args.clips_per_session, len(clip_idxs)))
            else:
                clip_idxs = clip_idxs[:args.clips_per_session]
        '''
        
        # decide clips present
        clip_idxs_all = clip_indices_for_session(td, args.clip_seconds, args.criterion)
        if not clip_idxs_all:
            # still record that this session had 0 available
            pair_stats.append({"sid": sid, "aid": aid, "available": 0, "selected": 0})
            continue

        clip_idxs = clip_idxs_all.copy()
        if args.clips_per_session is not None and args.clips_per_session >= 0:
            if args.mode == "random":
                clip_idxs = random.sample(clip_idxs, k=min(args.clips_per_session, len(clip_idxs)))
            else:
                clip_idxs = clip_idxs[:args.clips_per_session]

        # record per-session stats
        pair_stats.append({
            "sid": sid,
            "aid": aid,
            "available": len(clip_idxs_all),
            "selected": len(clip_idxs)
        })


        # IVs for (sid, aid)
        iv = micro[(micro["activity_id"] == aid) & (micro["student_id"] == sid)].copy()
        iv["phrase_index"] = _to_num(iv.get("phrase_index"))
        iv["word_index"]   = _to_num(iv.get("word_index"))

        # Aggregate word-level IVs
        word_ivs = iv[(iv["intervention_scope"] == "word")]
        agg = (word_ivs
               .groupby(["phrase_index","word_index"], dropna=False)
               .agg({
                   "intervention_type": lambda s: "|".join([str(x) for x in s if str(x).strip() != ""]),
                   "intervention_word": lambda s: "|".join([str(x) for x in s if str(x).strip() != ""])
               })
               .reset_index())
        agg.rename(columns={"intervention_type":"word_iv_types","intervention_word":"word_iv_words"}, inplace=True)

        # phrase IVs
        phrase_ivs = iv[iv["intervention_scope"] == "phrase"][["phrase_index","intervention_type","intervention_word"]].copy()

        # phrase first-word start times
        phrase_first = td.groupby("phrase_index")["Adjusted_Start"].min()

        for k in sorted(clip_idxs):
            start = int(k * args.clip_seconds)
            end   = int(start + args.clip_seconds)
            in_clip_phrase_idx = phrase_first[(phrase_first >= start) & (phrase_first < end)].index.tolist()
            if not in_clip_phrase_idx: 
                continue

            words = td[td["phrase_index"].isin(in_clip_phrase_idx)].copy()
            words = words.merge(agg, on=["phrase_index","word_index"], how="left")

            for _, w in words.iterrows():
                rows.append({
                    "sample_id": f"{aid}_{sid}_{k}",
                    "batch_id": args.batch_id or "",
                    "activity_id": aid,
                    "student_id": sid,
                    "clip_index": k,
                    "clip_start": start,
                    "clip_end": end,
                    "clip_seconds": args.clip_seconds,
                    "row_type": "word",
                    "phrase_index": int(w["phrase_index"]) if pd.notna(w["phrase_index"]) else "",
                    "word_index":   int(w["word_index"])   if pd.notna(w["word_index"])   else "",
                    "story_word":   (w.get("Story Word") if pd.notna(w.get("Story Word")) else ""),
                    "kaldi_word":   (w.get("AmiraKaldi_Rec_Word") if pd.notna(w.get("AmiraKaldi_Rec_Word")) else ""),
                    "w2v_word":     (w.get("AmiraW2V_Rec_Word") if pd.notna(w.get("AmiraW2V_Rec_Word")) else ""),
                    "adjusted_start": float(w["Adjusted_Start"]) if pd.notna(w["Adjusted_Start"]) else "",
                    "adjusted_end":   float(w["Adjusted_End"])   if pd.notna(w["Adjusted_End"])   else "",
                    "correct_label":  int(w["annotator label"]) if pd.notna(w.get("annotator label")) else "",
                    "word_iv_types":  (w.get("word_iv_types") if pd.notna(w.get("word_iv_types")) else ""),
                    "word_iv_words":  (w.get("word_iv_words") if pd.notna(w.get("word_iv_words")) else ""),
                    "phrase_iv_type": "",
                    "phrase_iv_word": "",
                    "raw_text":       (w.get("raw_text") if pd.notna(w.get("raw_text")) else ""),  # <-- NEW
                })

            piv = phrase_ivs[phrase_ivs["phrase_index"].isin(in_clip_phrase_idx)]
            for _, r in piv.iterrows():
                rows.append({
                    "sample_id": f"{aid}_{sid}_{k}",
                    "batch_id": args.batch_id or "",
                    "activity_id": aid,
                    "student_id": sid,
                    "clip_index": k,
                    "clip_start": start,
                    "clip_end": end,
                    "clip_seconds": args.clip_seconds,
                    "row_type": "phrase_iv",
                    "phrase_index": int(r["phrase_index"]) if pd.notna(r["phrase_index"]) else "",
                    "word_index":   "",
                    "story_word":   "",
                    "kaldi_word":   "",
                    "w2v_word":     "",
                    "adjusted_start": "",
                    "adjusted_end":   "",
                    "correct_label":  "",
                    "word_iv_types":  "",
                    "word_iv_words":  "",
                    "phrase_iv_type": (r["intervention_type"] if pd.notna(r["intervention_type"]) else ""),
                    "phrase_iv_word": (r["intervention_word"] if pd.notna(r["intervention_word"]) else ""),
                })

    # global order
    if args.global_mode == "random":
        random.shuffle(rows)
    else:
        rows.sort(key=lambda r: (r["student_id"], r["activity_id"], r["clip_index"],
                                 0 if r["row_type"]=="word" else 1,
                                 r["phrase_index"] if r["phrase_index"] != "" else 10**9,
                                 r["word_index"]   if r["word_index"]   != "" else 10**9))
    for i, r in enumerate(rows):
        r["order"] = i

    out_df = pd.DataFrame(rows)

    # write Excel (preserve symbols)
    out_path = args.out
    if not out_path.lower().endswith(".xlsx"):
        out_path += ".xlsx"
    with pd.ExcelWriter(out_path, engine="openpyxl") as xw:
        out_df.to_excel(xw, index=False, sheet_name="sample")

    # --- report --- (This is wrong, we need to change because it will not port with coder because of the requested changes)
    
    '''
    clips   = out_df.drop_duplicates(subset=["activity_id","student_id","clip_index"]).shape[0]
    students= out_df["student_id"].nunique()
    samples = out_df["sample_id"].nunique()
    print(f"Wrote {len(out_df):,} rows to {out_path}")
    print(f"Samples (unique sample_id): {samples:,}")
    print(f"Clips (unique activity_id,student_id,clip_index): {clips:,}")
    print(f"Unique students: {students:,}")
    '''
    
    # --- report ---
    '''
    rows_total = len(out_df)
    clips   = out_df.drop_duplicates(subset=["activity_id","student_id","clip_index"]).shape[0]
    students= out_df["student_id"].nunique()
    samples = students * args.sessions_per_student  # target sessions per student

    print(f"Wrote {rows_total:,} rows to {out_path}")
    print(f"Samples (students × sessions_per_student): {samples:,}")
    print(f"Clips (unique activity_id,student_id,clip_index): {clips:,}")
    print(f"Unique students: {students:,}")
    '''
    
    '''
    rows_total = len(out_df)
    clips   = out_df.drop_duplicates(subset=["activity_id","student_id","clip_index"]).shape[0]
    students= out_df["student_id"].nunique()
    # sessions actually used:
    sessions_selected = len(set((r[0], r[1]) for r in picked_pairs))  # unique (student, activity) pairs

    print(f"Wrote {rows_total:,} rows to {out_path}")
    print(f"Sessions selected (unique student_id × activity_id): {sessions_selected:,}")
    print(f"Clips (unique activity_id,student_id,clip_index): {clips:,}")
    print(f"Unique students: {students:,}")
    '''
    
    # --- report ---
    rows_total = len(out_df)
    clips_actual = out_df.drop_duplicates(
        subset=["activity_id","student_id","clip_index"]
    ).shape[0]
    students = out_df["student_id"].nunique()

    # sessions attempted (picked), regardless of yield
    sessions_attempted = len(set((r[0], r[1]) for r in picked_pairs))
    # sessions that yielded >=1 clip
    sessions_with_yield = len(set((s["sid"], s["aid"]) for s in pair_stats if s["selected"] > 0))
    sessions_zero_clip = sessions_attempted - sessions_with_yield

    eligible_students_count = len(eligible_students)
    sessions_min_required = eligible_students_count * args.min_sessions

    # clips max possible (if a per-session cap is set)
    if args.clips_per_session is not None and args.clips_per_session >= 0:
        clips_max_possible = sessions_attempted * args.clips_per_session
        clip_fill_rate = (clips_actual / clips_max_possible) if clips_max_possible > 0 else 0.0
    else:
        clips_max_possible = None
        clip_fill_rate = None

    # sessions below target clips (only meaningful if clips_per_session is set)
    sessions_below_target = 0
    if args.clips_per_session is not None and args.clips_per_session >= 0:
        sessions_below_target = sum(1 for s in pair_stats if s["selected"] < args.clips_per_session)

    print(f"Wrote {rows_total:,} rows to {out_path}")
    print(f"Unique students: {students:,}")

    # sessions line with min-required
    print(
        "Sessions selected (unique student_id × activity_id): "
        f"{sessions_attempted:,} ({sessions_min_required:,} min possible --min-sessions)"
    )

    # clips line with max-possible + fill rate
    if clips_max_possible is not None:
        print(
            f"Clips (unique activity_id,student_id,clip_index): "
            f"{clips_actual:,} ({clips_max_possible:,} max possible; {clip_fill_rate:.1%})"
        )
    else:
        print(
            "Clips (unique activity_id,student_id,clip_index): "
            f"{clips_actual:,} (max possible: n/a)"
        )




if __name__ == "__main__":
    ap = argparse.ArgumentParser()
    ap.add_argument("--out", default="sample.xlsx")        # now xlsx (@Maggie, there should be no ? present)
    ap.add_argument("--clip-seconds", dest="clip_seconds", type=int, default=20)
    ap.add_argument("--min-sessions", dest="min_sessions", type=int, default=2)
    ap.add_argument("--sessions-per-student", type=int, default=-1)
    ap.add_argument("--clips-per-session", type=int, default=3)
    ap.add_argument("--mode", choices=["random","linear"], default="random")
    ap.add_argument("--global-mode", choices=["random","linear"], default="linear")
    ap.add_argument("--criterion", choices=["phrase_start","word_overlap"], default="phrase_start")
    ap.add_argument("--seed", type=int, default=None)
    ap.add_argument("--batch-id", default="")
    args = ap.parse_args()
    if args.clips_per_session is not None and args.clips_per_session < 0:
        args.clips_per_session = None
    main(args)