# 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] # for i in range(len(column_names)): # column_names[i] = column_names[i].replace("_"," ") # st.write(column_names) # .replace("_"," ") 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() # Get the column names from the cursor description criteria_columns = [f"score_criteria_{i}" for i in range(10)] column_names = [column[0].replace("_"," ") for column in cursor.description][4:14] # Print the column names instructor_avg_scores = df.groupby("inst id")[column_names].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") # Create a select box to filter by instructor selected_instructor = st.selectbox("Select Instructor", instructor_avg_scores["instructor name"].unique()) # Filter data based on the selected instructor filtered_data = df[df["inst id"] == instructor_avg_scores[instructor_avg_scores["instructor name"] == selected_instructor]["inst id"].values[0]] # st.write(filtered_data[filtered_data.columns[4:15]]) # st.write(selected_instructor) selected_instructor_comments = list(filtered_data["comments"]) # st.write(selected_instructor_comments) #get all comments fro the instructor st.subheader(f"Evaluated by: {len(selected_instructor_comments)} students") # Join with the subj_inst and subject tables to get subject names 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") # # Group data by subject and calculate average scores per criteria # subject_avg_scores = filtered_data.groupby("sub_name").agg({ # "score_criteria_1": "mean", # "score_criteria_2": "mean", # "score_criteria_3": "mean" # }).reset_index() # criteria_columns = [f"score_criteria_{i}" for i in range(10)] subject_avg_scores = filtered_data.groupby("sub name")[column_names].mean().reset_index() # # Calculate the total average score for each subject # subject_avg_scores["total_average"] = subject_avg_scores[["score_criteria_1", "score_criteria_2", "score_criteria_3"]].mean(axis=1) # criteria_columns = [f"score_criteria_{i}" for i in range(10)] subject_avg_scores["total average"] = subject_avg_scores[column_names].mean(axis=1) # Create a grouped bar chart for average scores per criteria by subject 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) return selected_instructor, selected_instructor_comments 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]) except Exception as e: st.error(f"An error occurred while fetching evaluation data: {str(e)}")