Spaces:
Paused
Paused
| # 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)}") | |