rgrs.xyz

Automating CAP Orientation Flight Slotting with Points

4 min read
code Civil Air Patrol

Automate fair, transparent CAP orientation flight slotting with a points-based system that considers flight history and time since last flight.

Fair, Fast, and Transparent: Automating CAP Orientation Flight Slotting with Points

Assigning Civil Air Patrol (CAP) cadet orientation flights can be a juggling act—balancing fairness, recency, and limited aircraft availability. To make this easier, I built a small Python tool that calculates priority points from official orientation flight reports and produces a ready-to-use slotting list.

This post walks through the problem, the solution, how the algorithm works, and exactly how to use it. You’ll find the source code here: abnormalend/cap-oflight-points.

Why I Built This

  • Fairness for first flights: Ensure cadets with no powered flights get a meaningful boost.
  • Progress toward completion: Favor cadets who are closest to finishing powered O-Flights.
  • Recency matters: Account for how long it’s been since a cadet’s last flight (or their join date if they haven’t flown yet).
  • Faster planning: Produce a clear, prioritized list of primaries and alternates for mission staff—fast.

What It Does

  • Reads inputs: The CAP Orientation Flights report CSV and a list of CAP IDs to slot.
  • Computes points: Uses flight history and time since last flight to compute a fair score.
  • Emits outputs:
    • oflight_points.csv — a complete points table.
    • slotting.txt — a clean, human-readable list of primaries and alternates.

How the Points Work

The logic is implemented in oflight.py. Three factors determine each cadet’s score:

  • Flight points:
    • FLIGHT_FACTOR (default 5) points per remaining powered flight.
    • NO_FLIGHT_FACTOR (default 5) bonus if the cadet has no powered flights yet.
  • Date points:
    • DATE_FACTOR (default 1) point per month since the last powered flight (or since join date if none).

Defined at the top of oflight.py:

# oflight.py
NO_FLIGHT_FACTOR = 5      # Extra Points for cadets with no powered flights
FLIGHT_FACTOR = 5         # Points per powered flight
DATE_FACTOR = 1           # Points per month since last powered flight

The script:

  • Counts powered flights across powered_1 … powered_5.
  • Determines the last powered date (most recent non-empty powered flight).
  • Falls back to Joined if there are no powered flights.
  • Computes months since that date.
  • Adds up flight_points + date_points into total_points.
  • Sorts cadets by total_points descending.

Input Files

  • Cadet_Orientation_Report.csv
    Export this from CAP eServices (Orientation Flights Report). The script renames cryptic columns automatically (like Textbox135 → powered_1).

  • cadet_list.txt
    A simple text file with one CAP ID per line, for the cadets you’re scheduling:

    123456
234567
345678

Outputs

  • oflight_points.csv
    A full table with computed columns like powered_count, last_powered, flight_points, date_points, and total_points.

  • slotting.txt
    A readable list showing primaries (up to your slot count) and alternates. It includes:

    • CAP ID
    • Name
    • Total Points
    • Next O-Flight number (1–5)
    • Last Flight (e.g., Apr-23)

Example snippet:

Primary Slots:
======================================================================
  #  CAPID    Name                           Points  Next  Last Flight
  1  123456   John Smith                      43     2       Apr-23
  2  234567   Jane Doe                        40     2       Jul-23

Alternates:
======================================================================
  3  345678   James Johnson                   36     1

Installation

  • Requirements: Python 3.6+ and pandas.
pip install pandas

Usage

  • Complete workflow (calculate points + produce slotting list):
python oflight.py <number_of_slots>
# Example:
python oflight.py 10
  • Calculate points only:
python oflight.py points
  • Create slot assignments only (after points are calculated):
python oflight.py slots [number_of_slots]
# Example:
python oflight.py slots 10

How It Fits Together

flowchart TD
    A[Cadet_Orientation_Report.csv] --> B[Process Data]
    B --> C[Count powered flights]
    B --> D[Find last powered date or Joined]
    B --> E[Compute months since]
    C --> F[Flight points]
    E --> G[Date points]
    F --> H[Total points]
    G --> H
    H --> I[oflight_points.csv]

    J[cadet_list.txt] --> K[Filter by CAP IDs]
    I --> K
    K --> L[Sort by total points]
    L --> M[Primary slots + Alternates]
    M --> N[slotting.txt]

Customizing Fairness

Tweak weights at the top of oflight.py to match your unit’s policy:

  • NO_FLIGHT_FACTOR: bonus for cadets with no flights.
  • FLIGHT_FACTOR: incentive for those with more remaining powered flights.
  • DATE_FACTOR: gives weight to recency—increases the priority if it’s been a long time since the last flight.

Example, if you want to emphasize recency more:

DATE_FACTOR = 2

Edge Cases and Safeguards

  • Date parsing: Missing or odd date formats are handled gracefully—invalid dates won’t crash the run.
  • Unmatched IDs: CAP IDs in cadet_list.txt that don’t match the CSV show up in an “Unmatched CAP IDs” section of slotting.txt.
  • String matching: CAPID is coerced to string to avoid numeric matching issues.

Quick Start Checklist

  • Export Cadet_Orientation_Report.csv from eServices.
  • Create cadet_list.txt with the CAP IDs you’re scheduling.
  • Install pandas.
  • Run: 
    python oflight.py 10
  • Open slotting.txt and use it for your mission planning.

Roadmap Ideas

  • Parity: Optional weighting for glider vs powered flight parity.
  • Dates: Automatic parsing of alternate date formats in the CSV.
  • CLI: Flags for weights instead of editing the script.
  • Export: HTML or CSV export for the slotting list.
  • Validation: Hints if required columns are missing.

Source

If you want this tailored to your unit’s exact policy or exported into your preferred format (Google Sheets, Excel, or a web dashboard), I can extend the tool accordingly.