#!/usr/bin/env python
# coding: utf-8
"""
Administrative Dashboard for Project Review Scheduler
Provides real-time analytics on reviewer performance, project completion rates,
and workload distribution patterns for executive decision-making.
"""
import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from datetime import datetime, timedelta
import numpy as np
# Configure Streamlit page
st.set_page_config(
page_title="Project Review Analytics Dashboard",
page_icon="📊",
layout="wide",
initial_sidebar_state="expanded"
)
# HARDCODED FILE PATHS - CHANGE THESE TO YOUR ACTUAL PATHS
USERS_FILE = "/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Users.csv"
PROJECTS_FILE = "/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Projects.csv"
REVIEWS_FILE = "/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Reviews.csv"
# Custom CSS for better styling
st.markdown("""
<style>
.metric-card {
background-color: #f0f2f6;
padding: 1rem;
border-radius: 0.5rem;
border-left: 4px solid #1f77b4;
}
.dashboard-header {
background: linear-gradient(90deg, #1f77b4, #ff7f0e);
padding: 1rem;
border-radius: 0.5rem;
color: white;
text-align: center;
margin-bottom: 2rem;
}
</style>
""", unsafe_allow_html=True)
# Dashboard Header
st.markdown("""
<div class="dashboard-header">
<h1> Project Review Analytics Dashboard</h1>
<p>Real-time insights for executive decision-making</p>
</div>
""", unsafe_allow_html=True)
# Sidebar for file uploads and filters
st.sidebar.header("📁 Data Sources")
# File uploaders
users_file = st.sidebar.file_uploader("Upload Users CSV", type=["csv"], key="users")
projects_file = st.sidebar.file_uploader("Upload Projects CSV", type=["csv"], key="projects")
reviews_file = st.sidebar.file_uploader("Upload Reviews CSV", type=["csv"], key="reviews")
# Load and process data
@st.cache_data
def load_data(users_file, projects_file, reviews_file):
"""Load and process uploaded CSV files"""
if users_file and projects_file and reviews_file:
users_df = pd.read_csv(users_file)
projects_df = pd.read_csv(projects_file)
reviews_df = pd.read_csv(reviews_file)
# Data preprocessing
projects_df['Start_Date'] = pd.to_datetime(projects_df['Start_Date'], errors='coerce')
projects_df['Next_Review_Date'] = pd.to_datetime(projects_df['Next_Review_Date'], errors='coerce')
reviews_df['Scheduled_Date'] = pd.to_datetime(reviews_df['Scheduled_Date'], errors='coerce')
reviews_df['Completion_Date'] = pd.to_datetime(reviews_df['Completion_Date'], errors='coerce')
return users_df, projects_df, reviews_df
return None, None, None
def calculate_reviewer_performance(users_df, reviews_df):
"""Calculate comprehensive reviewer performance metrics"""
performance_data = []
for _, user in users_df.iterrows():
user_reviews = reviews_df[reviews_df['Reviewer_ID'] == user['User_ID']]
# Calculate metrics
total_reviews = len(user_reviews)
completed_reviews = len(user_reviews[user_reviews['Status'] == 'Completed'])
overdue_reviews = len(user_reviews[user_reviews['Status'] == 'Overdue'])
in_progress_reviews = len(user_reviews[user_reviews['Status'] == 'In Progress'])
# Calculate completion rate
completion_rate = (completed_reviews / total_reviews * 100) if total_reviews > 0 else 0
# Calculate average review time for completed reviews
completed_with_dates = user_reviews[
(user_reviews['Status'] == 'Completed') &
(user_reviews['Completion_Date'].notna()) &
(user_reviews['Scheduled_Date'].notna())
]
if len(completed_with_dates) > 0:
avg_review_time = (completed_with_dates['Completion_Date'] -
completed_with_dates['Scheduled_Date']).dt.days.mean()
else:
avg_review_time = 0
performance_data.append({
'Reviewer_ID': user['User_ID'],
'Reviewer_Name': user['Name'],
'Department': user.get('Department', 'Unknown'),
'Total_Reviews': total_reviews,
'Completed_Reviews': completed_reviews,
'Overdue_Reviews': overdue_reviews,
'In_Progress_Reviews': in_progress_reviews,
'Completion_Rate': completion_rate,
'Avg_Review_Days': avg_review_time,
'Current_Load': int(user.get('Current_Load', 0))
})
return pd.DataFrame(performance_data)
def calculate_project_metrics(projects_df, reviews_df):
"""Calculate project completion and status metrics"""
total_projects = len(projects_df)
overdue_projects = len(projects_df[projects_df['Status'] == 'Overdue'])
due_soon_projects = len(projects_df[projects_df['Status'] == 'Due Soon'])
up_to_date_projects = len(projects_df[projects_df['Status'] == 'Up to Date'])
# Department-wise breakdown
dept_breakdown = projects_df.groupby('Department')['Status'].value_counts().unstack(fill_value=0)
# Calculate completion rates by department
dept_completion = projects_df.groupby('Department').agg({
'Status': 'count',
'Next_Review_Date': lambda x: sum(pd.to_datetime(x, errors='coerce') < datetime.now())
}).rename(columns={'Status': 'Total_Projects', 'Next_Review_Date': 'Overdue_Count'})
return {
'total_projects': total_projects,
'overdue_projects': overdue_projects,
'due_soon_projects': due_soon_projects,
'up_to_date_projects': up_to_date_projects,
'dept_breakdown': dept_breakdown,
'dept_completion': dept_completion
}
def create_workload_distribution_chart(performance_df):
"""Create interactive workload distribution visualization"""
fig = px.bar(
performance_df,
x='Reviewer_Name',
y='Current_Load',
color='Department',
title='Current Workload Distribution by Reviewer',
labels={'Current_Load': 'Active Reviews', 'Reviewer_Name': 'Reviewer'},
text='Current_Load'
)
fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(
xaxis_tickangle=-45,
height=500,
showlegend=True
)
return fig
def create_performance_heatmap(performance_df):
"""Create performance heatmap showing completion rates by department"""
dept_performance = performance_df.groupby('Department').agg({
'Completion_Rate': 'mean',
'Avg_Review_Days': 'mean',
'Total_Reviews': 'sum'
}).round(2)
fig = go.Figure(data=go.Heatmap(
z=[dept_performance['Completion_Rate'].values],
x=dept_performance.index,
y=['Completion Rate %'],
colorscale='RdYlGn',
text=dept_performance['Completion_Rate'].values,
texttemplate='%{text:.1f}%',
colorbar=dict(title="Completion Rate %")
))
fig.update_layout(
title='Department Performance Heatmap - Completion Rates',
height=300
)
return fig
def create_project_status_pie(project_metrics):
"""Create pie chart for project status distribution"""
labels = ['Up to Date', 'Due Soon', 'Overdue']
values = [
project_metrics['up_to_date_projects'],
project_metrics['due_soon_projects'],
project_metrics['overdue_projects']
]
colors = ['#2E8B57', '#FFD700', '#DC143C']
fig = go.Figure(data=[go.Pie(
labels=labels,
values=values,
marker_colors=colors,
textinfo='label+percent+value',
hole=0.4
)])
fig.update_layout(
title='Project Status Distribution',
height=400
)
return fig
def create_trend_analysis(reviews_df):
"""Create trend analysis for review completion over time"""
# Group by month and status
reviews_df['Month'] = reviews_df['Scheduled_Date'].dt.to_period('M')
monthly_trends = reviews_df.groupby(['Month', 'Status']).size().unstack(fill_value=0)
fig = go.Figure()
for status in monthly_trends.columns:
fig.add_trace(go.Scatter(
x=monthly_trends.index.astype(str),
y=monthly_trends[status],
mode='lines+markers',
name=status,
line=dict(width=3)
))
fig.update_layout(
title='Review Trends Over Time',
xaxis_title='Month',
yaxis_title='Number of Reviews',
height=400,
hovermode='x unified'
)
return fig
# Main dashboard logic
if users_file and projects_file and reviews_file:
users_df, projects_df, reviews_df = load_data(users_file, projects_file, reviews_file)
# Sidebar filters
st.sidebar.header("🔍 Filters")
# Department filter
departments = ['All'] + list(users_df['Department'].unique())
selected_dept = st.sidebar.selectbox("Filter by Department", departments)
# Date range filter
date_range = st.sidebar.date_input(
"Select Date Range",
value=(datetime.now() - timedelta(days=30), datetime.now()),
key="date_range"
)
# Calculate metrics
performance_df = calculate_reviewer_performance(users_df, reviews_df)
project_metrics = calculate_project_metrics(projects_df, reviews_df)
# Apply department filter
if selected_dept != 'All':
performance_df = performance_df[performance_df['Department'] == selected_dept]
projects_df_filtered = projects_df[projects_df['Department'] == selected_dept]
project_metrics = calculate_project_metrics(projects_df_filtered, reviews_df)
# Key Performance Indicators (KPIs)
st.header("🎯 Key Performance Indicators")
col1, col2, col3, col4 = st.columns(4)
with col1:
st.metric(
label="Total Projects",
value=project_metrics['total_projects'],
delta=f"{project_metrics['up_to_date_projects']} up to date"
)
with col2:
avg_completion_rate = performance_df['Completion_Rate'].mean()
st.metric(
label="Avg Completion Rate",
value=f"{avg_completion_rate:.1f}%",
delta=f"{len(performance_df[performance_df['Completion_Rate'] > 80])} high performers"
)
with col3:
st.metric(
label="Overdue Projects",
value=project_metrics['overdue_projects'],
delta=f"{(project_metrics['overdue_projects']/project_metrics['total_projects']*100):.1f}% of total",
delta_color="inverse"
)
with col4:
total_active_reviews = performance_df['Current_Load'].sum()
st.metric(
label="Active Reviews",
value=total_active_reviews,
delta=f"{performance_df['In_Progress_Reviews'].sum()} in progress"
)
# Main dashboard content
st.header("📈 Analytics Dashboard")
# Row 1: Workload Distribution and Performance Heatmap
col1, col2 = st.columns([2, 1])
with col1:
workload_chart = create_workload_distribution_chart(performance_df)
st.plotly_chart(workload_chart, use_container_width=True)
with col2:
performance_heatmap = create_performance_heatmap(performance_df)
st.plotly_chart(performance_heatmap, use_container_width=True)
# Row 2: Project Status and Trends
col1, col2 = st.columns(2)
with col1:
status_pie = create_project_status_pie(project_metrics)
st.plotly_chart(status_pie, use_container_width=True)
with col2:
if len(reviews_df) > 0:
trend_chart = create_trend_analysis(reviews_df)
st.plotly_chart(trend_chart, use_container_width=True)
# Detailed Tables
st.header("📊 Detailed Analytics")
tab1, tab2, tab3 = st.tabs(["Reviewer Performance", "Project Details", "Review History"])
with tab1:
st.subheader("Reviewer Performance Metrics")
st.dataframe(
performance_df.sort_values('Completion_Rate', ascending=False),
use_container_width=True
)
# Download button for performance data
csv_performance = performance_df.to_csv(index=False)
st.download_button(
label="📥 Download Performance Report",
data=csv_performance,
file_name=f"reviewer_performance_{datetime.now().strftime('%Y%m%d')}.csv",
mime="text/csv"
)
with tab2:
st.subheader("Project Status Overview")
st.dataframe(
projects_df[['Project_ID', 'Project_Name', 'Department', 'Status', 'Next_Review_Date']],
use_container_width=True
)
# Department breakdown chart
if 'dept_breakdown' in project_metrics and not project_metrics['dept_breakdown'].empty:
fig_dept = px.bar(
project_metrics['dept_breakdown'].reset_index(),
x='Department',
y=['Up to Date', 'Due Soon', 'Overdue'],
title='Project Status by Department',
barmode='stack'
)
st.plotly_chart(fig_dept, use_container_width=True)
with tab3:
st.subheader("Review Activity Log")
review_display = reviews_df.merge(
users_df[['User_ID', 'Name']],
left_on='Reviewer_ID',
right_on='User_ID',
how='left'
).merge(
projects_df[['Project_ID', 'Project_Name']],
on='Project_ID',
how='left'
)
st.dataframe(
review_display[['Review_ID', 'Project_Name', 'Name', 'Status', 'Scheduled_Date', 'Completion_Date']].rename(
columns={'Name': 'Reviewer_Name'}
).sort_values('Scheduled_Date', ascending=False),
use_container_width=True
)
# Executive Summary
st.header("📋 Executive Summary")
summary_col1, summary_col2 = st.columns(2)
with summary_col1:
st.subheader(" Key Insights")
# Calculate insights
top_performer = performance_df.loc[performance_df['Completion_Rate'].idxmax()] if not performance_df.empty else None
bottleneck_dept = project_metrics['dept_breakdown'].sum(axis=1).idxmax() if 'dept_breakdown' in project_metrics and not project_metrics['dept_breakdown'].empty else None
insights = []
if top_performer is not None:
insights.append(f" Top performer: {top_performer['Reviewer_Name']} ({top_performer['Completion_Rate']:.1f}% completion rate)")
if project_metrics['overdue_projects'] > 0:
insights.append(f" {project_metrics['overdue_projects']} projects require immediate attention")
if bottleneck_dept:
insights.append(f" {bottleneck_dept} department has the highest project volume")
workload_imbalance = performance_df['Current_Load'].std()
if workload_imbalance > 2:
insights.append(f" Workload distribution needs rebalancing (std dev: {workload_imbalance:.1f})")
for insight in insights:
st.write(insight)
with summary_col2:
st.subheader(" Recommendations")
recommendations = []
# Workload recommendations
overloaded_reviewers = performance_df[performance_df['Current_Load'] > performance_df['Current_Load'].mean() + performance_df['Current_Load'].std()]
if not overloaded_reviewers.empty:
recommendations.append("🔄 Redistribute workload from overloaded reviewers")
# Performance recommendations
low_performers = performance_df[performance_df['Completion_Rate'] < 70]
if not low_performers.empty:
recommendations.append(" Provide additional training for underperforming reviewers")
# Project status recommendations
if project_metrics['overdue_projects'] > project_metrics['total_projects'] * 0.1:
recommendations.append(" Implement escalation process for overdue projects")
if avg_completion_rate < 80:
recommendations.append(" Set minimum completion rate targets (80%+)")
for rec in recommendations:
st.write(rec)
else:
st.info(" Please upload Users, Projects, and Reviews CSV files to view the dashboard")
# Sample data format guide
st.subheader("📋 Required Data Format")
col1, col2, col3 = st.columns(3)
with col1:
st.write("**Users.csv**")
st.code("""User_ID,Name,Email,Department,Current_Load
U001,John Doe,john@email.com,IT,3
U002,Jane Smith,jane@email.com,HR,2""")
with col2:
st.write("**Projects.csv**")
st.code("""Project_ID,Project_Name,Department,Status,Next_Review_Date
P001,System Upgrade,IT,Due Soon,2025-06-15
P002,Policy Review,HR,Up to Date,2025-07-01""")
with col3:
st.write("**Reviews.csv**")
st.code("""Review_ID,Project_ID,Reviewer_ID,Status,Scheduled_Date
R001,P001,U001,In Progress,2025-06-10
R002,P002,U002,Completed,2025-05-15""")
# Footer
st.markdown("---")
st.markdown(
"<div style='text-align: center; color: #666;'>"
"Project Review Analytics Dashboard | Built with Streamlit & Plotly"
"</div>",
unsafe_allow_html=True
)
2025-06-07 07:45:31.453 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.454 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.454 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.455 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.455 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.455 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.455 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.456 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.456 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.456 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.457 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.457 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.457 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.457 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.457 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.458 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.459 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.459 No runtime found, using MemoryCacheStorageManager
2025-06-07 07:45:31.460 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.461 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.461 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.461 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.461 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.461 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.462 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.462 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.462 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.463 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.463 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.463 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.463 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.464 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.465 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.465 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.465 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.465 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
2025-06-07 07:45:31.466 Thread 'MainThread': missing ScriptRunContext! This warning can be ignored when running in bare mode.
DeltaGenerator()
# Test loading your files
users_df = pd.read_csv("/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Users.csv")
projects_df = pd.read_csv("/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Projects.csv")
reviews_df = pd.read_csv("/Users/cynthiamcginnis/Documents/ProjectReviewScheduler/Reviews.csv")
print(" Files loaded successfully!")
print(f"Users: {len(users_df)} rows")
print(f"Projects: {len(projects_df)} rows")
print(f"Reviews: {len(reviews_df)} rows")
# Show first few rows
print("\nUsers data:")
print(users_df.head())
Files loaded successfully!
Users: 10 rows
Projects: 25 rows
Reviews: 6 rows
Users data:
User_ID Name Email Department \
0 U001 Deborah Munoz zstrong@example.org QA
1 U002 Jennifer Johnson ann40@example.net Finance
2 U003 Steven Stephenson stevensonstephanie@example.com IT
3 U004 Stephanie Berry robertblack@example.com QA
4 U005 Sarah Jones debbiemorris@example.com IT
Current_Load
0 5
1 6
2 5
3 5
4 6