data.table Exercises in R: 50 Real Practice Problems

Explanation: Inside [, the first argument is i and lives entirely in the data.table's column namespace, so you write mpg > 25 not mt$mpg > 25. Compared to base R's mt[mt$mpg > 25, ] this is shorter and avoids repeating the table name. data.table also evaluates i more efficiently when a key or index is set, which you'll explore later in Section 6.

Explanation: Writing column names inside .() (a shorthand for list()) tells data.table to return a new data.table with those columns. Without .(), mt[, car] returns a plain vector of car names because j is a single expression. The .() form is the idiomatic way to subset multiple columns while keeping data.table structure.

Explanation: When j is wrapped in .(), you can name the returned columns inline using name = expression syntax, mirroring dplyr::summarise. Drop the .() and you get a bare numeric value, which is sometimes what you want, but losing the column name makes downstream code harder to chain. Use .() whenever you'll bind, join or print the result.

Explanation: i and j are independent expressions, so you can stack a logical filter and a column projection in a single bracketed call without piping. The order is fixed: rows first, then columns. Compared to base R you avoid creating an intermediate subset and avoid repeating mt$, which is the main ergonomic win of data.table syntax.

Explanation: The by slot defines the grouping. data.table returns groups in the order they first appear in the source table. To get groups in sorted order without re-sorting afterwards, use keyby = cyl instead. keyby also sets a key on the result, which makes downstream joins and lookups fast.

Explanation: Each item inside .() is its own expression evaluated per group. .N is a special symbol holding the number of rows in the current group, so it works without referencing any column. Mixing summary functions and .N in one call is the standard "summary by group" pattern in data.table and avoids the multi-step group_by + summarise flow.

Explanation: Pass multiple grouping columns inside .() (or list()). The output has one row per unique combination. With keyby = .(cyl, am) instead, you'd get the same six rows but sorted by both keys, which often matters for plotting or for downstream merges that rely on a sort order.

Explanation: data.table overrides order() internally with a fast radix-sort version when used inside [. The minus sign reverses direction. Chaining [1:6] afterwards is the idiomatic "top N" pattern. For an in-place sort that also rearranges the underlying object (no copy), use setorder(mt, -mpg) instead, which is slightly faster on large tables.

Explanation: Bracket chaining is data.table's answer to the pipe: the first [] produces an intermediate data.table and the second filters it. Because each [] returns a fresh data.table, you can chain arbitrarily many. The newly named mean_mpg column is available immediately in the next bracket since it lives in the intermediate result.

Explanation: by accepts arbitrary expressions, not just column names. Naming them inside .() makes the output column explicit. This pattern saves you from creating a throwaway flag column before grouping. It's especially handy in interactive analysis when you want a quick "is X true vs not" cut without polluting the source table.

Explanation: The := operator modifies the data.table by reference: no new object is created and the assignment returns invisibly. That makes it extremely memory-efficient for large tables compared to mt$kpl <- mt$mpg * 0.4251, which would copy the whole frame. To see the modified table after a :=, print it explicitly or wrap the call in [] on the right side.

Explanation: A logical expression on a vector evaluates element-wise, so mpg < 18 produces a logical vector of the right length automatically. No ifelse wrapper is needed when the result is already TRUE/FALSE. If you wanted "Low"/"Normal" labels instead, switch to fifelse(mpg < 18, "Low", "Normal"), the type-stable data.table version of ifelse.

Explanation: The functional form ` :=(name1 = expr1, name2 = expr2) is the cleanest way to add multiple columns in one pass. It edits the table once, scanning columns just once, which is faster than calling :=` twice in a row. Use this whenever you have several derived columns to compute together.

Explanation: Setting a column to NULL via := deletes it by reference, in constant time, regardless of table size. This is the data.table equivalent of df$qsec <- NULL but it does not copy the whole table. To drop multiple columns at once use mt[, c("col1", "col2") := NULL].

Explanation: When := is combined with an i filter, the assignment only touches the matched rows, leaving all others untouched. This is the canonical "patch a subset" idiom: cheap, in place, no copy. Equivalent base R code, mt$gear[mt$gear > 4] <- 4, works but is slower on wide tables because it triggers a copy of the whole frame in some R versions.

Explanation: frank is data.table's fast equivalent of base rank. Combined with := and by, it produces a within-group rank in one pass without splitting and recombining. Negating hp reverses the order so higher horsepower gets rank 1. The ties.method = "min" choice gives the smallest of the tied ranks to all tied values, a common choice for leaderboards.

Explanation: setnames renames columns by reference, never copying the table. The old/new vector form lets you rename many at once and is order-sensitive: position 1 in old maps to position 1 in new. To rename by position, pass integer indices in old. After this exercise, remember the table is permanently changed for downstream exercises in your session.

Explanation: The (num_cols) syntax on the left of := unquotes the variable so the names come from the character vector, not literally. .SD is the subset of data, restricted to .SDcols, and lapply iterates a function over each column. This idiom scales: change .SDcols and the function, and the same shape works for hundreds of columns.

Explanation: .N is the row count of the current i-filtered, by-grouped frame. It is an integer scalar inside each group, so wrapping it with .() gives you a named output column. This is the data.table equivalent of dplyr::count(cyl) and the most-used idiom in real-world data.table code.

Explanation: With no by, .N refers to the whole table (after any i filter). This is the convenient way to express a row count plus other summary stats in the same call. Note that mt[, .N] (without the .() wrapper) returns a bare integer instead of a single-row data.table, which is fine for printing but harder to bind into a result table.

Explanation: .SD is "Subset of Data" and represents the per-group slice of the table without the grouping columns. Returning head(.SD, 1) per group is the data.table equivalent of dplyr::slice_head(n = 1). Because .SD includes all non-group columns by default, this is also useful for sampling, lagging, or any per-group operation that needs the full row, not just an aggregate.

Explanation: .SDcols controls which columns end up in .SD. Combined with lapply(.SD, FUN) it's the standard pattern for "apply this function to many columns at once". Pass a character vector, an integer range, or a function predicate like is.numeric to choose columns dynamically. The output has one row per group with one summary column per .SDcols entry.

Explanation: When i sorts globally and by groups afterwards, data.table preserves the sorted order within each group. Calling head(.SD, 2) per group then takes the top two heaviest rows. This "sort then per-group head" trick is the cleanest top-N idiom in data.table and is much faster than splitting by group and applying head separately.

Explanation: .GRP is an integer that uniquely tags each group, assigned in the order the groups are encountered. It is useful for tracing which rows belong together after a complex pipeline, for joining a per-group lookup back, or as a compact factor replacement. Note: the numbering follows the order groups appear in the source, not the sorted order; use keyby to align the two.

Explanation: .I is the vector of row indices in the original table for the current group. which.max(hp) returns the position of the maximum inside the group, and .I[] translates that group-relative position into the original row number. This is the standard idiom when you need the index back into the source frame, for example to update those rows or to extract them by mt[idx_vector].

Explanation: .BY is a named list of the grouping column values inside the current group, even when the group is defined by an anonymous expression. It's most useful inside custom functions called from j, where the function body doesn't see the surrounding by argument. Use it to build descriptive labels, write group-specific log lines, or branch logic based on which group is currently being processed.

Explanation: Reading customers[orders, on = "cust_id"] aloud as "for every row of orders, look up the matching row in customers" gives you the mental model: the right-hand table is the driver, the bracketed table is the lookup. Rows from orders with no match get NA for the lookup columns. This is data.table's idiomatic left join and works without setting a key.

Explanation: The nomatch = NULL flag tells data.table to discard rows where the lookup fails, producing an inner join. The default nomatch = NA keeps unmatched rows with NAs (a left join). Memorising this single flag is enough to flip between left and inner joins without changing the rest of the syntax.

Explanation: merge with all = TRUE returns every row from both tables, filling unmatched cells with NA. The bracket syntax X[Y] cannot express a full outer join directly, so merge is the right tool here. all.x = TRUE and all.y = TRUE give left and right outer joins respectively, completing the four standard join variants in a single function.

Explanation: Prefixing the right-hand table with ! flips the semantics from "keep matches" to "keep non-matches". This anti-join pattern reads naturally: "give me orders not in customers". It's the idiomatic way to find orphans, missing references, or recently churned IDs without writing an explicit is.na filter afterwards.

Explanation: Inside an update-on-join, the right-hand table's columns are referenced with the i. prefix to disambiguate from the left-hand columns. i.city is the city column from customers. Update-on-join writes the new column directly into the left-hand table by reference, never creating a copy, which is the most memory-efficient way to enrich a large fact table with dimensional attributes.

Explanation: Non-equi joins use inequality operators inside on = .(). They are the data.table answer to "lookup by range", which would otherwise require a cross join plus filter. The x. prefix disambiguates the left-hand price column when the same name appears in the join condition. Non-equi joins also support <, > and combinations, and are dramatically faster than the SQL equivalent on large tables.

Explanation: roll = TRUE carries the last observed value forward to fill gaps in the join. It's designed for time-series and reference data: as-of pricing, currency rates, last-known location. The keyed columns become the join columns automatically, so setkey on date for both tables is mandatory. Use roll = -Inf to carry the next value backward instead.

Explanation: Pass a character vector to on = to join on multiple columns. The match must align on every column, otherwise the row is unmatched. This is the workhorse pattern when fact tables and dimensions share a composite key. For the most common case where both tables already have an identical key set via setkey, you can omit on = entirely and data.table will use the shared key automatically.

Explanation: melt converts wide to long. Columns listed in id.vars are preserved as identifiers; every other column becomes a stacked variable/value pair. The result has rows equal to nrow(input) * (ncol - id_count). This is the input shape expected by ggplot2's facetting and most modelling pipelines that want one observation per row.

Explanation: dcast reverses melt. The formula has id columns on the left and the column that holds variable names on the right. When multiple rows share an id-variable pair, fun.aggregate collapses them; omitting it produces an error when duplicates exist. value.var names the column whose values fill the resulting cells.

Explanation: Passing measure.vars as a list of vectors creates one melted group per list element, producing paired variable1/value1, variable2/value2 columns. The two groups are aligned by position within each vector. This is the data.table-only shortcut for "melt multiple variable families at once" without running melt twice and joining the results.

Explanation: Setting fun.aggregate = length turns dcast into a fast cross-tabulation, faster than table() because it runs on the underlying data.table engine. Empty combinations become 0 rather than NA because the missing-cell fill default is 0 when fun.aggregate returns counts. Use fill = NA to override.

Explanation: Passing a character vector to value.var produces one set of wide columns per value variable, prefixed with the variable name. This is the easiest way to lay several measurement series next to each other for visual inspection without running dcast repeatedly. na.rm = TRUE is forwarded to mean so missing readings are ignored per cell.

Explanation: melt then dcast is data.table's pivot pipeline. Round-tripping is a useful sanity check that no information was lost: the recovered table has the same rows and column types as the original. The pattern is also how you reshape via an intermediate long form when neither pure melt nor pure dcast is enough on its own.

Explanation: setkey sorts the table by reference and marks the column as the key, enabling O(log n) lookups via the mt[value] shorthand. Without a key, the same lookup is O(n) and you'd have to write mt[car == "Honda Civic"]. For batch lookups, pass a vector: mt[c("Honda Civic", "Lotus Europa")]. Keys are persistent: once set, they survive subset operations.

Explanation: setorder reorders the rows of the table by reference, no copy, no key set, no return value. It's faster than mt[order(-hp)] because the latter materialises a copy. The minus sign in front of a column reverses sort direction. For sorting plus key setting in one call, use setkey which always sorts ascending; for sorting without keying, prefer setorder.

Explanation: A secondary index gives you the speed of binary search on a non-key column without reordering the table. data.table also auto-creates indexes when you filter with == on a column repeatedly: this is called "auto-indexing". You can inspect indexes with indices(mt) and remove them with setindex(mt, NULL). Multiple indexes can coexist on different columns.

Explanation: fcase is data.table's vectorised, type-stable multi-branch conditional. It evaluates conditions top to bottom and returns the value for the first match. Unlike dplyr::case_when, it never silently coerces types, raising an error if branches mix incompatible types. Pass default = "other" to handle the catch-all rather than writing an explicit final TRUE row.

Explanation: fread and fwrite are typically 10x to 100x faster than base read.csv/write.csv, thanks to a multi-threaded C implementation. fread also auto-detects types, column separators and headers. For very large files, pass nrows to read a subset, select to keep only a few columns, or cmd to pipe through gzip/grep before reading.

Explanation: setDT upgrades a data.frame to a data.table in place, with zero copy. as.data.table does the same job but returns a new object, doubling memory transiently. keep.rownames = "car" preserves rownames as a column rather than dropping them, useful for mtcars-like datasets where the rowname is informative.

Explanation: Because := mutates by reference, two variable names pointing at the same data.table will both see the change. copy() performs a deep copy so the new object is independent. This is the single most common source of subtle bugs in data.table code. The general rule: if a function accepts a data.table and mutates it with := or set*, document it loudly or call copy() defensively at the top.

Explanation: A single ` :=(...) call is faster than three back-to-back := calls because the data.table dispatcher and column-name lookup happens once. The trade-off is readability: too many simultaneous updates start to obscure intent. Three to five is a common sweet spot. fmatch is data.table's fast match` used here to translate a character band into a numeric index.

Explanation: uniqueN is data.table's fast distinct-count, equivalent to length(unique(x)) but implemented in C and several times faster. It also accepts a data.table argument, in which case it counts distinct rows across all columns. Pair it with by for "cardinality per group" reports, a frequent metric in audits, churn dashboards and feature-engineering.

Explanation: setcolorder moves the listed columns to the front, in the order given, and leaves the rest in their original order. It mutates by reference, so no copy is made even on a very wide table. Use it to put key columns at the start of a printed table for readability, or to align column order across multiple tables before a rbindlist.