|
import pandas as pd |
|
import numpy as np |
|
import plotly.express as px |
|
import plotly.graph_objs as go |
|
import streamlit as st |
|
import joblib |
|
import re |
|
|
|
|
|
def get_year(student_id): |
|
year_str = "" |
|
for char in student_id: |
|
if char.isdigit(): |
|
year_str += char |
|
if len(year_str) == 2: |
|
break |
|
return int(year_str) |
|
|
|
|
|
@st.cache_data() |
|
def process_data(raw_data): |
|
|
|
raw_data = raw_data[ |
|
~raw_data["TenMH"].str.contains("IE|Intensive English|IE2|IE1|IE3|IE0") |
|
] |
|
|
|
pivot_df = pd.pivot_table( |
|
raw_data, values="DiemHP", index="MaSV", columns="TenMH", aggfunc="first" |
|
) |
|
pivot_df = pivot_df.reset_index().rename_axis(None, axis=1) |
|
pivot_df.columns.name = None |
|
pivot_df = pivot_df.dropna(thresh=50, axis=1) |
|
pivot_df = pivot_df.rename(columns=lambda x: x.strip()) |
|
|
|
df = pd.merge(pivot_df, raw_data[["MaSV"]], on="MaSV") |
|
df.drop_duplicates(subset="MaSV", keep="last", inplace=True) |
|
dfid = df["MaSV"] |
|
df.drop(["MaSV"], axis=1, inplace=True) |
|
df.replace(["WH", "VT", "I"], np.nan, inplace=True) |
|
df.iloc[:, :-1] = df.iloc[:, :-1].apply(pd.to_numeric) |
|
df = pd.merge(dfid, df, left_index=True, right_index=True) |
|
df["MaSV_school"] = df["MaSV"].str.slice(2, 4) |
|
df["Major"] = df["MaSV"].str.slice(0, 2) |
|
df["Year"] = 2000 + df["MaSV"].apply(get_year) |
|
df["Year"] = df["Year"].astype(str) |
|
df = pd.merge(df, raw_data[["MaSV", "DTBTK"]].drop_duplicates(), on="MaSV") |
|
df = df.drop(columns="MaSV") |
|
|
|
return df |
|
|
|
|
|
def process_data_per(raw_data): |
|
|
|
raw_data = raw_data[ |
|
~raw_data["TenMH"].str.contains("IE|Intensive English|IE2|IE1|IE3|IE0") |
|
] |
|
pivot_df = pd.pivot_table( |
|
raw_data, values="DiemHP", index="MaSV", columns="TenMH", aggfunc="first" |
|
) |
|
pivot_df = pivot_df.reset_index().rename_axis(None, axis=1) |
|
pivot_df.columns.name = None |
|
pivot_df = pivot_df.dropna(thresh=50, axis=1) |
|
pivot_df = pivot_df.rename(columns=lambda x: x.strip()) |
|
|
|
pivot_df.replace(["WH", "VT", "I"], np.nan, inplace=True) |
|
pivot_df.iloc[:, 1:] = pivot_df.iloc[:, 1:].apply(pd.to_numeric) |
|
|
|
return pivot_df |
|
|
|
|
|
def process_predict_data(raw_data): |
|
dtk = raw_data[["MaSV", "DTBTKH4"]].copy() |
|
dtk.drop_duplicates(subset="MaSV", keep="last", inplace=True) |
|
|
|
count_duplicates = ( |
|
raw_data.groupby(["MaSV", "MaMH"]).size().reset_index(name="Times") |
|
) |
|
courses = raw_data[ |
|
raw_data["MaMH"].str.startswith( |
|
("IT", "BA", "BM", "BT", "MA", "CE", "EE", "EL", "ENEE", "IS", "MAFE", "PH") |
|
) |
|
] |
|
|
|
courses_list = courses["MaMH"].unique().tolist() |
|
|
|
count_duplicates["fail_courses_list"] = ( |
|
(count_duplicates["MaMH"].isin(courses_list)) & (count_duplicates["Times"] >= 2) |
|
).astype(int) |
|
|
|
count_duplicates["fail_not_courses_list"] = ( |
|
(~count_duplicates["MaMH"].isin(courses_list)) |
|
& (count_duplicates["Times"] >= 2) |
|
).astype(int) |
|
|
|
count_duplicates["pass_courses"] = ( |
|
(~count_duplicates["MaMH"].isin(courses_list)) |
|
& (count_duplicates["Times"] == 1) |
|
).astype(int) |
|
|
|
fail = ( |
|
count_duplicates.groupby("MaSV")[["fail_courses_list", "fail_not_courses_list"]] |
|
.sum() |
|
.reset_index() |
|
) |
|
|
|
fail.columns = ["MaSV", "fail_courses_list_count", "fail_not_courses_list_count"] |
|
|
|
df = pd.merge(dtk, fail, on="MaSV") |
|
df = df.rename(columns={"DTBTKH4": "GPA"}) |
|
|
|
data = raw_data[["MaSV", "NHHK", "SoTCDat"]] |
|
data = ( |
|
data.groupby(["MaSV"])["SoTCDat"].mean().reset_index(name="Mean_Cre").round(2) |
|
) |
|
|
|
df = pd.merge(df, data, on="MaSV") |
|
df1 = raw_data[["MaSV", "MaMH", "NHHK"]] |
|
courses_list = raw_data[ |
|
(raw_data["MaMH"].str.startswith("EN")) |
|
& ~(raw_data["MaMH"].str.contains("EN007|EN008|EN011|EN012")) |
|
].MaMH.tolist() |
|
filtered_df = df1[df1["MaMH"].isin(courses_list)] |
|
nhhk_counts = ( |
|
filtered_df.groupby("MaSV")["NHHK"].nunique().reset_index(name="EPeriod") |
|
) |
|
df = pd.merge(df, nhhk_counts, on="MaSV", how="left").fillna(0) |
|
df = df[ |
|
[ |
|
"MaSV", |
|
"GPA", |
|
"Mean_Cre", |
|
"fail_courses_list_count", |
|
"fail_not_courses_list_count", |
|
"EPeriod", |
|
] |
|
] |
|
return df |
|
|
|
|
|
def predict_late_student(test_df): |
|
|
|
model = joblib.load("model/Time/Late.joblib") |
|
model1 = joblib.load("model/Time/Sem.joblib") |
|
|
|
test_dfed = process_predict_data(test_df) |
|
|
|
std_id = test_dfed.iloc[:, 0] |
|
|
|
test_dfed = test_dfed.drop(test_dfed.columns[0], axis=1) |
|
|
|
prediction = model.predict(test_dfed) |
|
|
|
prediction1 = model1.predict(test_dfed) |
|
|
|
test_dfed["Semeters"] = prediction1 |
|
test_dfed["Progress"] = ["late" if p == 1 else "not late" for p in prediction] |
|
|
|
test_dfed.insert(0, "MaSV", std_id) |
|
|
|
for index, row in test_dfed.iterrows(): |
|
if row["Semeters"] <= 9 and row["Progress"] == "late": |
|
test_dfed.loc[index, "Semeters"] = row["Semeters"] / 2 |
|
test_dfed.loc[index, "Progress"] = "may late" |
|
else: |
|
test_dfed.loc[index, "Semeters"] = row["Semeters"] / 2 |
|
|
|
return test_dfed |
|
|
|
|
|
def get_major(raw_data): |
|
major_mapping = { |
|
"BA": "BA", |
|
"BE": "BM", |
|
"BT": "BT", |
|
"CE": "CE", |
|
"EE": "EE", |
|
"EN": "EL", |
|
"EV": "ENEE", |
|
"IE": "IS", |
|
"IT": "IT", |
|
"MA": "MAFE", |
|
"SE": "PH", |
|
} |
|
for major, ma_mh in major_mapping.items(): |
|
if raw_data["MaSV"].str[:2].str.contains(major).any(): |
|
return major, ma_mh |
|
return None, None |
|
|
|
|
|
def create_pivot_table(raw_data): |
|
pivot_df = pd.pivot_table( |
|
raw_data, values="DiemHP", index="MaSV", columns="MaMH", aggfunc="first" |
|
) |
|
pivot_df = pivot_df.reset_index().rename_axis(None, axis=1) |
|
pivot_df.columns.name = None |
|
return pivot_df |
|
|
|
|
|
def drop_nan_columns(pivot_df): |
|
pivot_df = pivot_df.rename(columns=lambda x: x.strip()) |
|
pivot_df.replace(["WH", "VT", "I", "P", "F"], np.nan, inplace=True) |
|
pivot_df.iloc[:, 1:] = pivot_df.iloc[:, 1:].apply(pd.to_numeric) |
|
return pivot_df |
|
|
|
|
|
def merge_with_xeploainh(pivot_df, raw_data): |
|
df = pd.merge(pivot_df, raw_data[["MaSV", "DTBTK"]], on="MaSV") |
|
df.drop_duplicates(subset="MaSV", keep="last", inplace=True) |
|
return df |
|
|
|
|
|
def fill_missing_values(df): |
|
col = df.drop(["MaSV", "DTBTK"], axis=1) |
|
columns_data = get_column_data(df) |
|
dup = pd.DataFrame(columns=columns_data) |
|
df = pd.merge(dup, df, on=col.columns.tolist(), how="outer") |
|
for col in df.columns: |
|
if df[col].isnull().values.any(): |
|
df[col].fillna(value=df["DTBTK"], inplace=True) |
|
return df |
|
|
|
|
|
def get_column_data(df): |
|
major = df["MaSV"].str[:2].unique()[0] |
|
column_file = f"Columns/column_{major}.txt" |
|
columns_data = [] |
|
with open(column_file, "r") as f: |
|
for line in f: |
|
columns_data.append(str(line.strip())) |
|
return columns_data |
|
|
|
|
|
def prepare_data(df): |
|
std_id = df["MaSV"].copy() |
|
df = df.drop(["MaSV", "DTBTK"], axis=1) |
|
df.sort_index(axis=1, inplace=True) |
|
return df |
|
|
|
|
|
def predict_rank(raw_data): |
|
major, ma_mh = get_major(raw_data) |
|
if major: |
|
raw_data["MaMH"] = raw_data["MaMH"].str[:-2] |
|
raw_data = raw_data[raw_data["MaMH"].str.startswith(ma_mh)] |
|
|
|
pivot_df = create_pivot_table(raw_data) |
|
pivot_df = drop_nan_columns(pivot_df) |
|
|
|
df = merge_with_xeploainh(pivot_df, raw_data) |
|
df = fill_missing_values(df) |
|
|
|
std_id = df["MaSV"].copy() |
|
df = prepare_data(df) |
|
|
|
model = joblib.load(f"model/{major}_rank.joblib") |
|
prediction = model.predict(df) |
|
|
|
new_columns = pd.concat( |
|
[pd.Series(std_id, name="MaSV"), pd.Series(prediction, name="Pred Rank")], |
|
axis=1, |
|
) |
|
df = pd.concat([new_columns, df], axis=1) |
|
newframe = df.copy() |
|
|
|
df = newframe[["MaSV", "Pred Rank"]] |
|
return df |
|
else: |
|
return None |
|
|
|
|
|
def predict_one_student(raw_data, student_id): |
|
|
|
student = process_data_per(raw_data) |
|
filtered_df = student[student["MaSV"] == student_id] |
|
if len(filtered_df) > 0: |
|
selected_row = filtered_df.iloc[0, 1:].dropna() |
|
values = selected_row.values.tolist() |
|
course_data_filtered = [x for x in selected_row if not np.isnan(x)] |
|
counts, bins = np.histogram(course_data_filtered, bins=np.arange(0, 110, 10)) |
|
grade_bins = [f"{bins[i]}-{bins[i+1]}" for i in range(len(bins) - 1)] |
|
total_count = len(selected_row) |
|
frequencies_percentage = (counts / total_count) * 100 |
|
|
|
fig1 = go.Figure() |
|
|
|
fig1.add_trace( |
|
go.Scatter( |
|
x=bins[:-1], y=frequencies_percentage, mode="lines", name="Frequency" |
|
) |
|
) |
|
|
|
fig1.update_layout( |
|
title="Frequency Range for", |
|
xaxis_title="Score", |
|
yaxis_title="Percentage", |
|
height=400, |
|
width=400, |
|
) |
|
|
|
data = raw_data[["MaSV", "NHHK", "TenMH", "DiemHP"]] |
|
data["TenMH"] = data["TenMH"].str.lstrip() |
|
data["NHHK"] = data["NHHK"].apply(lambda x: str(x)[:4] + " S " + str(x)[4:]) |
|
rows_to_drop = [] |
|
with open("rows_to_drop.txt", "r") as f: |
|
for line in f: |
|
rows_to_drop.append(str(line.strip())) |
|
data = data[~data["TenMH"].isin(rows_to_drop)] |
|
student_data = data[data["MaSV"] == student_id][["NHHK", "TenMH", "DiemHP"]] |
|
student_data["DiemHP"] = pd.to_numeric(student_data["DiemHP"], errors="coerce") |
|
|
|
fig2 = px.bar( |
|
student_data, |
|
x="TenMH", |
|
y="DiemHP", |
|
color="NHHK", |
|
title="Student Score vs. Course", |
|
) |
|
fig2.update_layout( |
|
title="Student Score vs. Course", |
|
xaxis_title=None, |
|
yaxis_title="Score", |
|
) |
|
fig2.add_shape( |
|
type="line", |
|
x0=0, |
|
y0=50, |
|
x1=len(student_data["TenMH"]) - 1, |
|
y1=50, |
|
line=dict(color="red", width=3), |
|
) |
|
|
|
col1, col2 = st.columns(2) |
|
with col1: |
|
st.plotly_chart(fig1, use_container_width=True) |
|
|
|
with col2: |
|
st.plotly_chart(fig2, use_container_width=True) |
|
else: |
|
st.write("No data found for student {}".format(student_id)) |
|
|
|
|
|
def show_boxplot1( |
|
new1_df, new1_dfa, major, school, year, additional_selection="", year_a="" |
|
): |
|
if additional_selection != " ": |
|
show_boxplot = st.checkbox( |
|
"Show Boxplot for student's performance", key="checkbox2" |
|
) |
|
|
|
if show_boxplot: |
|
fig = px.box(new1_df) |
|
fig1 = px.box(new1_dfa) |
|
fig.update_layout( |
|
title="Boxplot of " + major + school + " student in " + year |
|
) |
|
fig1.update_layout( |
|
title="Boxplot of " |
|
+ major |
|
+ additional_selection |
|
+ " student in " |
|
+ year_a |
|
) |
|
col1, col2 = st.columns(2) |
|
|
|
with col1: |
|
st.plotly_chart(fig, use_container_width=True) |
|
|
|
with col2: |
|
st.plotly_chart(fig1, use_container_width=True) |
|
|
|
elif additional_selection == " " and year_a != " ": |
|
show_boxplot = st.checkbox( |
|
"Show Boxplot for student's performance", key="checkbox2" |
|
) |
|
|
|
if show_boxplot: |
|
fig = px.box(new1_df) |
|
fig1 = px.box(new1_dfa) |
|
fig.update_layout( |
|
title="Boxplot of " + major + school + " student in " + year |
|
) |
|
fig1.update_layout( |
|
title="Boxplot of " + major + school + " student in " + year_a |
|
) |
|
col1, col2 = st.columns(2) |
|
|
|
with col1: |
|
st.plotly_chart(fig, use_container_width=True) |
|
|
|
with col2: |
|
st.plotly_chart(fig1, use_container_width=True) |
|
|
|
elif additional_selection == " ": |
|
show_boxplot = st.checkbox( |
|
"Show Boxplot for student's performance", key="checkbox2" |
|
) |
|
|
|
if show_boxplot: |
|
fig = px.box(new1_df) |
|
fig.update_layout(title="Boxplot of " + major + " student in " + year) |
|
st.plotly_chart(fig, use_container_width=True) |
|
|
