Managing Non-Operating Schools in the Region Redistribution Project

Overview

This document outlines the recommended approach for tracking and managing schools that are no longer operating in the Region Redistribution Project. The solution maintains data integrity while ensuring that redistribution algorithms only consider active schools.

Current Data Structure

The project already includes an is_active column in the schools.csv file. This is the primary field for tracking school operational status:

name,DOE_code,street_address,street_address_2,city,zip,county,lat,lon,enrollment,size_tier,current_rm,assigned_rm,is_active
ABC School,12345,123 Main St,,Miami,33101,DADE,25.7617,-80.1918,200,Medium,Some RM,Some RM,TRUE

Recommended Implementation

1. Update Process Flow

1. Monthly Status Updates: Obtain updated school operational status from Florida DOE 2. Status Tracking File: Maintain a separate file for changes 3. Data Update: Update the main schools.csv file with new status 4. Redistribution: Ensure inactive schools are excluded from active assignments 5. Historical Tracking: Maintain logs of status changes

2. Status Tracking File

Create a school_closures.csv file to track school closures:

DOE_code,name,closure_date,closure_reason,rm_handling_closure,closure_notes,updated_date
12345,ABC School,2024-12-01,Financial issues,Amanda McCook,Final closure,2024-12-02
67890,XYZ Academy,2024-11-15,Consolidation with other school,Jalem Robinson,Contact parents by Dec 15,2024-11-16

3. Update Script

Create a script update_school_status.py:

"""
update_school_status.py

Script to update school operational status based on closure information.
"""

import pandas as pd
from datetime import datetime
import os

def update_school_status(closure_file='school_closures.csv', 
                        schools_file='schools.csv', 
                        output_file='schools.csv'):
    """
    Update school status based on closure information
    
    Parameters:
    - closure_file: CSV with school closure information
    - schools_file: Main schools data file
    - output_file: Output file to save updated data
    """
    
    # Check if files exist
    if not os.path.exists(closure_file):
        print(f"Warning: {closure_file} does not exist. No status updates applied.")
        return
    
    if not os.path.exists(schools_file):
        print(f"Error: {schools_file} does not exist.")
        return
    
    # Load existing school data
    schools_df = pd.read_csv(schools_file, encoding='utf-8-sig')
    
    # Load closure updates
    closure_updates = pd.read_csv(closure_file)
    
    print(f"Processing {len(closure_updates)} school closure updates")
    
    # Count updates
    updates_applied = 0
    
    # Update the is_active status and add closure information
    for _, closure in closure_updates.iterrows():
        mask = schools_df['DOE_code'] == closure['DOE_code']
        if mask.any():
            schools_df.loc[mask, 'is_active'] = False
            schools_df.loc[mask, 'closure_date'] = closure.get('closure_date', '')
            schools_df.loc[mask, 'closure_reason'] = closure.get('closure_reason', '')
            schools_df.loc[mask, 'rm_handling_closure'] = closure.get('rm_handling_closure', '')
            schools_df.loc[mask, 'closure_notes'] = closure.get('closure_notes', '')
            updates_applied += 1
        else:
            print(f"Warning: DOE_code {closure['DOE_code']} not found in schools.csv")
    
    # Save updated schools file
    schools_df.to_csv(output_file, index=False, encoding='utf-8-sig')
    print(f"Updated school status for {updates_applied} schools")
    
    # Summary statistics
    active_count = len(schools_df[schools_df['is_active'] == True])
    inactive_count = len(schools_df[schools_df['is_active'] == False])
    print(f"Active schools: {active_count}")
    print(f"Inactive schools: {inactive_count}")
    print(f"Total schools: {len(schools_df)}")

