# Moving from data.table

Your data.table code keeps running unchanged on ry, so moving it is optional. What a move buys you
is the checker: a [DataFrame](/reference/dataframes.md) query is typed column by column, so a misspelled
column or a sum of a text column is reported before the query runs. You can move one query at a
time, because `as.DataFrame(dt)` converts a `data.table` and `as.data.table(df)` converts back,
keeping every column's type.

ry can do most of the rewriting for you, as [Rewriting automatically](#rewriting-automatically)
shows. Each section below shows a data.table idiom and then its DataFrame equivalent, with the
output of `ry run`. The examples share one table:

```r
sales <- DataFrame(
  region = ["eu", "us", "eu", "asia"],
  units = [3L, 5L, 2L, 7L],
  price = [1.5, 2, 3, 0.5]
)
sales
```

```text
   region units price
   <char> <int> <num>
1:     eu     3   1.5
2:     us     5   2.0
3:     eu     2   3.0
4:   asia     7   0.5
```

## Reading a file

| data.table | DataFrame |
| --- | --- |
| `fread("sales.csv")` | `DataFrame.read_csv("sales.csv")` |
| `fread("sales.csv", stringsAsFactors = TRUE)` | `DataFrame.read_csv("sales.csv", strings_as_factors = TRUE)` |

When the path is a string literal, the checker reads the file and types every column.

## Filtering rows: `dt[i]`

```r
dt[units > 2L & region != "asia"]
```

`DataFrame.filter` keeps the rows where every condition holds:

```r
sales |> DataFrame.filter(Expr.col("units") > 2L, Expr.col("region") != "asia")
```

```text
   region units price
   <char> <int> <num>
1:     eu     3   1.5
2:     us     5   2.0
```

## Selecting and computing columns: `dt[, j]`

```r
dt[, .(region, units)]
dt[, .(revenue = units * price)]
```

`DataFrame.select` takes column names as strings and computed columns as named expressions:

```r
sales |> DataFrame.select("region", "units")
sales |> DataFrame.select(revenue = Expr.col("units") * Expr.col("price"))
```

```text
   region units
   <char> <int>
1:     eu     3
2:     us     5
3:     eu     2
4:   asia     7
   revenue
     <num>
1:     4.5
2:    10.0
3:     6.0
4:     3.5
```

## Grouping: `dt[, j, by]`

```r
dt[, .(units = sum(units)), by = region]
```

`DataFrame.group_by` names the groups, and `GroupBy.agg` gives each aggregation:

```r
sales |>
  DataFrame.group_by("region") |>
  GroupBy.agg(units = Expr.col("units") |> Expr.sum())
```

```text
   region units
   <char> <int>
1:     eu     5
2:     us     5
3:   asia     7
```

## Adding a column: `:=`

```r
dt[, revenue := units * price]
```

`DataFrame.with_columns` returns a new DataFrame with the column added or replaced, so assign the
result. A DataFrame follows R's copy-on-modify rule instead of updating in place, so a copy you took
earlier keeps its old columns:

```r
sales <- sales |> DataFrame.with_columns(revenue = Expr.col("units") * Expr.col("price"))
sales
```

```text
   region units price revenue
   <char> <int> <num>   <num>
1:     eu     3   1.5     4.5
2:     us     5   2.0    10.0
3:     eu     2   3.0     6.0
4:   asia     7   0.5     3.5
```

To change only some rows, as `dt[units > 2L, flag := TRUE]` does, compute the column for every row
with `Expr.if_else(test, yes, no)`.

## Joins: keys and `on =`

```r
regions[sales, on = "region"]
# or, with a key
setkey(regions, region)
regions[sales]
```

`X[Y]` keeps every row of `Y`, which is a left join of `Y` with `X`. `DataFrame.join` names both
sides and the kind of join, and needs no key:

```r
regions <- DataFrame(region = ["eu", "us"], manager = ["Ana", "Bo"])
DataFrame.join(sales, regions, on = "region", how = "left")
```

```text
   region units price revenue manager
   <char> <int> <num>   <num>  <char>
1:     eu     3   1.5     4.5     Ana
2:     us     5   2.0    10.0      Bo
3:     eu     2   3.0     6.0     Ana
4:   asia     7   0.5     3.5    <NA>
```

The left table's columns come first. Use `left_on` and `right_on` when the key names differ, and
`DataFrame.join_asof` for a rolling join (`roll = TRUE`).

## Ordering: `setorder`

```r
setorder(dt, region, -units)
```

`DataFrame.sort` returns the sorted table, with one `descending` flag per column:

```r
sales |> DataFrame.sort("region", "units", descending = c(FALSE, TRUE))
```

```text
   region units price revenue
   <char> <int> <num>   <num>
1:   asia     7   0.5     3.5
2:     eu     3   1.5     4.5
3:     eu     2   3.0     6.0
4:     us     5   2.0    10.0
```

## Windows, ranks, reshaping and dates

| data.table | DataFrame |
| --- | --- |
| `dt[, share := x / sum(x), by = g]` | `DataFrame.with_columns(df, share = Expr.col("x") / Expr.over(Expr.sum(Expr.col("x")), "g"))` |
| `dt[, r := frank(-x, ties.method = "first"), by = g]` | `DataFrame.with_columns(df, r = Expr.over(Expr.rank(Expr.col("x"), descending = TRUE), "g"))` |
| `dcast(dt, g ~ k, value.var = "x", fun.aggregate = sum)` | `DataFrame.pivot(df, on = "k", index = "g", values = "x", agg = "sum")` |
| `CJ`-style pairing, or `dt[, .(v = 1:n), by = .(id)]` | `DataFrame.join(df, DataFrame(v = 1:n), how = "cross")` |
| `dt[, due := day + 28L]` | `DataFrame.with_columns(df, due = Expr.col("day") + 28L)` |
| `dt[, days := as.integer(due - day)]` | `DataFrame.with_columns(df, days = Expr.col("due") - Expr.col("day"))` |

[Working with DataFrames](/reference/dataframes.md#windows-and-ranks) shows each with its output.

## Existing data.table code

data.table is a normal package under ry. Code written for it runs unchanged under `ry run`, on
data.table's own R and C code, and ry adds no special cases for it. A DataFrame replaces data.table
only where you choose it for new code; the rest of this page maps each idiom across.

## Finding the data.table code

Set the `data-table` lint to `"warn"` in `ry.toml` to have `ry check` and the editor mark each call
that creates a data.table (`data.table()`, `fread()`, `as.data.table()` and `setDT()`) with its
DataFrame equivalent:

```toml
[lint]
data-table = "warn"
```

## Rewriting automatically

The editor offers each rewrite in this guide as a code action, on the `data-table` lint's warning
and on any query, `:=`, `fread()` or `setorder()` it can translate, along with one action that
rewrites the whole file. From the command line, `ry migrate data-table` prints the rewrites as a
diff, and `--write` applies them:

```r
library(data.table)
sales <- fread("sales.csv")
totals <- sales[units > 2L, .(units = sum(units), n = .N), by = region]
sales[, revenue := units * price]
setorder(sales, region, -units)
```

```text
$ ry migrate data-table --write report.R
applied 4 rewrites in 1 file
```

```r
library(data.table)
sales <- DataFrame.read_csv("sales.csv")
totals <- sales |> DataFrame.filter(Expr.col("units") > 2L) |> DataFrame.group_by("region") |> GroupBy.agg(units = Expr.col("units") |> Expr.sum(), n = Expr.len())
sales <- sales |> DataFrame.with_columns(revenue = Expr.col("units") * Expr.col("price"))
sales <- sales |> DataFrame.sort("region", "units", descending = c(FALSE, TRUE))
```

The DataFrame verbs do not accept a data.table, so a rewritten query, `:=` or `setorder()` on a
table that is still a data.table converts it first, as in `as.DataFrame(sales) |> DataFrame.filter(...)`.
The whole-file rewrite leaves the conversion out for a name like `sales` above, whose every
assignment it rewrites into a DataFrame; a function parameter typed `data.table` keeps it.

The whole-file rewrite never hands a DataFrame to code that still expects a data.table. A
rewritten table may flow only into other rewritten code, into `$`, `[[` or a shape function such
as `nrow()` or `names()`, or out of a function as its return value. A query in that last place
runs on the DataFrame and converts the result back, so the function's callers still receive a
data.table:

```r
top <- function(sales: data.table) {
  return(sales[units > 2L])
}
# becomes
top <- function(sales: data.table) {
  return(data.table::as.data.table(as.DataFrame(sales) |> DataFrame.filter(Expr.col("units") > 2L)))
}
```

Any other form whose table would reach other code is left as it is, such as `fread()` whose result
goes to `saveRDS()`, a `data.table()` a function returns, or a table some later `:=` still updates
in place. The editor still offers each of those as a single code action.

Delete `library(data.table)` once nothing in the file needs it, and run `ry fmt` to break the long
chains over several lines.

A query is rewritten only when the checker types its table as a data.table or a DataFrame, so
`values[values > 2]` on a vector is left alone. Inside the brackets, a bare name is a column when
the checker knows the table's columns and the name is one of them. When the columns are not known,
a name is a column unless it is a variable in scope or a name the project defines, a base constant such as
`pi` or `letters`, or one of data.table's own symbols such as `.SD`. That rule matches data.table,
which also looks a name up among the columns first.

Anything without an exact equivalent is left as it is rather than rewritten approximately:

- a query on row numbers (`dt[1:2]`), a column read as a vector (`dt[, units]`), or a `j` that mixes
  per-row columns with aggregations;
- an aggregation other than `sum`, `mean`, `median`, `min`, `max`, `sd`, `var`, `first`, `last`
  and `.N`, such as `uniqueN()`;
- `:=` inside a function, with `by =`, or removing a column with `NULL`, because the rewrite assigns
  the result to the table's own name and a function's caller would no longer see the change;
- `dt[i, col := value]` unless the checker knows the columns: a new column takes `NA` in the other
  rows, and an existing one is rewritten only for a constant of the column's own type;
- a join after `setkey()`, since finding the key needs the order the statements run in, and a join
  on `on =` unless the checker knows both tables' columns and they share none but the keys, because
  data.table names a shared column `i.col` and `DataFrame.join` names it `col_right`;
- `fread()` of anything but a literal path ending in `.csv`, or with any argument other than the
  path and `stringsAsFactors`, and `data.table()` with `key`, `keep.rownames`, `check.names` or
  `stringsAsFactors`.

Two differences remain after a rewrite. The DataFrame aggregations skip missing values, so
`sum(units)` over a column with an `NA` gives a number instead of `NA`. `DataFrame.sort` places
missing values last, where `setorder()` places them first.
