app.py

from supabase import create_client
import pandas as pd
import streamlit as st
import plotly.express as px
import datetime
import numpy as np
import os
import json

#page configs
st.set_page_config(layout="centered", page_icon="🧭", page_title="CSweet Metrics")
st.markdown("<h1 style='text-align: center; color: grey;'>CSweet Analysis</h1>", unsafe_allow_html=True)
hide_menu_style = """
        <style>
        #MainMenu {visibility: hidden;}
        </style>
        """
st.markdown(hide_menu_style, unsafe_allow_html=True)

API_URL = os.getenv("API_URL")
API_KEY = os.getenv("API_KEY")


supabase = create_client(API_URL, API_KEY)

#input
col1, col2 = st.columns(2)
with col1:
  d = st.date_input(
      "Choose start date",
      datetime.date(2023, 1, 1))
  dt64_start = np.datetime64(d)
  st.write('Start Date:', d)
with col2:
  e = st.date_input(
      "Choose end date",
      datetime.date(2023, 3, 2))
  st.write('End Date:', e)
  dt64_end = np.datetime64(e)


#raw data process
query_results = supabase.table("user_checkpoints").select('checkpoint_name,completed_by(full_name),completed_at').execute()
list_set = []
for data in query_results:
  # print(type(data[1]))
  for entry in data[1]:
    data = []
    data.append(entry['checkpoint_name'])
    data.append(entry['completed_at'])
    
    data.append(entry['completed_by']['full_name'])
    list_set.append(data)
  break
data = pd.DataFrame(list_set)
data.columns = ['module', 'completed_time', 'name']
#add roles and managers
query_results = supabase.table("profiles").select('full_name,managed_by(full_name),role').execute()
manager_role_lookup = {}
for _data in query_results:
  for entry in _data[1]:
    _data = []
    try: manager_role_lookup[entry['full_name']]= {'role': entry['role'], 'manager': entry['managed_by']['full_name']}
    except: pass
  break
#transform dataframe
def calculate_avg_completion_rate(x):
    if len(x) <= 1:
        return 0
    agg_completion_rate = 0
    for i in range(0,len(x)-2):
        print(x[i],x[i+1],pd.Timedelta(x[i+1] - x[i]).seconds,'\n')
        agg_completion_rate += (pd.Timedelta(x[i+1] - x[i]).seconds)

    return agg_completion_rate/(len(x))
data['completed_time'] = pd.to_datetime(data['completed_time'])
data['completed_time'] = pd.to_datetime(data.completed_time).dt.tz_localize(None)
print(data.iloc[0])
data = data[(data['completed_time']> dt64_start) & (data['completed_time'] < dt64_end)]
data = data.groupby(["name"]).agg(list).reset_index()
data['modules completed'] = data.apply(lambda x: len(x['completed_time']),axis=1)
data['completion rate'] = data.apply(lambda x: calculate_avg_completion_rate(x['completed_time']),axis=1)
data["completion rate"] = (data["completion rate"] - data["completion rate"].mean()) / (data["completion rate"].max() - data["completion rate"].min())
data['manager'] = data.apply(lambda x: manager_role_lookup[x['name']]['manager'] if x['name'] in manager_role_lookup else x['name'],axis=1)
data['role'] = data.apply(lambda x: manager_role_lookup[x['name']]['role'] if x['name'] in manager_role_lookup else x['name'],axis=1)
data = data.sort_values(by=['modules completed'], ascending=False)

normalized_data = data.copy()#normalized data for efficiency modeling
normalized_data["modules completed"] = (normalized_data["modules completed"] - normalized_data["modules completed"].mean()) / (normalized_data["modules completed"].max() - normalized_data["modules completed"].min())
normalized_data['efficiency score'] = normalized_data.apply(lambda x: x['completion rate']+ x['modules completed'],axis=1)
normalized_data = normalized_data.sort_values(by=['efficiency score'], ascending=False)

#manager agg
manager_data = normalized_data.groupby(["manager"]).agg(list).reset_index()
manager_data['efficiency score'] = manager_data['efficiency score'].apply(lambda x:sum(x)/len(x))
manager_data = manager_data.sort_values(by=['efficiency score'], ascending=False)

#display
tab1, tab2 = st.tabs(["Raw Data", "Efficiency Modeling"])
with tab1:
  with st.expander("See person data"):
      st.dataframe(normalized_data[['name','role','manager','modules completed','completion rate','efficiency score','module']])
  fig1 = px.bar(data, x='name', y='modules completed', color='manager',title="Modules Completed")
  fig2 = px.scatter(data, x='modules completed', y='completion rate',  color='manager',custom_data=['name', 'role', 'manager'],title="Modules Completed vs Completion Rate")
  fig2.update_traces(
      hovertemplate="<br>".join([
          "modules: %{x}",
          "completion: %{y}",
          "Name: %{customdata[0]}",
          "Role: %{customdata[1]}",
          "Manager: %{customdata[2]}",
      ])
  )
  st.plotly_chart(fig1, use_container_width=True)
  st.plotly_chart(fig2, use_container_width=True)
with tab2:
  with st.expander("See manager data"):
    st.dataframe(manager_data[['manager','efficiency score','name','role']])
  with st.expander("See person data"):
      st.dataframe(normalized_data[['name','role','manager','modules completed','completion rate','efficiency score','module']])
  
  fig3 = px.box(normalized_data, x="manager", y="efficiency score",title="Manager Efficiency Distribution")
  st.plotly_chart(fig3, use_container_width=True)
  fig4 = px.bar(manager_data, x="manager", y="efficiency score",title="Manager Efficiency Distribution")
  # fig4.update_layout(xaxis={'categoryorder':'total descending'})
  st.plotly_chart(fig4, use_container_width=True)
  fig5 = px.bar(normalized_data, x="name", y="efficiency score",color="manager",title="Efficiency Score by Person")
  # fig4.update_layout(xaxis={'categoryorder':'total descending'})
  st.plotly_chart(fig5, use_container_width=True)






#normalize completion rate
#normalize modules completed
#create third column that sums above two

#analyze daily roles