The Project Review Scheduler is an automated solution designed to address critical operational challenges in our company’s project review process. Currently, our review process is managed manually through Excel spreadsheets, creating inefficiencies that impact both productivity and compliance.
According to research by KPMG (2016), approximately 70% of project organizations still rely on manual processes for project reporting, which significantly hampers decision-making capabilities. Our internal audits have revealed similar inefficiencies, with approximately 15% of projects requiring periodic review being completely overlooked, while 30% undergo unnecessary multiple reviews.
The Project Review Scheduler aims to solve these problems through a Python-based application that automates the project review process with the following key components:
By implementing this solution, we expect to eliminate missed reviews, reduce administrative time spent on scheduling by at least 70%, ensure fair workload distribution among reviewers, and provide management with clear visibility into the review process (Montgomery, 2000).
This documentation outlines the implementation plan for the Project Review Scheduler, including requirements analysis, database design, core functionality development, user interface creation, testing procedures, and deployment strategy.
# Create CVS files for 'projects', 'users', and 'reviews
import pandas as pd
import numpy as np
from datetime import datetime
import os
# Create directory
os.makedirs("review_scheduler_data", exist_ok=True)
# Projects.csv
projects = pd.DataFrame({
"Project ID": range(1, 11),
"Project Name": [f"Project {chr(65+i)}" for i in range(10)],
"Start Date": pd.date_range(start="2021-01-01", periods=10, freq='180D'),
"Last Review Date": pd.date_range(start="2022-01-01", periods=10, freq='180D'),
"Review Frequency (Years)": np.random.choice([1, 2], size=10)
})
projects.to_csv("review_scheduler_data/Projects.csv", index=False)
# Users.csv
users = pd.DataFrame({
"User ID": range(1, 6),
"Name": [f"Reviewer {i}" for i in range(1, 6)],
"Department": ["Tech", "Operations", "HR", "Finance", "Compliance"],
"Email": [f"reviewer{i}@company.com" for i in range(1, 6)],
"Current Load": [0]*5
})
users.to_csv("review_scheduler_data/Users.csv", index=False)
# Reviews.csv
reviews = pd.DataFrame(columns=[
"Review ID", "Project ID", "Reviewer ID", "Scheduled Date", "Status", "Completion Date"
])
reviews.to_csv("review_scheduler_data/Reviews.csv", index=False)
print("✅ CSV files created in 'review_scheduler_data/' folder.")
✅ CSV files created in 'review_scheduler_data/' folder.This component automatically calculates review status for all projects based on configured review frequencies, following recommendations from Montgomery (2000) regarding quality assessment improvement processes.
import pandas as pd
from datetime import datetime
# Load data
projects = pd.read_csv("review_scheduler_data/Projects.csv", parse_dates=["Start Date", "Last Review Date"])
today = pd.to_datetime("today")
# Calculate next review date
projects["Next Review Date"] = projects["Last Review Date"] + pd.to_timedelta(projects["Review Frequency (Years)"] * 365, unit='D')
projects["Days Until Review"] = (projects["Next Review Date"] - today).dt.days
projects["Status"] = projects["Days Until Review"].apply(
lambda x: "Overdue" if x < 0 else ("Due Soon" if x <= 30 else "Up to Date")
)
projects.to_csv("review_scheduler_data/Projects_With_Status.csv", index=False)
print("✅ Review status updated.")✅ Review status updated.This component implements an optimized reviewer assignment system that addresses the workload imbalance identified in our problem statement where “some team members are overburdened with multiple concurrent reviews while others remain underutilized” (KPMG, 2016).
import pandas as pd
# Load data
projects = pd.read_csv("review_scheduler_data/Projects_With_Status.csv")
users = pd.read_csv("review_scheduler_data/Users.csv")
reviews = pd.read_csv("review_scheduler_data/Reviews.csv")
due_projects = projects[projects["Status"].isin(["Overdue", "Due Soon"])]
# Assign reviewers
review_assignments = []
review_id_start = len(reviews) + 1
for _, proj in due_projects.iterrows():
user = users.sort_values("Current Load").iloc[0]
review_assignments.append({
"Review ID": review_id_start,
"Project ID": proj["Project ID"],
"Reviewer ID": user["User ID"],
"Scheduled Date": proj["Next Review Date"],
"Status": "Scheduled",
"Completion Date": ""
})
users.loc[users["User ID"] == user["User ID"], "Current Load"] += 1
review_id_start += 1
new_reviews = pd.DataFrame(review_assignments)
reviews = pd.concat([reviews, new_reviews], ignore_index=True)
users.to_csv("review_scheduler_data/Users.csv", index=False)
reviews.to_csv("review_scheduler_data/Reviews.csv", index=False)
print("✅ Reviewers assigned.")
✅ Reviewers assigned.This component implements an automated communication system that sends timely notifications to assigned reviewers, addressing one of the key inefficiencies identified in our manual process (KPMG, 2016).
## Email Notification System
import pandas as pd
from faker import Faker
# Initialize Faker
fake = Faker()
Faker.seed(42) # For reproducible results
# Load data from CSV files
projects = pd.read_csv("review_scheduler_data/Projects.csv")
users = pd.read_csv("review_scheduler_data/Users.csv")
reviews = pd.read_csv("review_scheduler_data/Reviews.csv")
# Example data (in a real implementation, this would come from the CSVs)
# Create sample projects with realistic names
sample_projects = [
{"Project ID": 101, "Project Name": "Cloud Infrastructure Migration"},
{"Project ID": 102, "Project Name": "Customer Portal Redesign"},
{"Project ID": 103, "Project Name": "Data Warehouse Optimization"},
{"Project ID": 104, "Project Name": "Mobile App Development"},
{"Project ID": 105, "Project Name": "Network Security Enhancement"}
]
# Create sample users with realistic names
sample_users = [
{"User ID": 1, "Name": "Jennifer Ramirez", "Email": "jennifer.ramirez@company.com", "Department": "IT"},
{"User ID": 2, "Name": "Michael Chen", "Email": "michael.chen@company.com", "Department": "Engineering"},
{"User ID": 3, "Name": "Sarah Johnson", "Email": "sarah.johnson@company.com", "Department": "QA"},
{"User ID": 4, "Name": "David Williams", "Email": "david.williams@company.com", "Department": "Product"},
{"User ID": 5, "Name": "Aisha Patel", "Email": "aisha.patel@company.com", "Department": "Operations"}
]
# Create sample reviews
sample_reviews = [
{"Review ID": 1, "Project ID": 101, "Reviewer ID": 3, "Scheduled Date": "2025-06-15", "Status": "Scheduled"},
{"Review ID": 2, "Project ID": 102, "Reviewer ID": 1, "Scheduled Date": "2025-06-20", "Status": "Scheduled"},
{"Review ID": 3, "Project ID": 103, "Reviewer ID": 4, "Scheduled Date": "2025-06-12", "Status": "Scheduled"},
{"Review ID": 4, "Project ID": 104, "Reviewer ID": 5, "Scheduled Date": "2025-06-25", "Status": "Scheduled"},
{"Review ID": 5, "Project ID": 105, "Reviewer ID": 2, "Scheduled Date": "2025-06-18", "Status": "Scheduled"}
]
# Convert sample data to DataFrames (for demonstration)
projects = pd.DataFrame(sample_projects)
users = pd.DataFrame(sample_users)
reviews = pd.DataFrame(sample_reviews)
# Merge review data with user and project information
merged = reviews.merge(users, left_on="Reviewer ID", right_on="User ID")
merged = merged.merge(projects[["Project ID", "Project Name"]], on="Project ID")
# Send email notifications
for _, row in merged.iterrows():
subject = f"Review Assignment: {row['Project Name']}"
body = f"""
Hi {row['Name']},
You have been assigned to review '{row['Project Name']}' scheduled for {row['Scheduled Date']}.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in {row['Department']} will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
"""
print(f"To: {row['Email']}")
print(f"Subject: {subject}")
print(body)
print("------")To: sarah.johnson@company.com
Subject: Review Assignment: Cloud Infrastructure Migration
Hi Sarah Johnson,
You have been assigned to review 'Cloud Infrastructure Migration' scheduled for 2025-06-15.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in QA will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
------
To: jennifer.ramirez@company.com
Subject: Review Assignment: Customer Portal Redesign
Hi Jennifer Ramirez,
You have been assigned to review 'Customer Portal Redesign' scheduled for 2025-06-20.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in IT will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
------
To: david.williams@company.com
Subject: Review Assignment: Data Warehouse Optimization
Hi David Williams,
You have been assigned to review 'Data Warehouse Optimization' scheduled for 2025-06-12.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in Product will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
------
To: aisha.patel@company.com
Subject: Review Assignment: Mobile App Development
Hi Aisha Patel,
You have been assigned to review 'Mobile App Development' scheduled for 2025-06-25.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in Operations will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
------
To: michael.chen@company.com
Subject: Review Assignment: Network Security Enhancement
Hi Michael Chen,
You have been assigned to review 'Network Security Enhancement' scheduled for 2025-06-18.
This review is part of our regular quality assessment process as outlined in our
project management framework. Your expertise in Engineering will help
ensure the project meets our organizational standards.
Please ensure the review is completed on time and document your findings in
the project review portal.
Regards,
Project Scheduler System
------