Tabular Data Operations

The same data-wrangling tasks side by side in R's data.table, Python's polars, and DuckDB SQL

Three of the fastest tabular engines available today — data.table (R), polars (Python), and DuckDB (SQL) — solve the same problems with different vocabulary. This guide puts them side by side, one operation at a time, so you can carry a mental model from one to the next or pick whichever console you're in.

All three run in the browser in rtemislive, each with its own console — a data.table R console, a polars Python console, and a DuckDB SQL console. Every snippet below runs unchanged there. Pick a language tab once and the whole page follows your choice.

Examples use the penguins dataset, with columns species, island, bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g, sex, and year.

Concept map

Operation	data.table (R)	polars (Python)	DuckDB SQL
Read CSV	`fread()`	`pl.read_csv()`	`read_csv_auto()`
Inspect	`str()`, `.N`	`.schema`, `.glimpse()`	`DESCRIBE`, `SUMMARIZE`
Rename	`setnames()`	`.rename()`	`... AS`, `RENAME COLUMN`
Retype	`:=` + `as.*()`	`.cast()`	`CAST` / `::`
Select	`DT[, .(...)]`	`.select()`	`SELECT`
Drop columns	`:= NULL`	`.drop()`	`... EXCLUDE`, `DROP COLUMN`
Filter	`DT[cond]`	`.filter()`	`WHERE`
Sort	`DT[order(...)]`	`.sort()`	`ORDER BY`
Derive	`:=`	`.with_columns()`	`SELECT ... AS`
Missing	`na.omit()`	`.drop_nulls()`	`IS NULL`, `coalesce()`
Distinct	`uniqueN()`	`.n_unique()`	`count(DISTINCT)`
Aggregate	`DT[, ..., by=]`	`.group_by().agg()`	`GROUP BY`
Window	`frank()`, `by=`	`.over()`	`OVER (...)`
Join	`merge()` / `DT[DT]`	`.join()`	`JOIN ... ON`
Wide → long	`melt()`	`.unpivot()`	`UNPIVOT`
Long → wide	`dcast()`	`.pivot()`	`PIVOT`
Write file	`fwrite()`	`.write_csv()`	`COPY ... TO`

Reading data

All three read CSV (and Parquet) directly from a path or URL. DuckDB can query a file in place without loading it into a table first.

library(data.table)

# CSV from a path or URL; fread auto-detects delimiter, types, and header
penguins <- fread("penguins.csv")

# Common knobs
penguins <- fread("penguins.csv", na.strings = c("", "NA"))

Inspecting schema and types

Before transforming anything, look at column names, types, and a quick statistical summary.

str(penguins)                       # structure: columns, types, sample values
names(penguins)                     # column names
sapply(penguins, class)             # type of each column
penguins[, .N]                      # row count
summary(penguins)                   # per-column summary stats

Renaming columns

Column names are part of your data's contract: clear, consistent names make every later step easier to read and less error-prone. Renaming changes the label only — the values stay put.

# Rename in place (modifies penguins directly)
setnames(penguins, "bill_length_mm", "bill_len")

# Several at once
setnames(penguins,
         old = c("bill_length_mm", "bill_depth_mm"),
         new = c("bill_len", "bill_dep"))

Setting and converting types

Every column has a type that decides what you can do with it — arithmetic on numbers, categories on factors, calendar math on dates. Readers often guess types from the file and sometimes guess wrong, so setting them deliberately is a common early step.

# := converts in place
penguins[, year := as.integer(year)]
penguins[, species := as.factor(species)]
penguins[, body_mass_g := as.numeric(body_mass_g)]

# Several columns at once
penguins[, `:=`(year = as.integer(year),
                sex  = as.factor(sex))]

Selecting columns

Selecting narrows a table to the columns you care about while keeping every row — the way you focus a wide table down to the few variables a given step actually needs.

penguins[, .(species, body_mass_g)]      # keep these two
penguins[, !c("year", "sex")]            # drop these
penguins[, .SD, .SDcols = patterns("_mm$")]  # columns matching a pattern

Removing columns

Dropping discards columns you no longer need — the complement of selecting, and the natural cleanup step after deriving intermediate columns or before writing a table out. Selecting keeps a named set; dropping removes a named set and keeps the rest.

# Drop in place by assigning NULL (modifies penguins directly)
penguins[, c("year", "sex") := NULL]

# A single column
penguins[, bill_depth_mm := NULL]

# Non-destructive: return a copy without these columns
penguins[, !c("year", "sex")]

Filtering rows

Filtering keeps only the rows that satisfy a condition and leaves the columns untouched — the row-wise counterpart to selecting.

penguins[species == "Gentoo"]
penguins[body_mass_g > 4000 & sex == "male"]
penguins[island %in% c("Biscoe", "Dream")]
penguins[is.na(sex)]                              # missing values
penguins[bill_length_mm %between% c(40, 50)]

Sorting rows

