Query a large CSV file lazily

Count the flights that left more than an hour late from each New York City airport, with a lazy query, so that Polars can push the column selection and the filter into the CSV reader.

The data are all 336,776 departures from New York City airports in 2013, from the R nycflights13 package (flights).

Requires the phpolars extension (Getting started). Runnable scripts: lazy-csv.setup.php and lazy-csv.php.

// nycflights13's flights table: 336,776 rows, 33 MB.$url = 'https://raw.githubusercontent.com/phpstats/datasets'    .'/main/csv/flights.csv';if (file_exists('flights.csv')) {    fwrite(STDERR, "flights.csv already exists.\n");    exit(1);}copy($url, 'flights.csv');echo filesize('flights.csv'), " bytes\n";

33206996 bytes

use function Phpolars\{col, len, scan_csv};$query = scan_csv('flights.csv', nullValues: 'NA')    ->filter(col('dep_delay')->gt(60))    ->groupBy('origin')    ->agg(        len()->alias('flights'),        col('arr_delay')->mean()->round(1)            ->alias('arr_delay'),    )    ->sort('origin');echo $query->explain(), "\n\n";echo $query->collect();

SORT BY [col("origin")]
  AGGREGATE[maintain_order: false]
    [len().alias("flights"), col("arr_delay").mean().round()] BY [col("origin")]
    FROM
    simple π 2/2 ["origin", "arr_delay"]
      Csv SCAN [flights.csv]
      PROJECT 3/20 COLUMNS
      SELECTION: col("dep_delay") > 60
      ESTIMATED ROWS: 368967

shape: (3, 3)
┌────────┬─────────┬───────────┐
│ origin ┆ flights ┆ arr_delay │
│ ---    ┆ ---     ┆ ---       │
│ str    ┆ u32     ┆ f64       │
╞════════╪═════════╪═══════════╡
│ EWR    ┆ 10940   ┆ 117.5     │
│ JFK    ┆ 8401    ┆ 117.8     │
│ LGA    ┆ 7240    ┆ 122.8     │
└────────┴─────────┴───────────┘

• PROJECT 3/20 COLUMNS: the reader converts only three columns to values. It still reads and splits every line of the file.
• SELECTION: the filter runs during the scan, so rejected rows never reach the aggregation.
• ESTIMATED ROWS is the planner’s estimate, not a count.
• flights counts every delayed departure. arr_delay, in minutes, averages the flights with a recorded arrival, because mean() skips nulls.
• scan_csv() doesn’t open the file. A missing file raises an error at explain() or collect().
• read_csv() would parse all 20 columns of every row before filtering.
• collect(engine: 'streaming') runs the query in batches, which can lower peak memory on large files.

Reference: scan_csv() · explain() · collect() · Lazy queries · Memory on large files