app5_selectbox/evaluation copy 2.py

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