Sorting reorders rows by one or more columns without changing their contents — the basis for inspection, ranking, and "top N" views.

penguins[order(-body_mass_g)]                  # descending
penguins[order(species, -body_mass_g)]         # by group, then mass desc
penguins[order(-body_mass_g)][1:10]            # top 10

Creating and deriving columns

Deriving adds new columns computed from existing ones — a unit conversion, a ratio, a category — while keeping every original row. It's how raw measurements become the features you analyze.

penguins[, mass_kg := body_mass_g / 1000]

# Several at once
penguins[, `:=`(mass_kg   = body_mass_g / 1000,
                bill_ratio = bill_length_mm / bill_depth_mm)]

# Conditional column
penguins[, size := fifelse(body_mass_g > 4000, "large", "small")]

Handling missing values

Real data has gaps. The common moves are the same everywhere: count them, drop them, or fill them in.

# Count missing values per column
penguins[, lapply(.SD, function(x) sum(is.na(x)))]

# Drop rows with any NA (optionally only in chosen columns)
na.omit(penguins)
na.omit(penguins, cols = c("body_mass_g", "sex"))

# Replace NA with a default
penguins[is.na(sex), sex := "unknown"]

# Mean imputation
m <- penguins[, mean(body_mass_g, na.rm = TRUE)]
penguins[is.na(body_mass_g), body_mass_g := m]

Counting and distinct values

Quick sanity checks before (or instead of) a full group-by: how many rows, how many distinct values, and how often each one occurs.

penguins[, .N]                                  # total rows
penguins[, uniqueN(species)]                    # number of distinct species
unique(penguins$species)                        # the distinct values
unique(penguins, by = c("species", "island"))   # distinct row combinations

# Frequency table, most common first
penguins[, .N, by = species][order(-N)]

Aggregating (group-by)

Collapse rows into per-group summaries. Each engine computes a count and a group mean here.

# Whole-table summary
penguins[, .(n = .N, mean_mass = mean(body_mass_g, na.rm = TRUE))]

# Per group
penguins[, .(n = .N, mean_mass = mean(body_mass_g, na.rm = TRUE)),
         by = species]

# Multiple grouping columns
penguins[, .(mean_mass = mean(body_mass_g, na.rm = TRUE)),
         by = .(species, island)]

Window functions

Windows compute across a group of rows without collapsing them — so you keep every row but add ranks, group-relative values, or running totals alongside it.

# Rank within each species, heaviest first
penguins[, mass_rank := frank(-body_mass_g), by = species]

# Each penguin's mass relative to its species mean
penguins[, mass_vs_mean := body_mass_g - mean(body_mass_g, na.rm = TRUE),
         by = species]

# Row number within group, lightest to heaviest
penguins[order(body_mass_g), rn := seq_len(.N), by = species]

Joining tables

These examples join against a second table, islands, with columns island and region.

# merge() is the most readable form
merge(penguins, islands, by = "island")                 # inner join
merge(penguins, islands, by = "island", all.x = TRUE)   # left join

# Native data.table syntax (fast; keyed join)
penguins[islands, on = "island"]                        # right/inner-style
penguins[islands, on = "island", nomatch = NULL]        # inner only

Reshaping: wide ⇄ long

penguins is wide — each measurement is its own column. Its long form stacks the four measurement columns into measurement/value rows. The two are exact inverses, so you can go wide → long → wide and land back where you started.

Two prep steps make the round-trip clean: add a row id so each original row stays identifiable, and drop rows with missing measurements first. The snippets below assume an id column exists (penguins[, id := .I] in data.table, penguins.with_row_index("id") in polars, row_number() OVER () in DuckDB).

Wide → long:

long <- melt(
  penguins,
  id.vars       = c("id", "species", "island"),
  measure.vars  = c("bill_length_mm", "bill_depth_mm",
                    "flipper_length_mm", "body_mass_g"),
  variable.name = "measurement",
  value.name    = "value"
)

Long → wide (back to the original shape):

wide <- dcast(
  long,
  id + species + island ~ measurement,
  value.var = "value"
)

Writing to file

The mirror of reading: persist the result so the next step — a colleague, a script, a dashboard — can pick it up. CSV is universal and human-readable; Parquet is compact, columnar, and preserves column types exactly, so it round-trips faster and without re-inferring schema. Reach for CSV when a human or a foreign tool will open it, Parquet for everything else.

# CSV — fast, parallel writer
fwrite(penguins, "penguins_clean.csv")

# Common knobs: gzip-compress, or use a different delimiter
fwrite(penguins, "penguins_clean.tsv", sep = "\t")
fwrite(penguins, "penguins_clean.csv.gz")

# Parquet (requires the arrow package)
arrow::write_parquet(penguins, "penguins_clean.parquet")

A typical flow across any of the three: read the file → inspect schema → retype and rename → filter/select down → derive new columns → aggregate or join → reshape for output. The verbs differ; the pipeline is the same.

Tabular Data Operations

On this page