CSV

Getting started

Our first move, is to tell the compiler, where your CSV file may be found. CSV.resource is a macro which reads the column headers and injects them into the compilers type system. Here; we inline a string for the compiler to analyze.

import io.github.quafadas.table.*

val csv : CsvIterator[("col1", "col2", "col3"), (Int, Int, Int)] = CSV.fromString("col1,col2,col3\n1,2,7\n3,4,8\n5,6,9")
// csv: CsvIterator[Tuple3["col1", "col2", "col3"], Tuple3[Int, Int, Int]] = empty iterator

val asList = LazyList.from(csv)
// asList: LazyList[NamedTuple[Tuple3["col1", "col2", "col3"], Tuple3[Int, Int, Int]]] = LazyList(
//   (1, 2, 7),
//   (3, 4, 8),
//   (5, 6, 9)
// )

asList.take(2).consoleFormatNt(fansi = false)
// res0: String = """| |col1|col2|col3|
// |-|----|----|----|
// |0|   1|   2|   7|
// |1|   3|   4|   8|
// |-|----|----|----|"""

The key point of the whole library - Note the take(2) method. This is a method from scala's stdlib. In case it's not clear - you get all the other stuff too - .filter, groupMapReduce, which are powerful. Their use is strongly typed, because CSVIterator is merely an Iterator of NamedTuples - you access the columns via their column name.

Reading CSV files

Reading CSV's as strings would be relatively uncommon - normally .csv is a file.

The CSV object has a few methods of reading CSV files. It is fundamentally scala.Source based inside the macro.

import io.github.quafadas.table.*

val csv_resource = CSV.resource("simple.csv")
val csv_abs = CSV.absolutePath("/users/simon/absolute/path/simple.csv")
val csv_url = CSV.url("https://example.com/simple.csv")

// Anchored to the source file this call sits in, and to the project root above it,
// so neither depends on where the build was invoked from.
val opts = CsvOpts(typeInferrer = TypeInferrer.FirstN(1000), delimiter = ';')
val csv_rel = CSV.relativeToSource("file.csv", opts)
val csv_root = CSV.projectRoot("data/file.csv", opts)

For customisation options look at CsvOpts, and supply that as a second argument to any of the above methods.

Closing the file

A CsvIterator holds the file handle it reads from, and releases it as soon as the rows run out. The usual shapes therefore need nothing from you:

CSV.resource("simple.csv").toSeq          // drained, so closed
CSV.resource("simple.csv").foreach(println) // likewise

If you stop reading early, the handle stays open until the JVM notices the iterator is unreachable - which is tied to garbage collection rather than to your reading, so enough abandoned reads in a row can exhaust the process's file descriptors. Say so explicitly, or let Using do it:

val head = Using(CSV.absolutePath("big.csv"))(_.take(10).toList).get

Note that .take(10) hands back a new iterator, which has no way to tell the original it is finished - that is why the Using wraps the CsvIterator itself rather than the slice. The same applies to JsonTable; Excel shares one cached workbook per file instead, which ExcelResourceManager.cleanup releases.

Columnar Reading

By default, CSV data is read as an iterator of rows (CsvIterator). For analytical workloads, you can read CSV data directly into a columnar format using ReadAs.Columns:

import io.github.quafadas.table.*

// Read as columns - returns NamedTuple of Arrays
val columnar = CSV.fromString("name,age,score\nAlice,30,95.5\nBob,25,87.3", CsvOpts(readAs = ReadAs.Columns))
// columnar: NamedTuple[Tuple3["name", "age", "score"], *:[Array[String], *:[Array[Int], *:[Array[Double], EmptyTuple]]]] = (
//   Array("Alice", "Bob"),
//   Array(30, 25),
//   Array(95.5, 87.3)
// )

// Access columns directly as typed arrays
val names: Array[String] = columnar.name
// names: Array[String] = Array("Alice", "Bob")
val ages: Array[Int] = columnar.age
// ages: Array[Int] = Array(30, 25)
val scores: Array[Double] = columnar.score
// scores: Array[Double] = Array(95.5, 87.3)

println(s"Average age: ${ages.sum.toDouble / ages.length}")
// Average age: 27.5
println(s"Max score: ${scores.max}")
// Max score: 95.5

Columnar reading:

• Loads all data into memory at once
• Provides direct array access to columns
• More efficient for column-oriented analytics
• Works with all CSV reading methods (resource, absolutePath, fromString, etc.)
• Supports all type inference options

For advanced use cases requiring a single dense array with stride information (e.g., for BLAS/LAPACK interop), see ReadAs.ArrayDenseColMajor[T]() and ReadAs.ArrayDenseRowMajor[T]() in the Column Orient cookbook.

