# evaluation.py import streamlit as st import pandas as pd from app5_selectbox.database_con import cursor, db_connection from app5_selectbox.app5_selectbox_func import display_table, generate_unique_4 from app5_selectbox.evaluation_analysis import eval_analysis import matplotlib.pyplot as plt import seaborn as sns import plotly.express as px import plotly.graph_objs as go # Function to perform analytics on instructors def analyze_instructors(cursor): try: # Execute the SQL query to fetch the evaluation data cursor.execute("SELECT * FROM evaluation") evaluation_data = cursor.fetchall() if not evaluation_data: st.warning("No evaluation data found.") else: # Create a DataFrame from the fetched data and set column names column_names = [i[0].replace("_", " ") for i in cursor.description] df = pd.DataFrame(evaluation_data, columns=column_names) # Get the column names for the score criteria criteria_columns = [f"score_criteria_{i}" for i in range(10)] column_names = [column[0].replace("_", " ") for column in cursor.description][4:14] # Define criteria labels globally criteria_labels = [(f"{column_names[i]}", f"{column_names[i]}".replace("_", " ")) for i in range(10)] instructor_avg_scores = df.groupby("inst id")[column_names].mean().reset_index() cursor.execute("SELECT inst_id, inst_name FROM instructor") instructor_data = cursor.fetchall() instructor_df = pd.DataFrame(instructor_data, columns=["inst id", "instructor name"]) instructor_avg_scores = instructor_avg_scores.merge(instructor_df, on="inst id", how="left") selected_instructor = st.selectbox("Select Instructor", instructor_avg_scores["instructor name"].unique()) filtered_data = df[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.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 """) subject_data = cursor.fetchall() subject_df = pd.DataFrame(subject_data, columns=["subj inst id", "sub name"]) filtered_data = filtered_data.merge(subject_df, on="subj inst id", how="left") 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 [("score_criteria_1", "Criteria 1"), ("score_criteria_2", "Criteria 2"), ("score_criteria_3", "Criteria 3")]: # fig.add_trace(go.Bar( # x=subject_avg_scores["sub_name"], # y=subject_avg_scores[criterion], # name=label, # )) criteria_labels = [(f"{column_names[i]}", f"{column_names[i]}".replace("_", " ")) for i in range(10)] for criterion, label in 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 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", )) # Display the overall average of all subjects overall_average = subject_avg_scores["total average"].mean() # st.write(f"Overall Average Score (All Subjects): {overall_average:.2f}") fig.update_layout( barmode="group", title=f"Average Scores per Criteria by Subject for Instructor: {selected_instructor}", xaxis_title=f"Overall Average Score (All Subjects): {overall_average:.2f}", yaxis_title="Average Score", ) st.plotly_chart(fig) # st.write("**Average score per Criteria**") results_to_prompt = "Average score per Criteria\n" criteria_averages = [] for criteria in filtered_data.columns[4:14]: average_score = round(sum(filtered_data[criteria] / len(filtered_data)), 2) criteria_averages.append((criteria, average_score)) results_to_prompt += f"{criteria}: {average_score}/5, \n" # print(results_to_prompt) # st.write(results_to_prompt) # # Create a Plotly bar chart fig = go.Figure() fig.add_trace(go.Bar( x=[criteria for criteria, _ in criteria_averages], y=[score for _, score in criteria_averages], text=[f"{score}/5" for _, score in criteria_averages], # textposition='outside', )) fig.update_layout( title="Average Score per Criteria", xaxis_title="Criteria", yaxis_title="Average Score", ) st.plotly_chart(fig) for subject in subject_avg_scores["sub name"]: subject_filtered_data = filtered_data[filtered_data["sub name"] == subject] fig = go.Figure() st.write(subject_filtered_data) for criterion, label in criteria_labels: fig.add_trace(go.Bar( x=[label], y=[subject_filtered_data[criterion].mean()], text=[subject_filtered_data[criterion].mean()], name=label, )) # Calculate the "total average" based on criteria columns total_average = subject_filtered_data[column_names].mean(axis=1).mean() # # dot point for Total Average" # fig.add_trace(go.Scatter( # x=[label], # y=[total_average], # mode="markers+text", # text=[round(total_average, 2)], # textposition="top center", # textfont=dict(size=14), # marker=dict(size=10, color="black"), # name="Total Average", # )) fig.update_layout( barmode="group", title=f"{subject} Average Score: {total_average:.2f}", # xaxis_title=f"Overall Average Score: {total_average:.2f}", yaxis_title="Average Score", ) st.plotly_chart(fig) # selected_instructor_comments.append(results_to_prompt) # st.write(selected_instructor_comments) return selected_instructor, selected_instructor_comments, results_to_prompt except Exception as e: st.error(f"An error occurred during data analytics: {str(e)}") # try: # # Execute the SQL query to fetch the evaluation data # cursor.execute("SELECT * FROM evaluation") # evaluation_data = cursor.fetchall() # if not evaluation_data: # st.warning("No evaluation data found.") # else: # # Create a DataFrame from the fetched data and set column names # column_names = [i[0] for i in cursor.description] # df = pd.DataFrame(evaluation_data, columns=column_names) # # Group data by instructor and calculate average scores per criteria # instructor_avg_scores = df.groupby("inst_id").agg({ # "score_criteria_1": "mean", # "score_criteria_2": "mean", # "score_criteria_3": "mean" # }).reset_index() # # Join with instructor data to get their names # cursor.execute("SELECT inst_id, inst_name FROM instructor") # instructor_data = cursor.fetchall() # instructor_df = pd.DataFrame(instructor_data, columns=["inst_id", "instructor_name"]) # instructor_avg_scores = instructor_avg_scores.merge(instructor_df, on="inst_id", how="left") # # Join with subj_inst and subject tables to get subject names # cursor.execute("SELECT si.subj_inst_id, s.sub_name FROM subj_inst si INNER JOIN subject s ON si.sub_id_code = s.sub_id_code") # subject_data = cursor.fetchall() # subject_df = pd.DataFrame(subject_data, columns=["subj_inst_id", "sub_name"]) # df = df.merge(subject_df, on="subj_inst_id", how="left") # # Create a select box to filter by instructor and subject # selected_instructor = st.selectbox("Select Instructor", instructor_avg_scores["instructor_name"].unique()) # selected_subjects = df[df["inst_id"] == instructor_avg_scores[instructor_avg_scores["instructor_name"] == selected_instructor]["inst_id"].values[0]]["sub_name"].unique() # selected_subject = st.selectbox("Select Subject", selected_subjects) # # Filter data based on the selected instructor and subject # filtered_data = df[(df["inst_id"] == instructor_avg_scores[instructor_avg_scores["instructor_name"] == selected_instructor]["inst_id"].values[0]) & # (df["sub_name"] == selected_subject)] # # Create a bar chart for average scores per criteria # fig = px.bar(instructor_avg_scores, x="instructor_name", # y=["score_criteria_1", "score_criteria_2", "score_criteria_3"], # labels={"value": "Average Score", "variable": "Criteria"}, # title="Average Scores per Criteria by Instructor") # st.plotly_chart(fig) # # Group data by subject instructor and calculate average scores # subject_avg_scores = filtered_data.groupby("sub_name").agg({ # "score_criteria_1": "mean", # "score_criteria_2": "mean", # "score_criteria_3": "mean" # }).reset_index() # # Create a bar chart for average scores per criteria for the selected subject # fig = px.bar(subject_avg_scores, x="sub_name", # y=["score_criteria_1", "score_criteria_2", "score_criteria_3"], # labels={"value": "Average Score", "variable": "Criteria"}, # title=f"Average Scores per Criteria for Subject {selected_subject}") # st.plotly_chart(fig) # except Exception as e: # st.error(f"An error occurred during data analytics: {str(e)}") def evaluation(cursor, table_name): try: # Execute the SQL query to fetch the evaluation data cursor.execute("SELECT * FROM evaluation") evaluation_data = cursor.fetchall() if not evaluation_data: st.warning("No evaluation data found.") else: # Create a DataFrame from the fetched data and set column names column_names = [i[0] for i in cursor.description] df = pd.DataFrame(evaluation_data, columns=column_names) # # Display the table with centered text # st.header(f"{table_name} Table") # st.dataframe(df.style.set_properties(**{'text-align': 'center'})) analyze_instructors_results = analyze_instructors(cursor) if st.button("Analyze comments"): # st.write(analyze_instructors_results[0], analyze_instructors_results[1]) eval_analysis(analyze_instructors_results[0], analyze_instructors_results[1], analyze_instructors_results[2]) except Exception as e: st.error(f"An error occurred while fetching evaluation data: {str(e)}")