Find Where Work Waits: Computing Dwell Time Between Workflow Steps From a Plain CSV Export

说明与用途

Python脚本读取工作流状态变更CSV，按记录ID分组事件，按时间戳排序，计算相邻状态转换的持续时间，并打印每个步骤的中位等待时间（小时），识别最长等待的交接环节。

问题

A business workflow audit needs to identify where work is stuck, but the only available data is a plain CSV export with record ID, status, and timestamp columns.

结果

The script outputs median dwell time per workflow step in hours, identifying which handoff or transition has the longest wait. The largest median wait is typically a handoff where someone must notice, decide, or copy something.

工作流

• Read the CSV file with csv.DictReader, grouping rows by record_id into a defaultdict of (timestamp, status) tuples
• Sort each record's events by timestamp
• Compute pairwise transition durations between consecutive statuses for each record
• Aggregate durations into a defaultdict keyed by 'status1 -> status2'
• Sort aggregated waits by descending median and print each step with its median hours and count

证据

record_id,status,changed_at 101,new,2026-10-01T09:02 101,contacted,2026-10-01T15:40 101,quoted,2026-10-03T11:15 102,new,2026-10-01T10:10 102,contacted,2026-10-02T09:30

来源

rows = defaultdict(list)

来源

for (t1, s1), (t2, s2) in zip(events, events[1:]):

来源

The transition with the largest median wait is usually a handoff: someone has to notice, decide or copy something.

来源

Two cautions: status changes are only logged when someone updates the record, so long waits can also mean 'forgot to update.' Check a few records by hand.

来源

This works as a warm-up before a fuller workflow audit. (Disclosure: I co-founded NxFlowAI, whose first audit takes 72 hours.)

来源