Recipe Date Enrichment - dathere/qsv GitHub Wiki

Recipe: Date Enrichment

Tier: Intermediate Commands used: datefmt, luau, partition, apply Anchor dataset: NYC 311 Service Requests (1M-row sample, nyc311samp.csv)

This recipe expands the original Cookbook entry. The original snippets are preserved at the bottom of Cookbook for backwards compatibility.

Problem

NYC 311 service-request records have a Created Date like 12/31/2014 11:59:45 PM. For analytics you typically want:

ISO 8601 normalized timestamp
a Quarter column (Q1, Q2, …) for fiscal-year reporting
a Year column for partitioning
a Weekday column for "is this complaint a weekday or weekend phenomenon?"
Turnaround Time (TAT) — how long between Created Date and Closed Date?
partitioned output: one file per quarter for parallel downstream processing

All of this in one qsv pipeline.

Data

curl -LO https://raw.githubusercontent.com/wiki/dathere/qsv/files/nyc311samp.csv
ls -lh nyc311samp.csv
qsv headers nyc311samp.csv

Key columns: Created Date, Closed Date, Borough, Complaint Type, Status, Resolution Action Updated Date.

For larger experiments, also try the 1M-row bundled sample: resources/test/NYC_311_SR_2010-2020-sample-1M.csv.

Solution

1. Normalize `Created Date` to ISO 8601

qsv datefmt 'Created Date' nyc311samp.csv > step1.csv

datefmt recognizes 19 input formats automatically. The default output is RFC 3339 (2014-12-31T23:59:45Z).

2. Add `Year` and `Year-Month` columns

qsv datefmt 'Created Date' --formatstr '%Y' --new-column Year step1.csv > step2.csv
qsv datefmt 'Created Date' --formatstr '%Y-%m' --new-column YearMonth step2.csv > step3.csv

--formatstr takes chrono strftime specifiers.

3. Add `Weekday` and `Quarter` columns

qsv datefmt 'Created Date' --formatstr '%A' --new-column Weekday step3.csv > step4.csv

%A is the full weekday name. For numeric quarter, use a small Luau script (since strftime doesn't have a quarter specifier):

-- getquarter.lua (also at docs/cookbook/lua/getquarter.lua)
local month = tonumber(string.sub(col['Created Date'], 6, 7))  -- ISO YYYY-MM
local q = math.ceil(month / 3)
return "Q" .. q

qsv luau map Quarter getquarter.lua step4.csv > step5.csv

Column values are exposed as globals by default — access them with col['Created Date'] (or col.Amount / col[1], or the bare column name). Pass --no-globals to turn that off. The script file is just a positional argument (there is no -x or -f flag).

4. Compute `Turnaround Time` (TAT)

-- turnaroundtime.lua (also at docs/cookbook/lua/turnaroundtime.lua)
-- needs date.lua from https://github.com/Tieske/date
local date = require "date"
if string.len(col['Created Date']) == 0 or string.len(col['Closed Date']) == 0 then
  return ""
end
local diff = date.diff(date(col['Closed Date']), date(col['Created Date']))
return diff:spandays()   -- fractional days

qsv luau map TAT turnaroundtime.lua step5.csv > step6.csv

Both scripts are bundled at docs/cookbook/lua/ — use them as starting points.

5. Partition into one file per quarter

qsv partition Quarter nyc311byqtr/ step6.csv
ls nyc311byqtr/
# Q1.csv  Q2.csv  Q3.csv  Q4.csv

Now you have four files for parallel downstream processing.

Full pipeline (one shot)

qsv datefmt 'Created Date' nyc311samp.csv \
  | qsv datefmt 'Created Date' --formatstr '%Y' --new-column Year \
  | qsv datefmt 'Created Date' --formatstr '%Y-%m' --new-column YearMonth \
  | qsv datefmt 'Created Date' --formatstr '%A' --new-column Weekday \
  | qsv luau map Quarter getquarter.lua \
  | qsv luau map TAT turnaroundtime.lua \
  > nyc311_enriched.csv

# Then partition
qsv partition Quarter nyc311byqtr/ nyc311_enriched.csv

Variations

Average TAT per Borough, in Brooklyn only

qsv search --select Borough -i brooklyn nyc311samp.csv \
  | qsv luau map TAT -x -f turnaroundtime.lua \
  | tee brooklyn-311-details.csv \
  | qsv stats --everything > brooklyn-tat-stats.csv

tee writes the intermediate to a file and pipes the same data to stats for aggregation.

Cast all `*_date` columns to ISO 8601 in one pass

qsv datefmt '/(?i)_date$/' nyc311samp.csv > all_dates_iso.csv

The regex column selector matches any column whose name ends in _date (case-insensitive).

Use a SQL-style aggregation instead

qsv sqlp nyc311samp.csv \
  "SELECT
     STRFTIME(ts, '%Y') || '-Q' || CAST(EXTRACT(QUARTER FROM ts) AS VARCHAR) AS quarter,
     Borough,
     COUNT(*) AS complaints,
     ROUND(AVG(EXTRACT(EPOCH FROM closed) - EXTRACT(EPOCH FROM ts)) / 86400.0, 2) AS avg_tat_days
   FROM (
     SELECT Borough,
            STRPTIME(\"Created Date\", '%m/%d/%Y %H:%M') AS ts,
            STRPTIME(\"Closed Date\", '%m/%d/%Y %H:%M') AS closed
     FROM nyc311samp
     WHERE \"Closed Date\" != ''
   ) t
   GROUP BY quarter, Borough
   ORDER BY quarter, complaints DESC"

Polars SQL has no julianday, and its STRFTIME takes the value first and the format second. Its automatic date parsing also can't read US month/day/year, so the subquery parses both columns explicitly with STRPTIME. nyc311samp.csv stores 10/31/2017 8:45; the full NYC 311 export uses 12/31/2014 11:59:45 PM, which needs '%m/%d/%Y %I:%M:%S %p'. EXTRACT(EPOCH FROM …) turns each timestamp into seconds, so the difference ÷ 86,400 is the turnaround in days. See SQL & Polars.

[!NOTE] New in 24.0.0: Polars SQL has bare date-part functions, so the quarter label can be written YEAR(ts) || '-Q' || QUARTER(ts). On 23.0.1 and earlier, YEAR and QUARTER are rejected as unsupported functions.

Filter business-hours-only events

qsv luau filter \
  "local h = tonumber(string.sub(_['Created Date'], 12, 13)); return h >= 9 and h <= 17" \
  nyc311_enriched.csv > business_hours.csv

Performance notes

datefmt is multithreaded (🚀 in the README legend) — no index required.
luau is single-process but the interpreter is fast (millions of rows/sec on simple expressions).
On the full 27M-row NYC 311 export, the whole pipeline above takes ~2 minutes on an M2 Pro with an index.
For a no-index NYC 311 1M-sample run: ~6 seconds.