Metadata-Version: 2.4
Name: atspm
Version: 3.0.0
Summary: Aggregates hi-res data from ATC traffic signal controllers into 15-minute binned ATSPM/performance measures.
Author-email: Shawn Strasser <shawn.strasser@odot.oregon.gov>
License-Expression: MIT
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Operating System :: OS Independent
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: atspm-detector>=2.0.0
Requires-Dist: duckdb>=2.0.0
Requires-Dist: jinja2
Requires-Dist: pyarrow
Requires-Dist: pytz
Provides-Extra: dev
Requires-Dist: pytest>=9.2; extra == "dev"
Requires-Dist: pytest-xdist>=3.8; extra == "dev"
Dynamic: license-file

# ATSPM Aggregation

<!-- Package Info -->
[![PyPI](https://img.shields.io/pypi/v/atspm)](https://pypi.org/project/atspm/)
[![PyPI - Python Version](https://img.shields.io/pypi/pyversions/atspm)](https://pypi.org/project/atspm/)
[![PyPI - Downloads](https://img.shields.io/pypi/dm/atspm)](https://pypi.org/project/atspm/)

<!-- Repository Info -->
[![GitHub License](https://img.shields.io/github/license/ShawnStrasser/atspm)](https://github.com/ShawnStrasser/atspm/blob/main/LICENSE)
[![GitHub issues](https://img.shields.io/github/issues/ShawnStrasser/atspm)](https://github.com/ShawnStrasser/atspm/issues)
[![GitHub stars](https://img.shields.io/github/stars/ShawnStrasser/atspm)](https://github.com/ShawnStrasser/atspm/stargazers)

<!-- Status -->
[![Unit Tests](https://github.com/ShawnStrasser/atspm/actions/workflows/pr-tests.yml/badge.svg)](https://github.com/ShawnStrasser/atspm/actions/workflows/pr-tests.yml)
[![codecov](https://codecov.io/gh/ShawnStrasser/atspm/badge.svg)](https://codecov.io/gh/ShawnStrasser/atspm)

`atspm` is a cutting-edge, lightweight Python package that transforms raw traffic signal controller event logs into aggregate performance measures and troubleshooting data which help transportation agencies continuously monitor and optimize signal timing performance, detect issues, and take proactive actions - all in real-time. `atspm` may be used by itself, embedded inside an ATMS application, or installed on an edge device.

## What Makes ATSPM Different?
Traditional traffic signal optimization tools like Synchro rely on periodic manual data collection and simulation models. In contrast, `atspm` offers:

*   **Real-Time Data:** Uses data directly collected from signal controllers at intersections.
*   **Continuous Monitoring:** Allows agencies to generate performance data for any time range, diagnosing problems before they escalate.
*   **Proactive Management:** Enables agencies to solve issues before they lead to major traffic disruptions, rather than relying on infrequent manual studies or citizen complaints.
*   **Cost Efficiency:** With over 330,000 traffic signals in the US, continuous monitoring reduces the need for costly manual interventions (typically $4,500 per intersection every 3-5 years).

The Python `atspm` project is inspired by UDOT ATSPM, which is a full-stack application for intersection-level visualization. This package focuses on **aggregation and analytics**, enabling a system-wide monitoring approach. Both projects are complementary and can be deployed together.

This project focuses only on transforming event logs into performance measures and troubleshooting data; it does not include data visualization. Feel free to submit feature requests or bug reports or to reach out with questions or comments. Contributions are welcome! 

## Table of Contents

- [What Makes ATSPM Different?](#what-makes-atspm-different)
- [Features](#features)
- [Installation](#installation)
- [Quick Start](#quick-start)
- [Usage Example](#usage-example)
- [Performance Measures](#performance-measures)
- [Release Notes](docs/CHANGELOG.md)
- [Future Plans](#future-plans)
- [Contributing](#contributing)
- [License](#license)

## Features

- Transforms event logs into aggregate performance measures and troubleshooting metrics
- Supports incremental processing for real-time data (ie. every 15 minutes)
- Runs locally using the powerful [DuckDB](https://duckdb.org/) analytical SQL engine.
- Output to user-defined folder structure and file format (csv/parquet/json), or query DuckDB tables directly
- Deployed in production by Oregon DOT since July 2024

## Installation

```bash
pip install atspm
```
Or pinned to a specific version:
```bash
pip install atspm==3.x.x
```
`atspm` works on Python 3.11-3.14 with DuckDB 2 and is tested on Ubuntu, Windows, and MacOS. It installs `atspm-detector`, which the `detector_health` aggregation uses.

## Quick Start

The best place to start is with these self-contained example uses in Colab!<br>
[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/drive/14SPXPjpwbBEPpjKBN5s4LoqtHWSllvip?usp=sharing)

## Usage Example

The first step in running the tool is to define the parameters that will dictate how the data is processed. The parameters include global settings for input data, output formats, and options to select specific performance measures.

### Exhaustive Parameter List

```python
from atspm import SignalDataProcessor, sample_data

params = {
    # --- Global Settings ---
    'raw_data': sample_data.data,           # Path (CSV/Parquet/JSON) or Pandas DataFrame
    'detector_config': sample_data.config,  # Path (CSV/Parquet/JSON) or Pandas DataFrame. Columns: DeviceId, Phase, Parameter,
                                            # Function, and optionally DistanceFromStopBar, MPH and LatencyCorrection (see Arrival on Green)
    'bin_size': 15,                         # Aggregation interval in minutes
    'output_dir': 'test_folder',            # Directory to save results
    'output_format': 'csv',                 # 'csv', 'parquet', or 'json'
    'output_file_prefix': 'run1_',          # Optional prefix for output files
    'output_to_separate_folders': True,     # Save each measure in its own subfolder
    'remove_incomplete': True,              # Remove bins has_data scores under 0.5 or has no row for (requires 'has_data' agg)
    'drop_fall_back_hour': True,            # Drop events in the hour US clocks repeat each November (default shown, see The fall clock change)
    'verbose': 1,                           # 0: Errors only, 1: Performance, 2: Debug
    'to_sql': False,                        # If True, returns SQL strings instead of executing
    'controller_type': 'maxtime',           # Global: '' (default), 'maxtime' or 'siemens' (case-insensitive)
                                            # When 'maxtime': phase_wait uses ActualCycleLength
                                            # When 'siemens': timeline invalidates intervals across hourly log restarts

    # --- Incremental Processing Settings ---
    'unmatched_event_settings': {
        'df_or_path': 'unmatched.csv',           # Track unmatched timeline events
        'split_fail_df_or_path': 'sf_unmatched.csv', # Track unmatched split failures
        'max_days_old': 7,                       # Max age for tracking unmatched events
        'first_run': True                        # First run only: these files are not there yet. Without it a missing file raises
    },

    # --- Performance Measures (Aggregations) ---
    'aggregations': [
        {'name': 'has_data', 'params': {}},
        {
            'name': 'actuations', 
            'params': {
                'fill_in_missing': True,    # Zero-fill silent detectors in bins where the device has data
                'known_detectors_max_days_old': 2  # With a main unmatched file, how long a silent detector keeps its zero rows
            }
        },
        {
            'name': 'arrival_on_green', 
            'params': {
                'latency_offset_seconds': 0, # Taken off each arrival time for detectors with no LatencyCorrection
                'spillback_on_seconds': 6,   # A detector ON for more than this has a queue over it (default shown), None to turn this off
                'spillback_gap_seconds': 5   # and its actuations count as on red until it has been OFF for this long (default shown)
            }
        },
        {
            'name': 'approach_delay',
            'params': {
                'latency_offset_seconds': 0, # The same three settings as arrival_on_green. Give both the same values
                'spillback_on_seconds': 6,   # None gives UDOT's method alone, and Queued_Delay_Seconds reads 0
                'spillback_gap_seconds': 5
            }
        },
        {
            'name': 'left_turn_gaps',
            'params': {}                     # No settings. The dashboard picks the critical gap
        },
        {
            'name': 'split_failures', 
            'params': {
                'red_time': 5,              # Min red time to consider a split failure
                'red_occupancy_threshold': 0.80,
                'green_occupancy_threshold': 0.80,
                'by_approach': True         # Must be True: by_approach=False raises
            }
        },
        {
            'name': 'yellow_red', 
            'params': {
                'latency_offset_seconds': 0
            }
        },
        {
            'name': 'arrival_pattern',
            'params': {
                'offset_slice_seconds': 5   # Red_Offset is rounded to this many seconds (the default)
            }
        },
        {
            'name': 'timeline', 
            'params': {
                'maxtime': True,            # Include MAXTIME-specific events
                'min_duration': 1,          # Filter out events shorter than n seconds
                'cushion_time': 1,          # Padding for instant events (seconds)
                'max_event_gap_seconds': {'05:00': 300, '06:00': 120, '21:00': 300, '23:00': 900},  # Longest device silence by time of day (default shown); a number for all day, None to disable
                'live': False               # If True, keep incomplete events as IsValid=False with common EndTime. This is for troubleshooting.
            }
        },
        {
            'name': 'ped', 
            'params': {
                'seconds_between_actuations': 15, # Min time between unique peds (default shown)
                'return_volumes': True            # Estimate pedestrian volumes
            }
        },
        {
            'name': 'phase_wait',
            'params': {
                'preempt_recovery_seconds': 120, # Time after preempt ends to exclude
                'assumed_cycle_length': 140,     # Fallback cycle length (Free mode)
                'skip_multiplier': 1.5,          # Threshold for skipped phases
                'tsp_skip_multiplier': 2.0       # Same, for waits that saw a TSP adjustment
            }
        },
        {'name': 'ped_delay', 'params': {}},
        {'name': 'platoon_ratio', 'params': {}},  # Requires arrival_on_green
        {'name': 'terminations', 'params': {}},
        {'name': 'splits', 'params': {}},        # MAXTIME-specific events
        {'name': 'coordination', 'params': {}},  # MAXTIME-specific events
        {'name': 'coordination_agg', 'params': {}} # General coordination state (Pattern, Cycle, etc.)
    ]
}

# Running the Processor
# Using 'with' ensures the DuckDB connection is closed automatically
with SignalDataProcessor(**params) as processor:
    processor.load()      # Load raw data into DuckDB
    processor.aggregate() # Run performance measures
    processor.save()      # Save to output_dir

# Alternatively, use the .run() method to perform all steps at once
processor = SignalDataProcessor(**params)
processor.run()
```

### Retrieving Results as a DataFrame
You can query the internal DuckDB database directly. Note that the connection must be open to query data:
```python
processor = SignalDataProcessor(**params)
processor.load()
results = processor.conn.query("SELECT * FROM actuations ORDER BY TimeStamp").df()
print(results.head())
processor.conn.close() # Manually close if not using 'with'
```

### Visualization Options
The data produced by `atspm` can be visualized using Power BI, Plotly, or other platforms. For example, see the [Oregon DOT ATSPM Dashboard](https://app.powerbigov.us/view?r=eyJrIjoiNzhmNTUzNDItMzkzNi00YzZhLTkyYWQtYzM1OGExMDk3Zjk1IiwidCI6IjI4YjBkMDEzLTQ2YmMtNGE2NC04ZDg2LTFjOGEzMWNmNTkwZCJ9).

> **Note:** Use [Parquet](https://parquet.apache.org/) format in production for significantly better performance and smaller file sizes.

## Performance Measures

`atspm` writes the tables named in the `aggregations` list you give it, and no others: each table below is written when listed in `aggregations`. Everything a run does comes from that list and its params, so nothing is kept between runs but the carry files and health store you choose to pass. There are two types of output:

- Binned aggregate tables, where each row represents a `bin_size`-minute interval
- A non-binned `timeline` table with start/end times for key events

### Binned aggregate measures (per `bin_size` interval)

All of the following tables include a `TimeStamp` column aligned to the start of each aggregation bin (for example, 15 minutes):

- **Has Data** (`has_data`): Scores each bin by the share of its minutes not inside an unexpected gap with no events at all. Also used to filter incomplete periods for other measures.
- **Actuations** (`actuations`): Detector actuations per detector and interval (with optional zero-filling of missing intervals).
- **Arrival on Green** (`arrival_on_green`): Percentage of vehicles reaching the stop bar during green by phase, with each advance detector actuation projected to the stop bar. See [Arrival on Green](#arrival-on-green).
- **Platoon Ratio** (`platoon_ratio`): HCM platoon ratio by phase and interval, `Rp = Percent_AOG / (g/C)`, with the green ratio estimated from phase green time within each bin and the HCM arrival type (1–6). Unlike arrival on green, it is comparable across phases with different splits. Requires `arrival_on_green`.
- **Approach Delay** (`approach_delay`): Seconds vehicles wait by phase and interval, with `Delay_Seconds` measured by UDOT's method and `Queued_Delay_Seconds` estimated for queues over the advance detector. See [Approach Delay](#approach-delay).
- **Left-Turn Gaps** (`left_turn_gaps`): Gaps in the traffic on each through phase by length, so a dashboard can pick any critical gap for the left turn across it. See [Left-Turn Gaps](#left-turn-gaps).
- **Yellow and Red Actuations** (`yellow_red`): Distribution of detector actuations relative to the start of red, including red offset and signal state. A row belongs to the `bin_size` bin of its cycle's red begin. Incremental runs give the same rows as one big run: a bin is written once the last cycle with a red in it has finished, so with runs cut on the bin each one comes out a run later, and the events it waits for are kept in `unmatched_events` (synthetic `EventId` 951 to 954).
- **Arrival Pattern** (`arrival_pattern`): `yellow_red` for `Advance` detectors: each vehicle is moved forward to the stop bar the way `arrival_on_green` does it (`DistanceFromStopBar`, `MPH`, `LatencyCorrection` and their defaults), and counted by the seconds from the red begin of the cycle it arrives in. `Red_Offset` is rounded to the nearest `offset_slice_seconds` (5 unless given). On 150 ODOT signals over one day that is about 5,400 rows per signal, or 1,445 per phase. There is no spillback handling. Incremental runs give the same rows as one big run, by the same rule as `yellow_red` (synthetic `EventId` 960 to 963).
- **Split Failures** (`split_failures`): Green and red occupancies by phase (and optionally detector/approach) and a count of cycles that meet split-failure thresholds; can be returned either per cycle or aggregated into time bins. Incremental runs give the same rows as one big run: a bin is written once, whole, by the first run that reaches the end of it, by the same rule as [Arrival on Green](#arrival-on-green), and `sf_unmatched` holds the events a later run needs. A missing detector off is filled in half way to the next on, but no earlier than the start of that on's bin, and a cycle is done at its first red barrier, so a run never needs an event it has not seen.
- **Terminations** (`terminations`): Counts of GapOut, MaxOut, and ForceOff terminations by phase.
- **Splits** (`splits`): MAXTIME-specific split events (cycle length/split services) aggregated by interval.
- **Coordination** (`coordination`): Raw events for Pattern, Cycle Length, Actual Cycle Length (MAXTIME), and Actual Offset (MAXTIME). Includes both `Raw_TimeStamp` and binned `TimeStamp`.
- **Coordination Aggregate** (`coordination_agg`): Binned coordination state per interval. Provides the active Pattern, Cycle Length, Actual Cycle Length (MAXTIME), and Actual Offset (MAXTIME) for every bin using fill-forward logic.
- **Pedestrian** (`ped`): Pedestrian services, push-button presses, unique presses and estimated pedestrian volumes, all in one row per phase and interval. `return_volumes=False` (default True) leaves out `Estimated_Volumes` and the rows of quiet bins. A press is a unique actuation if it comes more than `seconds_between_actuations` (default 15) after the previous press on that input, or if no earlier press is known. See [Pedestrian counts and volumes](#pedestrian-counts-and-volumes).
- **Ped Delay** (`ped_delay`): Average pedestrian delay and sample counts per phase and interval, derived from `timeline`.
- **Phase Wait** (`phase_wait`): Average wait time for a phase to turn green after a call, with filtering for preempts and skipped phases (wait > 1.5x cycle length). A wait is left out when the device logged no call on any phase during it or in the 15 minutes before it ended, since the call state is then unknown. For MAXTIME controllers, set `controller_type='maxtime'` globally to use the more accurate ActualCycleLength.
- **Detector Health** (`detector_health`): How much of each `bin_size` interval each vehicle detector and ped button was failing, and with what problem, from `atspm-detector`. See [Detector health](#detector-health).

### Arrival on Green

`arrival_on_green` and `platoon_ratio` follow the UDOT method (ATSPM Methods and Assumptions Manual 4.3, Purdue Coordination Diagram, Step 1). A detector sits upstream of the stop bar, so each detector on event (`EventId` 82) on an advance detector is moved forward to when the vehicle reaches the stop bar:

```
arrival = detector on + distance / (speed * 1.467) - latency
```

The arrival time is used for everything after that. An arrival is on green if the phase is green at that moment, and it counts in the bin it lands in, so `Total_Actuations` for a bin differs slightly from the `actuations` table, which keeps the detector's own times.

A bin with vehicles and none on green reads 0, including a bin where the phase never turned green. A bin with no vehicles has no row. `platoon_ratio` reads 0 in a bin with green and none of its vehicles on green, and has no row for a bin with no green, where the ratio has no meaning.

Distance, speed and latency come from three optional `detector_config` columns, in UDOT's names:

| Column | Meaning | If missing |
| --- | --- | --- |
| `DistanceFromStopBar` | Feet from the leading edge of the detector to the stop bar | 220 for phases 2 and 6, 120 for all others |
| `MPH` | Speed in miles per hour. UDOT uses the speed limit | 35 |
| `LatencyCorrection` | Seconds from the vehicle crossing the detector to the controller logging it | `latency_offset_seconds`, or 0 without it |

Each value is judged row by row: a row may have one and not the others, and whatever is blank, not a number, a negative distance or a speed of zero takes its default. A latency of 0 in the column is a real 0, and beats `latency_offset_seconds`. A config with only `DeviceId`, `Phase`, `Parameter` and `Function` keeps working and takes the defaults for every detector. Column names ignore case, spaces and underscores. With `to_sql=True` your own `detector_config` table needs all three columns, which may be NULL.

**Queues over the detector.** UDOT's method has no step for a queue that reaches the advance detector. Vehicles stop on it while the phase is red. When the phase turns green the queue moves, and its vehicles begin actuating the detector on green, so they read as arriving on green although they all arrived on red. So an advance detector that stays ON for more than `spillback_on_seconds` (default 6) gets a flag, and every actuation that begins while it holds the flag counts as an arrival on red, whatever the phase shows. `Total_Actuations` does not change, and `platoon_ratio` follows through `Percent_AOG`.

The flag goes only when the detector has been OFF for `spillback_gap_seconds` (default 5), measured from off to the next on, not when traffic starts moving, because the queue clears at about one vehicle every 2 seconds. Each detector has its own flag. The flag begins when the detector has been ON long enough, so the actuation that trips it keeps its own reading. A detector with no off events never gets the flag. `spillback_on_seconds=None` turns all of this off and gives UDOT's method alone. The two defaults come from three days of real events on 846 signals, with no ground truth to tune against: more than 6 seconds on is a car at walking pace or stopped, and 5 seconds of empty lane is well past the gap between vehicles in a queue that is clearing.

**Incremental runs** give the same rows as one big run wherever the runs are cut, for both measures. A bin is written once, whole, by the first run whose data reaches the end of it, and that is a run whose last event, across all signals, is within a second of the end of the bin. A run that stops short of that leaves the bin it stopped in for the next run. Vehicles that arrive after results stop, the phase events from there on, the phases still green, the detectors holding the flag and the detectors still on are kept in `unmatched_events` (synthetic `EventId` 938 to 941 and 948 to 950, and 942 with `remove_incomplete`), so `unmatched_event_settings` with `df_or_path` is all that is needed, with or without `timeline`. With runs cut on the bin and enough signals to fill the last second, every bin is written by the run that reaches it. With a few quiet signals the last event can be short of the end of the bin, and that bin is written one run later, with the same values. A run with no unmatched settings has nothing after it, so it writes all it has, up to its last event. Arrivals past that are not counted.

Two limits: a run that really stops inside the last second of a bin is taken as having reached the end of it, and a latency larger than the travel time can move an arrival back into a bin already written.

### Approach Delay

`approach_delay` follows UDOT's Approach Delay method (ATSPM Methods and Assumptions Manual 4.3), on the arrivals of [Arrival on Green](#arrival-on-green): only `Advance` detectors, the same arrival time from the same `detector_config` columns and defaults, and the same spillback flag. A vehicle counts in the bin it arrives in, whenever its wait ends, so it is in the same bin as `arrival_on_green`'s `Total_Actuations`. A bin's start time names its period: 3:15 covers 3:15 to 3:30. A bin with vehicles and no delay reads 0, and a bin with no vehicles has no row.

| Column | Which vehicles | Seconds |
| --- | --- | --- |
| `Delay_Seconds` | Arrived on red, under the flag or not | Measured: from arrival to the next green, added up |
| `Queued_Delay_Seconds` | Crossed on green or yellow under the flag | Estimated: half the time from the later of red start and flag start to their green, added up. 0 if the flag began after the green did |

Any other vehicle on green or yellow adds nothing, as in UDOT's method. A vehicle with no green within 360 seconds, or with an estimate over 360 seconds, is in neither column. UDOT states no such limit. Red starts at red clearance begin (10), or at end of yellow (9), which is UDOT's own event and is all that a signal logging no 10 has.

**The estimate has no published method.** UDOT's method gives a vehicle on green no delay and says it does not account for queues that exceed the detection zone. `spillback_on_seconds=None` leaves it out, and `Queued_Delay_Seconds` reads 0.

Seconds per vehicle, with both tables listed with the same `bin_size` and settings:

```sql
(SUM(Delay_Seconds) + SUM(Queued_Delay_Seconds)) / SUM(Total_Actuations)
```

It holds at any roll-up, since the vehicles are in the same bins. `SUM(Delay_Seconds) / SUM(Total_Actuations)` is the measured part alone, and the top number over 3,600 is vehicle-hours. UDOT's average is written two ways, over arrivals on red and over all actuations, and this table has no count of arrivals on red. `approach_delay` runs without `arrival_on_green`.

**Incremental runs** give the same rows as one big run wherever the runs are cut. A bin is written once and never changes, by the first run whose data reach 360 seconds past its end, across all signals, when none of its vehicles can still be waiting. With runs cut on a 15-minute bin that is one run after `arrival_on_green`, so the newest bin of `arrival_on_green` has no row here yet, and a chart should stop at the newest bin here. A run with no unmatched settings writes all it has. Sums of the bins not written yet, waiting vehicles, the signal events they need and the detectors holding the flag are kept in `unmatched_events` (synthetic `EventId` 957 to 959, and 942 with `remove_incomplete`).

### Left-Turn Gaps

`left_turn_gaps` gives the gaps in the traffic on each through phase, the ones a left turn across it can use, by length. A gap starts when the last occupied lane goes off (81) and ends when any lane comes on (82). It is cut to the phase's green and yellow, from green begin (1) to the next red clearance begin (10), or end of yellow (9) on a signal logging no 10, so a gap across two greens counts once in each. A phase uses its `Count` zones, or its `Presence` zones if it has none. Other zones and phases other than 2, 4, 6 and 8 have no rows. UDOT's Left Turn Gap Analysis measures off to off instead (UT-21.12, p.33), which counts time a car spends on the zone as gap. A bin's start time names its period, and a bin with no green or yellow has no row.

The table is long, like `yellow_red`, so a dashboard picks the critical gap afterwards, with no rerun, and it can pick one for each approach. Use 5.3 seconds unless you have a reason not to. UDOT's capacity code uses 4.1 seconds up to 2 opposing lanes and 5.3 above, and UT-21.12 says to calculate it for each approach (p.73). Every second of green and yellow is in one row of its bin:

| `Gap_Length` | `Seconds` hold |
| --- | --- |
| 0 | The road occupied, and gaps under 3 seconds |
| 3.0 to 9.9 | Gaps of that length, up to the next 0.1 |
| 10 | Gaps of 10 seconds or more |

A gap's seconds split at bin edges and stay in the slice of its whole length. It counts in `Gaps` once, in the bin it ends in, so a row can have `Seconds` with `Gaps` of 0. The share of green and yellow in gaps of 5.3 seconds or more, at any roll-up:

```sql
SUM(Seconds) FILTER (WHERE Gap_Length >= 5.3) / SUM(Seconds)
```

In Power BI, with `Critical Gap` a table of its own for a slicer, with no relationship:

```dax
Gap Share =
VAR Critical = SELECTEDVALUE ( 'Critical Gap'[Seconds], 5.3 )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( left_turn_gaps[Seconds] ), left_turn_gaps[Gap_Length] >= Critical ),
        SUM ( left_turn_gaps[Seconds] )
    )
```

Any critical gap from 3 to 10 seconds in steps of 0.1 reads exactly. With the same filter, `SUM(Gaps)` is the number of large gaps, the same `SUM(Seconds)` is what UT-21.12's capacity check calls the total duration of the acceptable gaps, and `SUM(Seconds) / SUM(Gaps)` is their average length over a whole period. Do not let a slicer filter `Gap_Length` directly, or the bottom of the share goes with it.

What it can't tell you:

- **All opposing green counts**, not only the time the left turn is permissive.
- **Compare a phase with itself over time**, not across zones. Count and presence zones differ: on 112 phases with both, presence stays on 1.0 second longer per on-period, and on 142 phases its share at 7.4 seconds runs a median 6.5 points lower, from 31.0 lower to 19.8 higher (5th to 95th percentile, Oct 27 2026). Most count zones are fixed pulses, so a car sitting on one is not seen.
- **Phases 2, 4, 6 and 8 are assumed to be through phases**, and `detector_config` has no lane type, so a zone in a left lane assigned to phase 2 counts.
- **A lane is empty until its first on**, so a zone that has never logged reads as an empty road and a share of 100%.

**Incremental runs** give the same rows as one big run wherever the runs are cut. A bin is written once and never changes, by the first run whose data for that signal reach 10 seconds past the end of the bin, when any gap across its end has ended or is in the top slice. With runs cut on a 15-minute bin that is one run after `arrival_on_green`, so a chart should stop at the newest bin here. A signal silent for a run writes nothing until it logs again. A run with no unmatched settings writes all it has, up to each signal's last event, and a gap still open there adds its seconds and no count. Sums of the bins not written yet, the lanes on and where the last stretch of each green began are kept in `unmatched_events` (synthetic `EventId` 927 to 930, and 942 with `remove_incomplete`).

### Pedestrian counts and volumes

`Estimated_Volumes` in `ped` is the published estimate, with its rolling hour taken over a bin and the 3 bins before it:

```
hour = unique actuations in this bin and the 3 before it
Estimated_Volumes = (0.7167 * hour + 0.0599 * hour^2) * (unique actuations in this bin / hour) + 1.1063 / 4
```

Looking back is what lets a bin be final the moment it is written. It used to look at the next 3 bins, which the end of a run does not have, so the last 3 bins of every run read low and could only be fixed by changing a row already written. On 758 ODOT signals over 2 days (Oct 5 and 6 2026), against the forward-looking form on the same counts:

| | Measured |
| --- | --- |
| Peds in a day, network | Up 0.00% and 0.02%. Every pair of presses within the hour counts once either way, so only the ends of the data differ |
| Peds in a day, one input with 10 or more | 90% unchanged, 99% within 0.3%, largest 4.2% |
| Peak hour (4 bins in a row) | Same size on average, 80% within about 5%. It starts in the same bin 59% of the time and within 15 minutes 77% of the time |
| Shape of the day | The same volumes sit a few minutes later: the middle of an input's day moves by a median of 3.7 minutes, and network hours read up to 8.5% lower on the morning rise and up to 7.7% higher on the evening fall |
| Each bin | 95% of bins with peds move by 1 ped or less. The largest moves, up to 40, are on the busiest bins, where a burst reads higher at its end and lower at its start |

**Incremental runs** give the same rows as one big run wherever the runs are cut, for `ped`, with or without `timeline`, when `unmatched_event_settings` has `df_or_path`. A bin is written once, whole, by the first run that reaches the end of it, the same rule as [Arrival on Green](#arrival-on-green), so nothing is held back from a run that reaches the end of its last bin and no past row ever changes. Each input's last press (synthetic `EventId` 937), the unique actuations of the last 3 bins written and of any bin not written yet (943), each input's next bin to write (944) and last activity (945), and the presses and ped services of any bin not written yet (946, 947) are kept in `unmatched_events`. A run with no unmatched settings has nothing after it, so it writes all it has.

With volumes on, an input has a row in every bin from its first activity (a press or a ped service) on, quiet bins included, with an estimate of just the intercept. That goes on through runs where it does nothing, until `max_days_old` passes with no activity, and it starts again at its next activity. One big run has no such limit, so it also has the intercept rows of an input quiet for longer than `max_days_old`, and that is the one difference between it and incremental runs. A device that stops logging keeps its intercept rows the same way. Join `has_data` or set `remove_incomplete` to leave them out. `return_volumes=False` leaves out all rows that count nothing.

Two limits: a run that really stops inside the last second of a bin is taken as having reached the end of it, and a press in a bin already written is too late to count. Volumes assume a `bin_size` of 15. The first run from an older unmatched file has no earlier bins to read, so its first 3 bins read a little low, once.

### The fall clock change

When clocks fall back, local time runs through 1:00 to 2:00 am twice on the first Sunday of November. Events from the two passes share one set of timestamps and cannot be separated, so durations and phase sequences built on them are wrong. ODOT's staging data of Nov 1 2026 had about 100 copies of each event in that hour, and false phase conflicts followed. So both passes are thrown out.

`drop_fall_back_hour`, on by default, drops every event from 1:00:00 up to but not including 2:00:00 on that day when `raw_data` is loaded, before anything is computed. Every aggregation reads `raw_data`, so to all of them the hour has no data, the same as an outage: `has_data` has no row for it, `timeline` marks an interval across it invalid, and with `remove_incomplete` no measure has a row in it. Without `remove_incomplete` a few rows can touch the hour, as with any outage: a vehicle that crossed a detector just before 1:00 arrives just after it, and a known ped input keeps its zero rows.

It assumes US rules (since 2007, the change is at 2:00 am on the first Sunday of November) and that `TimeStamp` is local time. If your data is in UTC or another fixed offset, is from somewhere with other rules, or has already been cleaned, turn it off:

```python
SignalDataProcessor(raw_data=events, drop_fall_back_hour=False, ...)
```

The spring change skips an hour and repeats none, so nothing is needed then. `to_sql=True` loads nothing, so the setting does not apply there, and your own `raw_data` table is read as it is.

### Detector health

`detector_health` runs `atspm-detector`'s checks for vehicle detectors (stuck on, silent, counts falling, too-fast actuations and so on) and ped buttons (stuck on, machine-regular presses, a sudden stop and so on) in one pass over `raw_data`. It needs no other aggregation.

```python
{'name': 'detector_health', 'params': {
    'detectors': 'detectors.parquet',    # DataFrame or path; None: detector_config
    'state': health_state,               # the last run's health_state, as a table or a parquet or CSV path; '': first run; None: one run over this raw_data
    'history': 'C:/atspm/health_rows',   # folder or glob of saved health_rows, or a function giving one; read only by a whole-day check
    'horizon_days': 3,                   # how far back a whole-day check looks
    'findings': False,                   # also write detector_health_findings, at runs with a whole-day check
    'ped': True,
    'ped_assign': False,                 # ped movement model (onnxruntime)
    'options': None, 'ped_options': None,  # atspm-detector vehicle and ped overrides
    'workers': 4,                        # processes for the whole-day check
    'clock_ahead_minutes': 60,           # see below
}}
```

`judge`, `store` and `restart_old_store` are gone and raise an error naming the replacement.

- **Every run**: each 15-minute bin the run reaches gets its rows from the checks that can answer at once: vehicle stuck on, too-fast actuations, too many for the traffic, ped stuck on and ped long hold. A bin reached only by fewer than 1 in 10 of the run's signals waits, and the next run writes it too.
- **First run past midnight**: it also runs the whole-day check over the last `horizon_days` and writes what that check finds in the previous day, and only there, as new rows with past `TimeStamp`s. Read the table by `TimeStamp`, not by file. No bin, detector and label is written twice. A fault that began earlier shows from the start of the previous day.
- **Midnight comes from the event stamps**, never a machine clock: it is past when 9 in 10 of the signals heard from in the day before it are past it, using this run's and the state's last events, or once the run is `clock_ahead_minutes` past it. Until then the run stops at midnight: its later events stay in the state and its later bins wait, for up to `clock_ahead_minutes`. A signal later than that can lose that day's whole-day rows.
- **First runs**: pass `state: ''`. Nothing is written until the first whole-day check, at the first midnight after a complete day, as the limits come from that check.
- **Carrying the tables**: each run makes `health_state` and `health_rows` on `processor.conn`, as with `unmatched_events`. Load `health_state` back every run. Only append `health_rows`, one file per run, and pass their folder as `history`. With paths in `state` and `history`, `save()` writes them there, in this order: `detector_health`, `health_rows`, `health_state` last, replacing it in one step. On a first run with `state: ''` the state goes to `output_dir` like any table. Only rows of runs the state lists count, so retry a failed run from the last saved state, and de-duplicate output on `TimeStamp`, `DeviceId`, `DetectorType`, `Detector` and `Label`. A run the state lists whose rows are missing from `history` raises before the check, as the check would write rows that can never be fixed. Delete rows older than `horizon_days + 1` days.
- **Types and sizes**: both tables go through parquet or CSV, read back in their listed types. Cut events on the quarter hour by stamp: statewide (1,134 signals) the state is then 0.6 MB of parquet or 16 MB of CSV, where a cut 5 s before the quarter hour gives 15 MB or 299 MB. Keep `health_rows` in parquet: 3 days statewide are 428 MB there, and would not fit in memory as a DataFrame. With plain `pd.read_csv`, pass `dtype={'DeviceId': str}` for ids of digits. An empty run writes no rows, in all columns, and leaves the state as it is. A state from an `atspm-detector` that changed what the rows mean raises; pass `''` to start again and delete the old rows.
- **Cost, statewide, measured**: an ordinary run took about 5 s and 1.5 GB at 3.63 M events. The midnight run took 530 to 670 s and 2.9 GB with 1 worker, and about 280 s and 6.5 GB in total with 4 workers, past a 4 GB limit on the main process.
- **Equal to one big run**: the same events cut into any runs, each in its own process carrying only these tables, give the same rows. With `state: None` one run over this `raw_data` is judged at each midnight and at its end, and writes no `health_state` or `health_rows`. A state needs `bin_size` 15.
- **Stuck past the window**: a button or detector a check rated stuck ON keeps its bins for as long as it stays ON, however long, as the open ON stays in the state. While its signal logs nothing (a button: while its phase walks nothing) it has no rows, as nothing is known of it then, and they start again if it is still ON. A run with no state knows nothing before its `raw_data`.
- **Reading the bins**: sum `Score` per detector over the period and divide by 4 for 15-minute bins: that is the hours of complete failure in the period, whatever its length. Do not average: 3 hours of total failure averages 0.125 over a day, 0.042 over 3 days and 0.018 over 7. Good is no hours, Suspect under 1 hour, Bad 1 hour or more, the same for any period. Borderline findings have no rows, so they read Good. A `Score` left empty means not enough data, not a problem.
- **Detectors**: pass `predict()`'s answers from `atspm-detector` where you can. Without them `detector_config` is used, which holds the agency's settings rather than what the detectors are found to do: `Function` is taken as named, with `Stopbar Count` read as `Count`, every detector counts as one lane, and a detector with several rows takes its lowest phase.
- **Clocks**: with a state, a run reaches where 1 in 10 of its signals have an event later, so a few clocks running ahead move nothing, and events more than `clock_ahead_minutes` past that are left out. With `state: None`, only rows stamped before the end of the run's last 15-minute bin are processed, a signal more than `clock_ahead_minutes` past the typical signal's last event does not move that time, and rows left out this way are printed per signal at `verbose=1`. That log is the only sign of them: such a signal otherwise just looks quiet in the results.

`to_sql=True` is not supported, and `remove_incomplete` does not apply. The `traffic-anomaly` parameters (`data`, `device_groups`, `decompose_params`, `anomaly_params`, `return_last_n_days`) were removed in 3.0.0 and raise an error.

### Timeline events (non-binned)

The **`timeline`** table is an event-level dimension for troubleshooting and visualization and is *not* binned into `bin_size` intervals. Each row includes:

- `DeviceId`
- `StartTime` / `EndTime`
- `Duration` (seconds between `StartTime` and `EndTime`)
- `EventClass` (for example, Green, Yellow, Ped Service, Split, Preempt)
- `EventValue` (phase/overlap or a coded value, depending on `EventClass`)
- `IsValid` (whether the start/end pair is complete; intervals that overlap a controller clock update, a Siemens hourly log restart, or a device silence are also marked invalid)

When `has_data` is available, an interval is also marked invalid if any `bin_size` bin it spans has no `has_data` row for that device, since events may have been missed while the device was not reporting. This works the same for incremental runs: each run saves a marker per device in the unmatched events (synthetic `EventId` 935) holding the last bin that had data, so a gap between or across runs is still detected when the interval finally ends.

`has_data` can only see an outage that empties a whole sub-bin, so the timeline also looks for device silences: two consecutive events from a device, of any `EventId`, further apart than `max_event_gap_seconds`. An interval paired across a silence (a green, phase call or phase wait bridging an 11-minute outage, say) is marked invalid. A controller resting in green overnight can log nothing for many minutes, so the threshold depends on the time of day the silence starts. The default is 120 seconds from 06:00, 300 from 21:00, 900 from 23:00 and 300 from 05:00. These were set from a full day of high-resolution data for 420 signals, where silences over 120 seconds outside outages were almost all overnight. Pass a dict of `'HH:MM'` start times to seconds for a different schedule, a single number for all day, or `None` to turn the check off. Incremental runs save each device's last event time in the unmatched events (synthetic `EventId` 936), so a silence across runs is found and the result matches a single pass over the same data.

Clock Update events have no reliable size, since the controller's time correction (`EventValue`) is optional and often 0. Any interval overlapping a 10-second window around each clock update, from 5 seconds before to 5 seconds after, is therefore marked invalid. The Clock Update row itself is shown like other point-in-time events, starting at the update and lasting `cushion_time` seconds.

With `controller_type='siemens'`, the timeline also handles the hourly log restart. Siemens controllers stop logging at the top of each hour and resume a few seconds later with `EventId` 1000 and a snapshot of the current state stamped with the restart time, so an interval running across the hour has its true start or end lost. Any interval overlapping the window from the device's last event before the restart (no earlier than the top of the hour) through the restart itself is marked invalid. Event 1000 is vendor-specific, so this only applies when the controller type is set.

**Alarms and flash across runs.** With a main unmatched file, an alarm or flash (173, 174, not `Flash - Not Flash`) still open at the end of a run is written at once with a 60-second `Duration` and `EventValue` 0. When the next alarm event arrives the final row is written with the true length and `EventValue` NULL. Both have the same `DeviceId`, `EventClass` and `StartTime`, and one that ends in the run it began has only the final row. So count alarms as distinct `DeviceId`, `EventClass`, `StartTime`, and for durations use `EventValue` NULL, or the longest row of each.

Passing `maxtime=True` to the `timeline` aggregation adds MAXTIME-only events such as splits and alarm group events (Event 175).
Passing `live=True` keeps incomplete timeline events (normally dropped), marks them `IsValid=False`, and assigns a common `EndTime` at the current bin boundary. This is useful for near-real-time dashboards.

### Aggregation Dependencies

Some aggregations require other aggregations to be included in your processing run. The processor will automatically validate these dependencies and raise an error if a required dependency is missing:

| Aggregation | Required Dependencies |
| --- | --- |
| `timeline` | `has_data` |
| `coordination_agg` | `timeline`, `has_data` |
| `phase_wait` | `timeline` |
| `ped_delay` | `timeline` |
| `platoon_ratio` | `arrival_on_green` |

The processor automatically sorts aggregations to ensure dependencies run first, so you don't need to worry about the order in your aggregations list.

The table below lists all `EventClass` values and their associated `EventValue` ranges produced by the `timeline` aggregation.

<details>
<summary>Timeline EventClass and EventValue reference</summary>

| EventClass | EventValue |
| --- | --- |
| Green | 1-16 |
| Yellow | 1-16 |
| Red | 1-16 |
| Ped Service | 1-16 |
| Ped Delay | 1-16 |
| Ped Omit | 1-16 |
| Phase Call | 1-16 |
| Phase Hold | 1-16 |
| Phase Omit | 1-16 |
| FYA | 1-16 |
| Advance Warning Phase | 1-16 |
| Overlap Green | 1-16 |
| Overlap Trail Green | 1-16 |
| Overlap Yellow | 1-16 |
| Overlap Red | 1-16 |
| Overlap Ped | 1-16 |
| Advance Warning Overlap | 1-16 |
| Split | 1-16 |
| Pattern Change | 0-255 |
| Cycle Length Change | 0-255 |
| Offset Change | 0-255 |
| Coord | 0-255 |
| Preempt | 1-16 |
| TSP Call | 1-16 |
| TSP Adjustment | 1-16 |
| TSP Checkin | 1-16 |
| TSP Service | 1-16 |
| TSP Detector | 1-16 |
| Watchdog | NULL |
| Stuck Off | 1-128 |
| Stuck On | 1-128 |
| Erratic | 1-128 |
| Ped Detector Failed | 1-255 |
| Transition | NULL |
| Transition Shortway | NULL |
| Transition Longway | NULL |
| Transition Dwell | NULL |
| Cycle Fault | NULL |
| Coord Fault | NULL |
| Coord Fail | NULL |
| Cycle Fail | NULL |
| MMU Flash | NULL |
| Local Flash | NULL |
| Flash - Other | NULL |
| Flash - Not Flash | NULL |
| Flash - Automatic | NULL |
| Flash - Local Manual | NULL |
| Flash - Fault Monitor | NULL |
| Flash - MMU | NULL |
| Flash - Startup | NULL |
| Flash - Preempt | NULL |
| Alarm Group State | NULL |
| Power Failure | NULL |
| Power Restored | NULL |
| Clock Update | 0-255 |
| Stop Time Input | NULL |
| Manual Control | NULL |
| Aux Switch | 1-64 |
| Interval Advance | NULL |
| Special Function | 1-64 |

</details>

Detailed documentation for each measure is coming soon.

## Output Schemas

The following tables are produced by the aggregation process, depending on the configured list of aggregations.

### Primary Performance Measures

#### actuations
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Detector` (INT16): Detector number.
*   `Total` (INT16): Total count of actuations.

#### arrival_on_green
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Total_Actuations` (INT16): Vehicles from the advance detectors arriving at the stop bar in this bin.
*   `Percent_AOG` (FLOAT): Fraction of those arriving on green (0.0 - 1.0). A bin with vehicles and none on green reads 0, and a bin with no vehicles has no row.

#### approach_delay
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Delay_Seconds` (FLOAT): Measured. Seconds from arrival to the next green, added up over the vehicles arriving on red in this bin, including those under the spillback flag.
*   `Queued_Delay_Seconds` (FLOAT): Estimated, with no published method. Half the time from the later of red start and flag start to their green, added up over the vehicles of this bin crossing on green or yellow under the flag.

A vehicle with no green within 360 seconds, or an estimate over 360 seconds, is in neither. See [Approach Delay](#approach-delay).

#### left_turn_gaps
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Through phase whose traffic makes the gaps.
*   `Gap_Length` (DOUBLE): Lower edge of a 0.1 second slice of gap length, from 3.0 to 9.9. 10 is 10 seconds or more. 0 is the road occupied and gaps under 3 seconds.
*   `Gaps` (INT32): Gaps of this length that ended in this bin.
*   `Seconds` (FLOAT): Seconds of this bin in this slice. A phase's rows in a bin add up to its green and yellow in the bin.

One row per bin, phase and slice with any time in it. See [Left-Turn Gaps](#left-turn-gaps).

#### platoon_ratio
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Total_Actuations` (INT16): Vehicles from the advance detectors arriving at the stop bar in this bin (from `arrival_on_green`).
*   `Green_Actuations` (INT16): Of those, the ones that arrived during green.
*   `Percent_AOG` (FLOAT): Fraction of actuations arriving on green (from `arrival_on_green`), equal to `Green_Actuations / Total_Actuations`.
*   `Green_Seconds` (FLOAT): Seconds the phase displayed green within the bin. Green intervals that straddle a bin boundary are split between bins, so this column sums exactly across consecutive bins.
*   `Green_Ratio` (FLOAT): `Green_Seconds` divided by bin length (g/C when the phase cycles continuously).
*   `Platoon_Ratio` (FLOAT): `Percent_AOG / Green_Ratio`. 1.0 = random arrivals; > 1 = platoons arriving on green; < 1 = arriving on red. Bins with no green for the phase are excluded.
*   `Arrival_Type` (UTINYINT): HCM arrival type from `Platoon_Ratio`: 1 (≤ 0.50), 2 (≤ 0.85), 3 (≤ 1.15), 4 (≤ 1.50), 5 (≤ 2.00), 6 (> 2.00).

`Platoon_Ratio` is a ratio of two ratios, so it cannot be averaged across bins: the mean of the per-bin values is not the value for the period, and it comes out biased high because bins with a small `Green_Ratio` inflate the quotient. To aggregate over any longer period (an hour, a day, a time of day), sum the four raw columns and divide once:

```sql
SELECT SUM(Green_Actuations) / SUM(Total_Actuations)
       / (SUM(Green_Seconds) / (COUNT(*) * <bin_size> * 60.0)) AS Platoon_Ratio
FROM platoon_ratio
GROUP BY DeviceId, Phase
```

`Green_Actuations` and `Green_Seconds` are output for exactly this purpose.

#### coordination
*   `TimeStamp` (DATETIME): Bin start time.
*   `Raw_TimeStamp` (DATETIME): Exact timestamp of the event.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `EventId` (INT16): Event ID (131, 132, 316, 318).
*   `Parameter` (INT16): Value associated with the event (Pattern, Cycle Length, etc.).

#### coordination_agg
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Pattern` (INT16): Pattern number in effect.
*   `CycleLength` (INT16): Cycle length in effect.
*   `ActualCycleLength` (INT16): Measured cycle length.
*   `ActualOffset` (INT16): Measured offset.

#### detector_health
One row per bin per problem; no row means no problem found. A row is written once, by the first run or whole-day check to find it, and never changes, so a new row can carry a past `TimeStamp`; the latest bin is the live status.
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `DetectorType` (TEXT): `Vehicle` or `Ped`. The two kinds of number can overlap.
*   `Detector` (BIGINT): Detector, or ped input for a button.
*   `Label` (TEXT): The problem, or `not_enough_data` (one row a day).
*   `Score` (DOUBLE): How much of the bin the detector was failing: about 1 for a complete failure over the whole bin, 0.5 for half a bin of it. Problems at the same moment share that moment's worst value, so the rows of a bin add up to at most 1. Empty on `not_enough_data`.

#### detector_health_findings
Written by `detector_health` when its `findings` param is True (default False), at runs with a whole-day check, for looking into a detector. The findings of the run's last check: one row per finding, with notes and borderline findings too.
*   `TimeStamp` (DATETIME): Time of the check.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `DetectorType` (TEXT): `Vehicle` or `Ped`.
*   `Detector` (INTEGER): Detector, or ped input for a button. Empty on a note about the whole signal.
*   `Start` (DATETIME): Start of the finding.
*   `End` (DATETIME): End of the finding.
*   `Label` (TEXT): What was found.
*   `Confidence` (DOUBLE): 0 to 1, the check's own severity: 0.5 at its suspect limit, 0.8 at its bad limit.
*   `Detail` (TEXT): The evidence.

#### has_data
At most one row per signal and bin. No row reads as Score 0, so a lost bin has none.
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Score` (FLOAT): Above 0 and up to 1. 1 - minutes of the bin in an unexpected gap / minutes of the bin. Half a bin in such a gap reads 0.5. It measures time in gaps, not counts.

It reads all events together, `EventId` 218 and below: a quiet detector or a lack of calls is not data loss while anything else is logged. A gap is a run of clock minutes with no event at all. It is unexpected when it runs over 4 times the signal's own recent longest gap, the longest in its last 4 bins with events, and over 2 minutes, so a quiet night reads 1. Nothing is judged until 4 bins with events are remembered. A flash status (173 with `Parameter` other than 2) clears that memory, as a signal in flash can be silent. `remove_incomplete`, `timeline`'s `IsValid` and `coordination_agg` read a bin as having data at Score 0.5 or more.

With a main unmatched file a bin is written by the first run whose data reaches its end, so a run cut anywhere gives the same rows, written once. A run with no such file writes all it has. A signal is known from its first event until it has logged nothing for `max_days_old`.

#### ped
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): `Parameter` of the event: the phase of a service, the ped input of a press. The two are read as one number.
*   `PedServices` (INT16): Ped services: walks begun (`EventId` 21) and ped overlap walks begun (67).
*   `PedActuation` (INT16): Button presses (`EventId` 90), repeat presses included.
*   `Unique_Actuations` (INT16): Unique presses: those more than `seconds_between_actuations` (default 15) after the previous press on that input, or with no earlier press known.
*   `Estimated_Volumes` (FLOAT): Estimated pedestrians from this bin and the 3 before it. Left out by `return_volumes=False` (default True). See [Pedestrian counts and volumes](#pedestrian-counts-and-volumes).

#### ped_delay
*   `TimeStamp` (DATETIME): Bin start time (rounded from EndTime of walk).
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `AvgPedDelay` (FLOAT): Average delay in seconds.
*   `Samples` (BIGINT): Number of ped delay events.

#### phase_wait
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `AvgPhaseWait` (FLOAT): Average wait time in seconds.
*   `MaxPhaseWait` (FLOAT): Longest wait in seconds.
*   `TotalSkips` (BIGINT): Count of skipped phases.
*   `Samples` (INT32): Waits averaged in this bin. A wait left out for a preempt is not one.

To roll the average up over a longer period, weight it: `SUM(AvgPhaseWait * Samples) / SUM(Samples)`. A plain average of the bins gives a bin with one wait the same say as a bin with twenty.

#### splits
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `EventId` (INT16): Split event ID (300-317).
*   `Services` (INT16): Number of times the split occurred.
*   `Average_Split` (FLOAT): Average split duration.

#### split_failures
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Green_Time` (FLOAT): Average green time, in seconds per cycle.
*   `Green_Occupancy` (FLOAT): Average green occupancy (0.0 - 1.0).
*   `Red_Occupancy` (FLOAT): Average red occupancy (0.0 - 1.0).
*   `Split_Failure` (INT16): Cycles that failed.
*   `Cycles` (INT16): Cycles finished in this bin, the count behind the three averages and `Split_Failure`. Left out with `by_cycle=True` (default False), where each row is one cycle.

`Cycles` lets a longer period roll up exactly from this table alone:

```sql
SELECT DeviceId, Phase,
       SUM(Split_Failure) / SUM(Cycles) AS Share_Failed,
       SUM(Green_Time * Cycles) / SUM(Cycles) AS Green_Time,
       SUM(Green_Occupancy * Cycles) / SUM(Cycles) AS Green_Occupancy,
       SUM(Red_Occupancy * Cycles) / SUM(Cycles) AS Red_Occupancy
FROM split_failures
GROUP BY DeviceId, Phase
```

#### terminations
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `PerformanceMeasure` (TEXT): 'GapOut', 'MaxOut', or 'ForceOff'.
*   `Total` (INT16): Count of terminations.

#### timeline
*   `StartTime` (DATETIME): Event start time.
*   `EndTime` (DATETIME): Event end time (or next event time).
*   `Duration` (FLOAT): Duration in seconds.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `IsValid` (BOOLEAN): Validity flag for the interval.
*   `EventClass` (TEXT): Description of the event class.
*   `EventValue` (INTEGER): Parameter value (Phase, Detector, etc.).

#### yellow_red
*   `TimeStamp` (DATETIME): Bin start time.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Signal_State` (INTEGER): Signal state during actuation.
*   `Red_Offset` (FLOAT): Time into red (seconds).
*   `Count` (FLOAT): Number of actuations.

#### arrival_pattern
*   `TimeStamp` (DATETIME): Bin start time, the bin of the cycle's red begin.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `Phase` (INT16): Phase number.
*   `Signal_State` (INT16): Signal state on arrival at the stop bar: 1 green, 8 yellow, 10 red.
*   `Red_Offset` (FLOAT): Seconds from the red begin to the arrival, rounded to the nearest `offset_slice_seconds`. Negative before red.
*   `Count` (FLOAT): Number of vehicles.

### Supporting State Tables
(Used primarily for incremental processing)

#### sf_unmatched
(Split Failures Unmatched)
The events a later run works out `split_failures` from: each detector's last event, and the events of a phase's unfinished cycle from its green on, with `Detector` 0 on the phase events.
*   `TimeStamp` (DATETIME): Timestamp of the event.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `EventId` (INT16): Event ID.
*   `Detector` (INT16): Detector number.
*   `Phase` (INT16): Phase number.

#### unmatched_events
*   `TimeStamp` (DATETIME): Timestamp of the event.
*   `DeviceId` (INTEGER or TEXT): Unique identifier for the controller (type depends on input).
*   `EventId` (INT16): Event ID.
*   `Parameter` (INT16): Parameter value.

Rows with `EventId` 927 to 959 are state carried between runs, not controller events. Each number has one meaning. A run must list every table the file holds rows of, or it raises. It raises as well on input that does not start after the last event held for its device, and, with `actuations`, `terminations`, `splits`, `coordination_agg`, `phase_wait` or `ped_delay` listed, on a run cut inside a bin, as they would write that bin twice:

| `EventId` | Written by | Holds | `Parameter` |
| --- | --- | --- | --- |
| 927 | `left_turn_gaps` | A lane on at the end of the data, stamped with its signal's last event | Detector |
| 928 | `left_turn_gaps` | A green or yellow not ended, stamped when its stretch of busy or empty road began | Phase |
| 929 | `left_turn_gaps` | `Seconds` of a slice of a bin not written yet, stamped with its bin plus one microsecond for each millisecond | Phase + 100 times the tenths of `Gap_Length` |
| 930 | `left_turn_gaps` | `Gaps` of a slice of a bin not written yet, stamped with its bin plus one microsecond for each | Phase + 100 times the tenths of `Gap_Length` |
| 931 to 934 | `coordination_agg` | Pattern, cycle length, actual cycle length, actual offset | The value |
| 935 | `timeline` | The last bin with data of each device | 0 |
| 936 | Every run with a main unmatched file | The last event of each device | 0 |
| 937 | `ped` | Each input's last press, one row per input | Input |
| 938 | `arrival_on_green`, `platoon_ratio` | A vehicle arriving after results stopped, at its projected time | Phase |
| 939, 940, 941 | `arrival_on_green`, `platoon_ratio` | Green, yellow and red clearance begins not written yet. 939 also marks a phase green when results stopped | Phase |
| 942 | `arrival_on_green`, `platoon_ratio`, `ped`, `yellow_red`, `arrival_pattern`, `approach_delay` or `left_turn_gaps` with `remove_incomplete` | A bin not written yet that `has_data` passed | 0 |
| 943 | `ped` | One unique actuation, stamped with its bin, in a bin not written yet or the last 3 written | Input |
| 944, 945 | `ped` | Each input's next bin to write, and its last activity | Input |
| 946, 947 | `ped` | One press, or one ped service, stamped with its bin, in a bin not written yet | Input |
| 948 | `arrival_on_green`, `platoon_ratio` | A vehicle arriving after results stopped that began while its detector held the spillback flag | Phase |
| 949 | `arrival_on_green`, `platoon_ratio` | A detector holding the spillback flag, stamped when it last turned off, or when it turned on if it is on | Detector |
| 950 | `arrival_on_green`, `platoon_ratio` | A detector on at the end of the data, stamped when it turned on | Detector |
| 951, 952, 953 | `yellow_red` | Green, yellow and red clearance begins of a cycle not finished writing | Phase |
| 954 | `yellow_red` | An actuation of a cycle not finished writing, already moved back by the latency | Phase |
| 955 | `timeline` | The last phase call event of each device | 0 |
| 956 | `actuations` with `fill_in_missing` | Each detector's last on event, for the zero rows of one silent for a whole run | Detector |
| 957 | `approach_delay` | A sum of a bin not written yet, stamped with its bin plus one microsecond for each unit. A sum too big for its bin takes more rows | Phase for `Delay_Seconds` in milliseconds, phase + 1000 for `Queued_Delay_Seconds` in half milliseconds |
| 958 | `approach_delay` | A vehicle still waiting, at its arrival, or a green, yellow or red begin a vehicle may need | Phase for a vehicle, phase + 1000 times 1, 8 or 10 for an event |
| 959 | `approach_delay` | A detector on at the end of the data, one holding the flag, when a flag began, or the on of a vehicle under the flag not at every stop bar of its detector yet | Detector, detector + 1000, + 2000 or + 3000 |
| 960, 961, 962 | `arrival_pattern` | Green, yellow and red clearance begins of a cycle not finished writing | Phase |
| 963 | `arrival_pattern` | A vehicle of a cycle not finished writing, or one that may arrive after a first green still to come, at its arrival at the stop bar | Phase |
| 970 | `has_data` | The signal's last event | 0 |
| 974 | `has_data` | The first bin not written, one per signal | 0 |
| 984, 993 | `has_data` | A bin part way through, stamped with that bin | Longest gap so far, minutes; minutes in unexpected gaps so far |
| 991 | `has_data` | A flash status in a bin part way through | 0 |
| 997 | `has_data` | The last 4 finished bins with events | Longest gap, minutes, or -1 for a flash status |

## Release Notes

See [CHANGELOG.md](docs/CHANGELOG.md) for a full history of changes.

## Future Plans

- Develop comprehensive documentation for each performance measure.

## Contributing

Ideas and contributions are welcome! Please feel free to submit a Pull Request. Note that GitHub Actions will automatically run unit tests on your code.

## License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
