dfsql

• Revision: the standalone count command is replaced with len, so make sure to replace (count) and col "count" with len and col "len" respectively.
  • the unary count <col> command is unaffected.

Install

cargo install dfsql --features cli

Backends

Without Cargo features, the root Executor, Frame, MaterializedFrame, Error, and Result names use the dynamic backend.

Enabling polars-backend makes the root executor use Polars internally:

[dependencies]
dfsql = { version = "0.16", features = ["polars-backend"] }

The root Frame and MaterializedFrame types encapsulate the selected implementation, so Polars types are not part of dfsql's public API. The dynamic backend is still compiled and remains available explicitly through dfsql::backend::dynamic::{Executor,Frame}.

The cli feature enables the command-line application that uses Polars.

File formats

The cli feature reads and writes CSV (.csv), JSON arrays (.json), JSON Lines (.jsonl or .ndjson), HDV binary (.hdvb), and HDV text (.hdvt) files via dfsql::file_ops.

HDV conversion uses the core hdv crate without its Polars feature. The Polars conversion lives in dfsql, so upgrading Polars does not require a Polars-aware HDV utility crate.

How to run

dfsql --input your.csv --output a-new.csv
# ...or
dfsql -i your.csv -o a-new.csv

REPL

• exit/quit: exit the REPL loop.

  exit

• undo: undo the previous successful operation.

  undo

• reset: reset all the changes and go back to the original data frame.

  reset

• schema: show column names and types of the data frame.

  schema

• save: save the current data frame to a file.

  save a-new.csv

Statements

• select

  select <expr>*

  select last_name first_name

  • Select columns "last_name" and "first_name" and collect them into a data frame.
• Group by

  group (<col> | <var>)* agg <expr>*

  group first_name agg (count)

  • Group the data frame by column "first_name" and then aggregate each group with the count of the members.
• filter

  filter <expr>

  filter first_name = "John"

• limit

  limit <int>

  limit 5

• reverse

  reverse

• sort

  sort ((asc | desc | ()) <col>)*

  sort icpsr_id

• use

  use <var>

  use other

  • Switch to the data frame called other.
• join

  (left | right | inner | full) join <var> on <col> <col>?

  left join other on id ID

  • left join the data frame called other on my column id and its column ID

Expressions

• col: reference to a column.

  col : (<str> | <var>) -> <expr>

  select col first_name

• exclude: remove columns from the data frame.

  exclude : <expr>* -> <expr>

  select exclude last_name first_name

• literal: literal values like 42, "John", 1.0, and null.
• binary operations

  select a * b

  • Calculate the product of columns "a" and "b" and collect the result.
• unary operations

  select -a

  select sum a

  • Sum all values in column "a" and collect the scalar result.
• alias: assign a name to a column.

  alias : (<col> | <var>) <expr> -> <expr>

  select alias product a * b

  • Assign the name "product" to the product and collect the new column.
• conditional

  <conditional> : if <expr> then <expr> (if <expr> then <expr>)* otherwise <expr> -> <expr>

  select if class = 0 then "A" if class = 1 then "B" else null

• cast: cast a column to either type str, int, or float.

  cast : <type> <expr> -> <expr>

  select cast str id

  • Cast the column "id" to type str and collect the result.