import pandas as pd

# Load dataset
df = pd.read_csv("Refine_Realistic_10M.csv", names=["Pattern", "MainResult"], header=0)

# Hitung jumlah S, F, B per pattern
pattern_stats = df.groupby("Pattern")["MainResult"].value_counts().unstack().fillna(0)
pattern_stats["Total"] = pattern_stats.sum(axis=1)
pattern_stats["SuccessRate"] = pattern_stats.get("S", 0) / pattern_stats["Total"]

# Tambah warna rekomendasi
def get_recommendation(prob):
    if prob >= 0.55:
        return "Green"
    elif prob >= 0.45:
        return "Yellow"
    else:
        return "Red"

pattern_stats["Recommendation"] = pattern_stats["SuccessRate"].apply(get_recommendation)

# Perbaiki nama kolom agar rapi
pattern_stats = pattern_stats.rename(columns={
    "S": "Success",
    "F": "Fail",
    "B": "Break"
})

# Pastikan kolom S/F/B ada semua
for col in ["Success", "Fail", "Break"]:
    if col not in pattern_stats.columns:
        pattern_stats[col] = 0

# Urutkan dan reset index
pattern_stats = pattern_stats.reset_index()
pattern_stats = pattern_stats[["Pattern", "Success", "Fail", "Break", "Total", "SuccessRate", "Recommendation"]]
pattern_stats = pattern_stats.sort_values(by="SuccessRate", ascending=False)

# Simpan ke Excel
pattern_stats.to_excel("ROX_Refine_Predictor_Realistic_1M.xlsx", index=False)
print("Selesai: hasil disimpan di ROX_Refine_Predictor_Realistic_1M.xlsx")