Strongly Typed CSVs

Scautable analyzes the CSV file and provides types and names for the columns. That means should get IDE support, auto complete, error messages for non sensical code, etc.

import io.github.quafadas.table.*

val experiment = asList
  .mapColumn["col1", Double](_.toDouble)
  .mapColumn["col2", Boolean](_.toInt > 3)
// experiment: LazyList[NamedTuple[Tuple3["col1", "col2", "col3"], *:[Double, *:[Boolean, *:[Int, EmptyTuple]]]]] = LazyList(
//   (1.0, false, 7),
//   (3.0, true, 8),
//   (5.0, true, 9)
// )

println(experiment.consoleFormatNt(fansi = false))
// | |col1| col2|col3|
// |-|----|-----|----|
// |0| 1.0|false|   7|
// |1| 3.0| true|   8|
// |2| 5.0| true|   9|
// |-|----|-----|----|

e.g. one cannot make column name typos because they are embedded in the type system.

val nope = experiment.mapColumn["not_col1", Double](_.toDouble)

// error:
// value toDouble is not a member of EmptyTuple
//  val nope = experiment.mapColumn["not_col1", Double](_.toDouble)
//                                                      ^^^^^^^^^^
// error:
// Column ("not_col1" : String) not found
//  val nope = experiment.mapColumn["not_col1", Double](_.toDouble)
//                                                                 ^

Column Operations

Let's have a look at the some column manipulation helpers;

• dropColumn
• addColumn
• renameColumn
• mapColumn

val colmanipuluation = experiment
  .dropColumn["col2"]
  .addColumn["col4", Double](x => x.col1 * 2 + x.col3.toDouble)
  .renameColumn["col4", "col4_renamed"]
  .mapColumn["col4_renamed", Double](_ * 2)
// colmanipuluation: LazyList[NamedTuple[*:["col1", *:["col3", *:["col4_renamed", EmptyTuple]]], *:[Double, *:[Int, *:[Double, EmptyTuple]]]]] = LazyList(
//   (1.0, 7, 18.0),
//   (3.0, 8, 28.0),
//   (5.0, 9, 38.0)
// )

colmanipuluation.consoleFormatNt(fansi = false)
// res5: String = """| |col1|col3|col4_renamed|
// |-|----|----|------------|
// |0| 1.0|   7|        18.0|
// |1| 3.0|   8|        28.0|
// |2| 5.0|   9|        38.0|
// |-|----|----|------------|"""

println(colmanipuluation.column["col4_renamed"].foldLeft(0.0)(_ + _))
// 84.0

// and select a subset of columns
colmanipuluation.columns[("col4_renamed", "col1")].consoleFormatNt(fansi = false)
// res7: String = """| |col4_renamed|col1|
// |-|------------|----|
// |0|        18.0| 1.0|
// |1|        28.0| 3.0|
// |2|        38.0| 5.0|
// |-|------------|----|"""

Several columns at once

mapColumn takes one column at a time, which gets repetitive on a wide table - a spreadsheet where a dozen columns all arrived as String needs a dozen calls. mapColumns takes a spec instead: a named tuple whose names are columns and whose values are the functions to apply.

val parsed = asList.mapColumns((
  col1 = (i: Int) => i.toDouble,
  col2 = (i: Int) => i > 3
))
// parsed: LazyList[NamedTuple[Tuple3["col1", "col2", "col3"], *:[Double, *:[Boolean, *:[Int, EmptyTuple]]]]] = LazyList(
//   (1.0, false, 7),
//   (3.0, true, 8),
//   (5.0, true, 9)
// )

parsed.consoleFormatNt(fansi = false)
// res8: String = """| |col1| col2|col3|
// |-|----|-----|----|
// |0| 1.0|false|   7|
// |1| 3.0| true|   8|
// |2| 5.0| true|   9|
// |-|----|-----|----|"""

Each column's new type is its function's result type. Columns the spec doesn't mention are untouched, and the order of the spec is irrelevant. Note that the lambdas need their parameter types written out - the spec's type is being inferred from the lambdas, so there is no expected type to infer them from.

The errors are the same shape as mapColumn's, and name the offending column:

asList.mapColumns((not_col1 = (i: Int) => i.toDouble))
// error: 
// mapColumns: no column named not_col1

asList.mapColumns((col1 = (s: String) => s.toDouble))
// error: 
// mapColumns: the function given for column col1 does not accept its type