def get_status_summary(schools_file='schools.csv'):
    """
    Get a summary of school status
    """
    schools_df = pd.read_csv(schools_file, encoding='utf-8-sig')
    
    active_count = len(schools_df[schools_df['is_active'] == True])
    inactive_count = len(schools_df[schools_df['is_active'] == False])
    
    print(f"School Status Summary for {schools_file}")
    print(f"-" * 40)
    print(f"Active schools: {active_count}")
    print(f"Inactive schools: {inactive_count}")
    print(f"Total schools: {len(schools_df)}")
    
    if inactive_count > 0:
        print(f"\nRecent closures:")
        inactive_schools = schools_df[schools_df['is_active'] == False].copy()
        for _, school in inactive_schools.head(10).iterrows():
            print(f"  - {school['name']} (DOE: {school['DOE_code']}) - Closed: {school.get('closure_date', 'Unknown')}")

if __name__ == "__main__":
    # Update status based on closure file
    update_school_status()
    
    # Display summary
    get_status_summary()

4. Integration with Redistribution Algorithm

Modify the final_redistribution.py script to work with only active schools:

def load_data():
    """Load and prepare data, filtering for active schools only"""
    print_section("PHASE 1: SETUP & DATA LOADING")

    # Load RMs
    print("\n1. Loading RMs from rms.csv...")
    rms_df = pd.read_csv('rms.csv', encoding='utf-8-sig')

    # Separate AD (Hialeah carve-out)
    ad_rm = rms_df[rms_df['role_type'] == 'AD'].iloc[0]
    rms_df = rms_df[rms_df['role_type'] != 'AD'].copy()

    print(f"   Loaded {len(rms_df)} RMs (excluding AD)")
    print(f"   AD: {ad_rm['name']} (Hialeah carve-out only)")

    # Load schools and separate by active status
    print("\n2. Loading schools from schools.csv...")
    schools_df = pd.read_csv('schools.csv', encoding='utf-8-sig')
    
    # Separate active and inactive schools for processing
    active_schools = schools_df[schools_df['is_active'] == True].copy()
    inactive_schools = schools_df[schools_df['is_active'] == False].copy()
    
    print(f"   Total schools: {len(schools_df)}")
    print(f"   Active schools: {len(active_schools)}")
    print(f"   Inactive schools: {len(inactive_schools)}")

    # Apply Hialeah carve-out to active schools only
    print("\n3. Applying Hialeah carve-out...")
    hialeah_cities = ['HIALEAH', 'HIALEAH GARDENS']
    hialeah_mask = active_schools['city'].str.upper().isin(hialeah_cities)
    hialeah_schools = active_schools[hialeah_mask].copy()
    redistribution_schools = active_schools[~hialeah_mask].copy()

    print(f"   Hialeah carve-out: {len(hialeah_schools)} schools --> {ad_rm['name']} (AD)")
    print(f"   For redistribution: {len(redistribution_schools)} schools")

    # Continue with the rest of the function...

5. Monthly Status Update Process

1. Receive Updates: Obtain monthly school status updates from Florida DOE 2. Add to Tracking File: Add new closures to school_closures.csv 3. Run Update Script: Execute python update_school_status.py 4. Verify Changes: Run python update_school_status.py to see summary 5. Update Documentation: Update status logs if needed

6. Annual Rebalancing Considerations

During the annual July rebalancing:

7. RM Notifications

Implement a process to notify RMs about school closures in their territories by checking the rm_handling_closure field and sending appropriate notifications.

Benefits of This Approach

1. Data Integrity: Maintains historical data while tracking current status 2. Operational Efficiency: Automatically excludes inactive schools from assignments 3. Historical Tracking: Maintains full history of school closures for analysis 4. Integration Ready: Works seamlessly with existing redistribution algorithms 5. Audit Trail: Clear tracking of all status changes with dates and reasons 6. Scalability: Easy to expand with additional status fields as needed

Next Steps

1. Add the is_active field to all schools if not already present 2. Create the initial school_closures.csv file for known closures 3. Implement the update script in your workflow 4. Integrate status checking into the redistribution process 5. Establish the monthly update process with Florida DOE