import streamlit as st import pandas as pd import plotly.graph_objs as go # import time import plotly.express as px # import ast import numpy as np from app5_selectbox.database_con import cursor, db_connection from app5_selectbox.app5_selectbox_func import generate_unique_4 from app5_selectbox.evaluation_analysis import eval_analysis # from app5_selectbox.evaluation_analysis_g4f import eval_analysis # from app5_selectbox.langchain_llama_gpu import llm_chain from app5_selectbox.g4f_prompt import g4f_prompt # Function to fetch evaluation data def fetch_evaluation_data(): cursor.execute("SELECT * FROM evaluation") evaluation_data = cursor.fetchall() if not evaluation_data: st.warning("No evaluation data found.") return None column_names = [i[0] for i in cursor.description] return pd.DataFrame(evaluation_data, columns=column_names) # Function to analyze instructors def analyze_instructors(evaluation_df): if evaluation_df is None: return column_names = evaluation_df.columns[4:14] criteria_labels = [column.replace("_", " ") for column in column_names] cursor.execute("SELECT * FROM instructor") instructor_data = cursor.fetchall() # st.write(instructor_data) instructor_df = pd.DataFrame(instructor_data, columns=["inst_id", "instructor name","program code","user name","password"]) instructor_avg_scores = evaluation_df.groupby("inst_id")[column_names].mean().reset_index() instructor_avg_scores = instructor_avg_scores.merge(instructor_df, on="inst_id", how="left") # st.write(instructor_avg_scores) # programs_list = sorted(instructor_avg_scores["program code"].unique()) # Fetch program options from the program table cursor.execute("SELECT prog_id, prog_code, prog_name FROM program") selected_program = pd.DataFrame(cursor.fetchall(), columns=["prog_id", "prog_code", "prog_name"]) # st.write(selected_program) # st.write(list({str(prog): prog[0] for prog in program_options})) selected_program_select = st.selectbox("Select Program", selected_program["prog_code"]) # selected_program = ast.literal_eval(str(selected_program)) # selected_program = st.selectbox("Select Program", programs_list) filtered_instructor_list = pd.DataFrame(instructor_avg_scores) # st.write(filtered_instructor_list) mask = filtered_instructor_list["program code"] == selected_program.loc[selected_program['prog_code'] == selected_program_select, 'prog_id'].values[0] # st.write(mask) filtered_instructor_list = filtered_instructor_list.loc[mask] # st.write(filtered_instructor_list) instructors_list = sorted(filtered_instructor_list["instructor name"].unique()) # print(type(instructor_avg_scores)) # instructors_list = instructor_avg_scores.query("program code == {selected_program}") # st.write(len(instructors_list)) # df to graph selected_instructor = st.selectbox("Select Instructor", instructors_list) filtered_data = evaluation_df[evaluation_df["inst_id"] == instructor_avg_scores[instructor_avg_scores["instructor name"] == selected_instructor]["inst_id"].values[0]] selected_instructor_comments = list(filtered_data["comments"]) st.write(filtered_data) st.subheader(f"Evaluated by: {len(selected_instructor_comments)} students") cursor.execute(""" SELECT subj_inst.subj_inst_id, subject.sub_name FROM subj_inst INNER JOIN subject ON subj_inst.sub_id_code = subject.sub_id_code """) # Assuming you have a DataFrame named 'filtered_data' # and column_names is a list of column names you want to consider for calculating average scores # Convert all columns to numeric data filtered_data[column_names] = filtered_data[column_names].apply(pd.to_numeric, errors='coerce') # Fetch subject data from the cursor subject_data = cursor.fetchall() # Create a DataFrame for subject data subject_df = pd.DataFrame(subject_data, columns=["subj_inst_id", "sub name"]) # Merge subject data with filtered data based on 'subj_inst_id' filtered_data = filtered_data.merge(subject_df, on="subj_inst_id", how="left") # Group by subject name and calculate average scores subject_avg_scores = filtered_data.groupby("sub name")[column_names].mean().reset_index() # Calculate total average and add it as a new column subject_avg_scores["total average"] = subject_avg_scores[column_names].mean(axis=1) subject_avg_scores = filtered_data.groupby("sub name")[column_names].mean().reset_index() subject_avg_scores["total average"] = subject_avg_scores[column_names].mean(axis=1) fig = go.Figure() for criterion, label in zip(column_names, criteria_labels): fig.add_trace(go.Bar( x=subject_avg_scores["sub name"], y=subject_avg_scores[criterion], name=label, )) # Add the total average score above the bars total_average = subject_avg_scores["total average"].mean() fig.add_trace(go.Scatter( x=subject_avg_scores["sub name"], y=subject_avg_scores["total average"], mode="markers+text", text=round(subject_avg_scores["total average"], 2), textposition="top center", textfont=dict(size=14), marker=dict(size=10, color="black"), name="Total Average", )) fig.update_layout( width=1000,height=600, barmode="group", title=f"Average Scores per Criteria by Subject for Instructor: {selected_instructor}", xaxis_title=f"Overall Average Score (All Subjects): {total_average:.2f}", yaxis_title="Average Score", ) st.plotly_chart(fig) results_to_prompt = "Average score per Criteria\n" criteria_averages = [(criteria.replace("_", " "), round(filtered_data[criteria].mean(), 2)) for criteria in column_names] for criteria, average_score in criteria_averages: results_to_prompt += f"{criteria}: {average_score}/5, \n" fig = go.Figure() fig.add_trace(go.Bar( x=[criteria for criteria, _ in criteria_averages], y=[average_score for _, average_score in criteria_averages], text=[f"{average_score}/5" for _, average_score in criteria_averages], )) fig.update_layout( width=1000, title="Average Score per Criteria", xaxis_title="Criteria", yaxis_title="Average Score", ) st.plotly_chart(fig) results_to_prompt = f""" Based from these over-all average score please Analyze it and provide short insights: {str(results_to_prompt)}. Make it in sentence type and in English language only. """ while True: try: with st.spinner("Analyzing... "): # st.write(llm_chain.run(prompt)) # st.write(g4f_prompt(results_to_prompt)) ############################# st.success("Analyzing Complete!") break except Exception as e: # Handle the error (e.g., log it or take appropriate action) # Sleep for a moment before retrying # st.write("Error occurred.. Retrying") pass # time.sleep(0.4) # Add pie graph of evaluation distribution per student's section # Fetch program options from the program table cursor.execute(f""" SELECT pr.prog_code || '-' || c.class_year || '-' || c.class_section AS merged_result, COUNT(*) AS occurrence_count FROM student s JOIN class c ON s.class_id = c.class_id JOIN program pr ON c.prog_id = pr.prog_id WHERE s.stud_id IN {tuple(list(filtered_data["stud_id"]))} GROUP BY s.class_id, pr.prog_code, c.class_year, c.class_section; """) merged_result = pd.DataFrame(cursor.fetchall(), columns=["merged_result", "occurrence_count"]) st.write(filtered_data) st.write(merged_result) # section_counts = filtered_data["stud_id"].value_counts() # st.write(section_counts) fig = px.pie( merged_result, values="occurrence_count", names="merged_result", title="Evaluation Distribution per Student's Section", ) # Add percentage and occurrence_count to the hover information fig.update_traces( hovertemplate="%{label}: %{percent}
Occurrence Count: %{value}", textinfo="percent+value", ) fig.update_layout( width=600, height=600, font=dict(size=20), ) st.plotly_chart(fig) cursor.execute(f""" SELECT s.class_id, pr.prog_code || '-' || c.class_year || '-' || c.class_section AS class_info, COUNT(DISTINCT s.stud_id) AS num_respondents, ROUND((AVG(Teaching_Effectiveness) + AVG(Course_Organization) + AVG(Accessibility_and_Communication) + AVG(Assessment_and_Grading) + AVG(Respect_and_Inclusivity) + AVG(Engagement_and_Interactivity) + AVG(Feedback_and_Improvement) + AVG(Accessibility_of_Learning_Resources) + AVG(Passion_and_Enthusiasm) + AVG(Professionalism_and_Ethical_Conduct)) / 10, 2) AS avg_overall, ROUND((COUNT(DISTINCT s.stud_id) * (AVG(Teaching_Effectiveness) + AVG(Course_Organization) + AVG(Accessibility_and_Communication) + AVG(Assessment_and_Grading) + AVG(Respect_and_Inclusivity) + AVG(Engagement_and_Interactivity) + AVG(Feedback_and_Improvement) + AVG(Accessibility_of_Learning_Resources) + AVG(Passion_and_Enthusiasm) + AVG(Professionalism_and_Ethical_Conduct)) / 10), 2) AS weighted_avg_overall FROM evaluation e JOIN student s ON e.stud_id = s.stud_id JOIN class c ON s.class_id = c.class_id JOIN program pr ON c.prog_id = pr.prog_id WHERE s.stud_id IN {tuple(list(filtered_data["stud_id"]))} GROUP BY s.class_id, pr.prog_code, c.class_year, c.class_section, class_info; """) avg_scores_per_class = pd.DataFrame(cursor.fetchall(), columns=[ "class_id", "class_info", "num_respondents", "avg_overall", "weighted_avg_overall" ]) # Calculate the last row's weighted_avg_overall / num_respondents last_row_index = avg_scores_per_class["weighted_avg_overall"].last_valid_index() if last_row_index is not None: avg_scores_per_class.at[last_row_index, "weighted_avg_overall"] /= avg_scores_per_class.at[last_row_index, "num_respondents"] # Convert the column to decimal.Decimal before rounding avg_scores_per_class["weighted_avg_overall"] = avg_scores_per_class["num_respondents"] * avg_scores_per_class["avg_overall"] # avg_scores_per_class["weighted_avg_overall"].apply(lambda x: round(float(x), 2)) # Drop rows with None values avg_scores_per_class = avg_scores_per_class.dropna() # Calculate the overall averages for avg_overall and weighted_avg_overall num_respondents = round(avg_scores_per_class["num_respondents"].sum(), 2) overall_avg_overall = round(avg_scores_per_class["avg_overall"].mean(), 2) overall_weighted_avg_overall = round(avg_scores_per_class["weighted_avg_overall"].sum(),2) weighted_avg_overall = round(overall_weighted_avg_overall / num_respondents,2) # # Append an additional row for avg_overall and weighted_avg_overall # avg_scores_per_class = avg_scores_per_class.append({ # "class_id": int(avg_scores_per_class["class_id"].max()) + 1, # "class_info": "Total", # "num_respondents": avg_scores_per_class["num_respondents"].sum(), # "avg_overall": round(overall_avg_overall, 2), # "weighted_avg_overall": round(overall_weighted_avg_overall / avg_scores_per_class["num_respondents"].sum(), 2) # }, ignore_index=True) # st.write(avg_scores_per_class.style.set_properties(**{'text-align': 'center'})) # Add summary rows to the DataFrame avg_scores_per_class = avg_scores_per_class.append({ "class_id": "", "class_info": "Summary", "num_respondents": num_respondents, "avg_overall": " ", "weighted_avg_overall": overall_weighted_avg_overall }, ignore_index=True) def calculate_satisfaction(weighted_avg_overall): if weighted_avg_overall > 4: return "Outstanding" elif weighted_avg_overall > 3: return "Above Average" elif weighted_avg_overall > 2: return "Average" elif weighted_avg_overall > 1: return "Below Average" else: return "Unsatisfactory" def highlight_cell(col, col_label, row_label): # check if col is a column we want to highlight if col.name == col_label: # a boolean mask where True represents a row we want to highlight mask = (col.index == row_label) # return an array of string styles (e.g. ["", "background-color: yellow"]) return ["background-color: lightgreen" if val_bool else "" for val_bool in mask] else: # return an array of empty strings that has the same size as col (e.g. ["",""]) return np.full_like(col, "", dtype="str") avg_scores_per_class = avg_scores_per_class.append({ "class_id": "", "class_info": "Weighted Avg.", "num_respondents": " ", # You can set this to "N/A" or any appropriate value "avg_overall": calculate_satisfaction(weighted_avg_overall), # You can set this to "N/A" or any appropriate value "weighted_avg_overall": weighted_avg_overall }, ignore_index=True) # st.dataframe(avg_scores_per_class.style.background_gradient(subset=["C"], cmap="RdYlGn", vmin=0, vmax=2.5)) avg_scores_per_class =avg_scores_per_class.style.apply(highlight_cell, col_label="avg_overall", row_label=9) st.write(avg_scores_per_class) st.write(f"Number of respondents: {num_respondents}") st.write(f"Overall weighted avg.: {overall_weighted_avg_overall}") st.write(f"Weighted avg overall: {weighted_avg_overall}") # if st.button("Analyze the results", key="analyze_results"): for subject in subject_avg_scores["sub name"]: with st.expander(subject): subject_filtered_data = filtered_data[filtered_data["sub name"] == subject] promt_txt = "" fig = go.Figure() # st.write(subject_filtered_data) # displays DF for every graphs for criterion, label in zip(column_names, criteria_labels): text = round(subject_filtered_data[criterion].mean(),2) fig.add_trace(go.Bar( x=[label], y=[text], text=text, name=label, )) promt_txt += criterion.replace("_", " ") + ": " + str(text)+ "\n" # st.text(promt_txt) # prompt per graph total_average = subject_filtered_data[column_names].mean(axis=1).mean() total_average_txt = f"{subject} Average Score: {round(total_average,2)}/5" fig.update_layout( barmode="group", width=1000, title=total_average_txt, yaxis_title="Average Score", ) st.plotly_chart(fig) prompt = f"generate a very short insights about this faculty evaluation result for the subject {subject}?\n{promt_txt}\nplease strictly shorten your response in sentence format" # st.text(prompt) while True: with st.spinner("Generating Recommendation"): try: # st.write(g4f_prompt(prompt)) ############################# # pass # break break except Exception as e: # Handle the error (e.g., log it or take appropriate action) # Sleep for a moment before retrying # st.write("Error occurred.. Retrying") pass # time.sleep(0.4) return selected_instructor, selected_instructor_comments, results_to_prompt def evaluation(): try: evaluation_df = fetch_evaluation_data() if evaluation_df is not None: analyze_instructors_results = analyze_instructors(evaluation_df) # if st.button("Analyze comments"): # eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2]) with st.expander("Sentiment Analysis"): eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2]) ############################# # pass except Exception as e: st.error(f"An error occurred: {str(e)}")