retype is the same idea for forceColumnType - it forces several column types in one call. Like forceColumnType it is an unchecked cast, so see Displaying Tables, where stripping display tags back off is the use that is always sound.

Accumulating, slicing etc

We can delegate all such concerns, to the standard library in the usual way - as we have everything in side the type system!

colmanipuluation.filter(_.col4_renamed > 20).groupMapReduce(_.col1)(_.col4_renamed)(_ + _)
// res11: Map[Double, Double] = Map(3.0 -> 28.0, 5.0 -> 38.0)

Joining

Joining is the one relational operation the standard library cannot do for us. It can group and fold rows perfectly well, but it has no way to work out the type of two named tuples stitched together on a key - so join is a first class operation here.

val orders = Seq(
  (custId = 1, qty = 5),
  (custId = 2, qty = 7),
  (custId = 1, qty = 9)
)
// orders: Seq[NamedTuple[Tuple2["custId", "qty"], Tuple2[Int, Int]]] = List(
//   (1, 5),
//   (2, 7),
//   (1, 9)
// )

val customers = Seq(
  (custId = 1, name = "ada"),
  (custId = 2, name = "bob")
)
// customers: Seq[NamedTuple[Tuple2["custId", "name"], Tuple2[Int, String]]] = List(
//   (1, "ada"),
//   (2, "bob")
// )

orders.join(customers)["custId"].consoleFormatNt(fansi = false)
// res12: String = """| |custId|qty|name|
// |-|------|---|----|
// |0|     1|  5| ada|
// |1|     2|  7| bob|
// |2|     1|  9| ada|
// |-|------|---|----|"""

The key is written after the right hand table, because the compiler works that table's shape out from the argument and only the key needs spelling out. The result is every column of the left table, then every column of the right one except the key.

Where the key is named differently on each side, use joinOn. The output keeps the left name.

val people = Seq((id = 1, name = "ada"), (id = 2, name = "bob"))
// people: Seq[NamedTuple[Tuple2["id", "name"], Tuple2[Int, String]]] = List(
//   (1, "ada"),
//   (2, "bob")
// )

orders.joinOn(people)["custId", "id"].consoleFormatNt(fansi = false)
// res13: String = """| |custId|qty|name|
// |-|------|---|----|
// |0|     1|  5| ada|
// |1|     2|  7| bob|
// |2|     1|  9| ada|
// |-|------|---|----|"""

leftJoin and leftJoinOn keep every left row, and the right hand columns become Option:

val sparse = Seq((custId = 1, name = "ada"))
// sparse: Seq[NamedTuple[Tuple2["custId", "name"], Tuple2[Int, String]]] = List(
//   (1, "ada")
// )

orders.leftJoin(sparse)["custId"].consoleFormatNt(fansi = false)
// res14: String = """| |custId|qty|     name|
// |-|------|---|---------|
// |0|     1|  5|Some(ada)|
// |1|     2|  7|     None|
// |2|     1|  9|Some(ada)|
// |-|------|---|---------|"""

A right hand column that is already an Option is left as it is rather than nesting into Option[Option[_]] - which does mean an unmatched row and a matched row holding None look alike.

A column name that appears on both sides is a compile error naming the offender, rather than a silently duplicated or overwritten column. Rename or drop it first.

Seq((custId = 1, name = "x")).join(customers)["custId"]
// error: 
// join: column name exists on both sides - rename or drop it first

Unknown keys, and keys whose types disagree, are caught in the same way:

orders.joinOn(people)["custId", "nope"]
// error:
// joinOn: no column named ("nope" : String) on the right
// orders.joinOn(people)["custId", "nope"]
//                                       ^

Worth knowing:

• The left side streams; the right is read into a hash index the first time the result is pulled. The right table is therefore the one that has to fit in memory - put the smaller table there.
• An inner join narrows an optional key. A None key cannot have matched, so join on a custId: Option[Int] hands back custId: Int - no .get that could never have thrown. A leftJoin leaves the key alone, because an unmatched row survives still holding its None.
• A None key never matches anything - as in SQL, where NULL = NULL is never true, and pandas, which drops missing keys from a merge. leftJoin still keeps such a row, with its right hand columns all None. Were None to match None instead, missing data would square itself: three missing keys on each side would be nine output rows carrying no information. To match missing to missing deliberately, map the key to a sentinel first - mapColumn["custId", Int](_.getOrElse(-1)).
• Left order is preserved, and a left row matching several right rows emits them in the right table's own order.
• Key types must agree exactly. A column carrying a display tag from formatColumn will not match an untagged one - join first, format afterwards.
• Only inner and left joins are built in. A right join is a left join with the tables swapped.