1.5.1.1 Read & write🔗ℹ

Writing a CSV file uses write-csv, and reading one should look familiar:

> (define df     (dataframe (list (series '(1 2 3) #:name "foo")                      (series (list polars-null "bak" "baz") #:name "bar"))))

> (write-csv df path)

> (read-csv path)

shape: (3, 2) ┌─────┬──────┐ │ foo ┆ bar  │ │ --- ┆ ---  │ │ i64 ┆ str  │ ╞═════╪══════╡ │ 1   ┆ null │ │ 2   ┆ bak  │ │ 3   ┆ baz  │ └─────┴──────┘

1.5.1.2 Scan🔗ℹ

Polars can also scan a CSV input. Scanning delays the actual parsing of the file and returns a lazyframe, a lazy computation holder; collect runs it. See Lazy API for why that is desirable.

> (scan-csv path)

#<lazyframe>

> (collect (scan-csv path))

shape: (3, 2) ┌─────┬──────┐ │ foo ┆ bar  │ │ --- ┆ ---  │ │ i64 ┆ str  │ ╞═════╪══════╡ │ 1   ┆ null │ │ 2   ┆ bak  │ │ 3   ┆ baz  │ └─────┴──────┘

1.5.1.3 Reading options🔗ℹ

Upstream documents read_csv’s options on its reference page; here they are with their Racket spellings. Each keyword keeps Python’s name and works the same on read-csv and scan-csv. The file, "flights.tsv", is 102 rows of nycflights13:

$ head -n 5 flights.tsv

year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin dest air_time distance hour minute time_hour

2013 1 1 517 515 2 830 819 11 UA 1545 N14228 EWR IAH 227 1400 5 15 2013-01-01 05:00:00

2013 1 1 533 529 4 850 830 20 UA 1714 N24211 LGA IAH 227 1416 5 29 2013-01-01 05:00:00

2013 1 1 542 540 2 923 850 33 AA 1141 N619AA JFK MIA 160 1089 5 40 2013-01-01 05:00:00

2013 1 1 544 545 -1 1004 1022 -18 B6 725 N804JB JFK BQN 183 1576 5 45 2013-01-01 05:00:00

Separator. Read with the default comma, a tab-separated file is one column whose name is the whole header line. Python’s read_csv returns that column; read-csv is stricter and raises, naming the separator to pass. Passing any #:separator, even #\,, turns the check off, and scan-csv does not check.

> (read-csv "flights.tsv")

read-csv: flights.tsv reads as one column whose header

splits on #\tab into 19 fields; pass #:separator #\tab, or

#:separator #\, to keep one column

> (shape (read-csv "flights.tsv" #:separator #\,))

'(102 1)

Missing values. Types are inferred from the first 100 rows, and the first NA is in row 101:

$ tail -n 2 flights.tsv

2013 1 1 1525 1530 -5 1934 1805 NA MQ 4525 N719MQ LGA XNA NA 1147 15 30 2013-01-01 15:00:00

2013 1 1 NA 1630 NA NA 1815 NA EV 4308 N18120 EWR RDU NA 416 16 30 2013-01-01 16:00:00

#:null-values takes one marker or a list of them:

> (read-csv "flights.tsv" #:separator #\tab)

read-csv: failed to read csv from flights.tsv: could not

parse `NA` as dtype `i64` at column 'arr_delay' (column

number 9)

The current offset in the file is 648 bytes.

You might want to try:

- increasing #:infer-schema-length (e.g.

#:infer-schema-length 10000, or #f for every row),

- specifying correct dtype with #:schema-overrides

- setting #:ignore-errors to #t,

- adding `NA` to #:null-values.

Original error: ```invalid primitive value found during CSV

parsing```

> (define flights (read-csv "flights.tsv" #:separator #\tab #:null-values "NA"))

> (~> flights (select "dep_delay" "arr_delay") (tail 3))

shape: (3, 2) ┌───────────┬───────────┐ │ dep_delay ┆ arr_delay │ │ ---       ┆ ---       │ │ i64       ┆ i64       │ ╞═══════════╪═══════════╡ │ -7        ┆ -4        │ │ -5        ┆ null      │ │ null      ┆ null      │ └───────────┴───────────┘

> (~> (read-csv "flights.tsv" #:separator #\tab #:null-values '("NA" "-"))       (ref #:columns "air_time")       null-count)

2

Types. #:schema-overrides fixes a column’s type, in any spelling series accepts; #:infer-schema-length sets how many rows inference reads (#f for all of them, 0 for strings throughout); #:ignore-errors reads what does not parse as null. Dates have their own keyword (Parsing dates).

> (~> (read-csv "flights.tsv" #:separator #\tab #:null-values "NA"                 #:schema-overrides '(("dep_delay" . f64) ("flight" . int32)))       (select "dep_delay" "flight")       (tail 2))

shape: (2, 2) ┌───────────┬────────┐ │ dep_delay ┆ flight │ │ ---       ┆ ---    │ │ f64       ┆ i32    │ ╞═══════════╪════════╡ │ -5.0      ┆ 4525   │ │ null      ┆ 4308   │ └───────────┴────────┘

> (~> (read-csv "flights.tsv" #:separator #\tab #:infer-schema-length #f)       (ref #:columns "dep_delay")       dtype)

'string

> (~> (read-csv "flights.tsv" #:separator #\tab #:ignore-errors #t)       (ref #:columns "dep_delay")       null-count)

1

Layout. A comment line, a separator and a quote character each have a keyword:

$ head -n 5 notes.csv

# carrier notes, typed up by hand

carrier;note

UA;'late; weather'

AA;on time

> (read-csv "notes.csv" #:separator #\; #:comment-prefix "#" #:quote-char #\')

shape: (2, 2) ┌─────────┬───────────────┐ │ carrier ┆ note          │ │ ---     ┆ ---           │ │ str     ┆ str           │ ╞═════════╪═══════════════╡ │ UA      ┆ late; weather │ │ AA      ┆ on time       │ └─────────┴───────────────┘

"latin1.csv" is not UTF-8; its é is one byte, which a UTF-8 terminal shows as �:

$ head -n 5 latin1.csv

carrier,name

B6,JetBlue

ZZ,Caf� Air

> (read-csv "latin1.csv" #:encoding 'utf8-lossy)

shape: (2, 2) ┌─────────┬──────────┐ │ carrier ┆ name     │ │ ---     ┆ ---      │ │ str     ┆ str      │ ╞═════════╪══════════╡ │ B6      ┆ JetBlue  │ │ ZZ      ┆ Caf� Air │ └─────────┴──────────┘

#:has-header, #:skip-rows and #:n-rows frame the rows to read:

$ head -n 5 parts/part-1.csv

origin,dest,dep_delay

EWR,IAH,2

LGA,IAH,4

> (read-csv "parts/part-1.csv" #:has-header #f #:skip-rows 1 #:n-rows 1)

shape: (1, 3) ┌──────────┬──────────┬──────────┐ │ column_1 ┆ column_2 ┆ column_3 │ │ ---      ┆ ---      ┆ ---      │ │ str      ┆ str      ┆ i64      │ ╞══════════╪══════════╪══════════╡ │ EWR      ┆ IAH      ┆ 2        │ └──────────┴──────────┴──────────┘

Columns. #:columns reads some columns, by name or position; #:new-columns renames the first ones; #:row-index-name adds a row number.

> (read-csv "flights.tsv" #:separator #\tab #:null-values "NA"             #:columns '("carrier" "flight" "dep_delay")             #:row-index-name "row" #:n-rows 3)

shape: (3, 4) ┌─────┬───────────┬─────────┬────────┐ │ row ┆ dep_delay ┆ carrier ┆ flight │ │ --- ┆ ---       ┆ ---     ┆ ---    │ │ u32 ┆ i64       ┆ str     ┆ i64    │ ╞═════╪═══════════╪═════════╪════════╡ │ 0   ┆ 2         ┆ UA      ┆ 1545   │ │ 1   ┆ 4         ┆ UA      ┆ 1714   │ │ 2   ┆ 2         ┆ AA      ┆ 1141   │ └─────┴───────────┴─────────┴────────┘

> (read-csv "parts/part-1.csv" #:has-header #f #:skip-rows 1             #:new-columns '("from" "to" "delay"))

shape: (2, 3) ┌──────┬─────┬───────┐ │ from ┆ to  ┆ delay │ │ ---  ┆ --- ┆ ---   │ │ str  ┆ str ┆ i64   │ ╞══════╪═════╪═══════╡ │ EWR  ┆ IAH ┆ 2     │ │ LGA  ┆ IAH ┆ 4     │ └──────┴─────┴───────┘

Messy files. "stations.csv" has a title line, decimal commas, a missing-value marker per column and a line with a field too many:

$ head -n 6 stations.csv

Station log, "north" region

station;day;at;temp;rain;note

Oslo;2024-05-01;06:00:00;12,5;0,0;-

Bergen;2024-05-01;06:30:00;-;3,2;

Tromso;2024-05-02;07:15:00;4,0;n/a;cold

Narvik;2024-05-02;08:00:00;3,5;1,0;windy;gusts

#:skip-lines skips lines without parsing them; #:decimal-comma reads 12,5 as 12.5; a #:null-values association list applies each marker to its column only; #:truncate-ragged-lines drops the extra field.

> (define stations     (read-csv "stations.csv" #:skip-lines 1 #:separator #\; #:decimal-comma #t               #:null-values '(("temp" . "-") ("rain" . "n/a"))               #:truncate-ragged-lines #t #:try-parse-dates #t))

> stations

shape: (4, 6) ┌─────────┬────────────┬──────────┬──────┬──────┬───────┐ │ station ┆ day        ┆ at       ┆ temp ┆ rain ┆ note  │ │ ---     ┆ ---        ┆ ---      ┆ ---  ┆ ---  ┆ ---   │ │ str     ┆ date       ┆ time     ┆ f64  ┆ f64  ┆ str   │ ╞═════════╪════════════╪══════════╪══════╪══════╪═══════╡ │ Oslo    ┆ 2024-05-01 ┆ 06:00:00 ┆ 12.5 ┆ 0.0  ┆ -     │ │ Bergen  ┆ 2024-05-01 ┆ 06:30:00 ┆ null ┆ 3.2  ┆ null  │ │ Tromso  ┆ 2024-05-02 ┆ 07:15:00 ┆ 4.0  ┆ null ┆ cold  │ │ Narvik  ┆ 2024-05-02 ┆ 08:00:00 ┆ 3.5  ┆ 1.0  ┆ windy │ └─────────┴────────────┴──────────┴──────┴──────┴───────┘

A gzip, zlib or zstd file reads like a plain one. API gaps: schema, with_column_names, include_file_paths, missing_columns, use_pyarrow. The tuning knobs change no result and are not bound.

1.5.1.4 Writing options🔗ℹ

write-csv takes write_csv’s options with their Racket spellings: separator, quoting, line ends, how nulls, floats and temporal values are written.

> (write-csv stations path #:separator #\; #:decimal-comma #t #:null-value "-"              #:date-format "%d.%m.%Y" #:time-format "%H:%M")

> (display (file->string path))

station;day;at;temp;rain;note Oslo;01.05.2024;06:00;12,5;0,0;- Bergen;01.05.2024;06:30;-;3,2;- Tromso;02.05.2024;07:15;4,0;-;cold Narvik;02.05.2024;08:00;3,5;1,0;windy

> (write-csv (select stations "station" "temp") path              #:quote-style 'non-numeric #:float-precision 2)

> (display (file->string path))

"station","temp" "Oslo",12.50 "Bergen", "Tromso",4.00 "Narvik",3.50

API gap: compressed output (compression).

1.5.1.5 Parsing dates🔗ℹ

Without help a date column reads as strings. #:try-parse-dates parses each column whose values look like dates, times or datetimes (ISO 8601 and a handful of other formats) as it is read, on read-csv and scan-csv alike. "apple_stock.csv" holds upstream’s hundred closing prices of Apple stock:

$ head -n 5 apple_stock.csv

Date,Close

1981-02-23,24.62

1981-05-06,27.38

1981-05-18,28.0

1981-09-25,14.25

> (read-csv "apple_stock.csv" #:try-parse-dates #t)

shape: (100, 2) ┌────────────┬────────┐ │ Date       ┆ Close  │ │ ---        ┆ ---    │ │ date       ┆ f64    │ ╞════════════╪════════╡ │ 1981-02-23 ┆ 24.62  │ │ 1981-05-06 ┆ 27.38  │ │ 1981-05-18 ┆ 28.0   │ │ 1981-09-25 ┆ 14.25  │ │ 1982-07-08 ┆ 11.0   │ │ …          ┆ …      │ │ 2012-05-16 ┆ 546.08 │ │ 2012-12-04 ┆ 575.85 │ │ 2013-07-05 ┆ 417.42 │ │ 2013-11-07 ┆ 512.49 │ │ 2014-02-25 ┆ 522.06 │ └────────────┴────────┘

> (~> (read-csv "flights.tsv" #:separator #\tab #:null-values "NA"                 #:try-parse-dates #t)       (select "time_hour")       (head 2))

shape: (2, 1) ┌─────────────────────┐ │ time_hour           │ │ ---                 │ │ datetime[μs]        │ ╞═════════════════════╡ │ 2013-01-01 05:00:00 │ │ 2013-01-01 05:00:00 │ └─────────────────────┘

Inference reads the first #:infer-schema-length rows, 100 by default. A binary format such as Parquet carries its schema, which is used as it is. A column in a format the parser does not recognise stays a string; str->date and its siblings parse it with an explicit format (Casting strings to dates).