Data Frames
|
This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production. The reference material consulted while preparing these pages is listed in the section bibliography. |
Checked against R 4.6.1 (2026-06-24) on 2026-10-20; R 4.6.2 is scheduled for 2026-10-29. A data frame is R’s name for a
table: a list of columns of equal length, each column a vector of one type, with one row per observation. This page shows
how to build one (and what the stringsAsFactors = FALSE default of R 4.0.0 changed), how to look inside it, how row names
work and fail, how to select, filter, add and remove columns and rows, how to sort and join, how to summarise by group
with the split-apply-combine pattern and its base shortcuts (aggregate(), tapply(), by(), ave(), rowsum()),
how to reshape between long and wide, how to find duplicates, and how to print. Every example uses base R and the
packages that ship with it (base, utils, stats and tools), except one short example that runs tibble, checked at
version 3.3.1. The add-on package data.table, checked at version 1.18.6.1, appears in prose only; no data.table code is
run. Both are described in a section near the end and have pages of their own:
The tidyverse and dplyr,
tidyr, purrr and stringr and
data.table.
Every example below is a complete script that was run with Rscript on R 4.6.1 with warnings printed as they happen
(options(warn = 1)), and the lines starting with #> are what it printed. The scripts also pass the default lintr
linters. Errors and warnings that a script shows on purpose are caught with tryCatch() and printed as their
conditionMessage(), so the text is exactly what R says. The examples use the library-shelf scenario of the section: the
data frames books and loans, the constants shelf_today and loan_days and the book class come from the
scenario.R file described on the section index, and the scripts that use them
start with source("scenario.R"), which reads books.csv with read.csv("books.csv") and loans.csv with
read.csv("loans.csv", colClasses = "character"). books has 16 rows and the columns title, author, isbn, year,
genre and pages; loans has 60 rows and the columns loan_id, isbn, member, loan_date and return_date. Both
are deliberately untidy (blanks, two spellings of one author, an empty ISBN, NA years and page counts, an impossible
date, a loan returned before it was made), because most of the work with data frames is cleaning them. This page is the
data-frame chapter of the vectors-and-data-structures group: Lists covers the
type underneath, Subsetting and Indexing the general rules of
[, [[ and $, Factors the categorical columns,
Missing and Special Values the NA that runs through the
examples, and Attributes and Names the attributes a data frame
is built from.
What a data frame is
The two manuals say the same thing in different words. An Introduction to R says that a data frame is a list with class
"data.frame"; the components must be vectors, factors, numeric matrices, lists or other data frames; vector components
must all have the same length and matrix components the same number of rows
(An Introduction to R: Data frames). The
language definition describes a data frame as the structure that most closely mimics a SAS or SPSS data set, a table
of cases by variables: a list of vectors, factors and matrices of equal length, with a names attribute that labels the
variables and a row.names attribute that labels the cases
(The R Language Definition: Data frame objects).
The language definition adds a warning that a list column of differing element lengths, which would give a ragged array,
is not generally handled correctly by the functions that work on data frames.
Three consequences shape everything else on this page.
-
A data frame is a list, so
length(df)is the number of columns,df[[1]]is the first column, andlapply(),Filter()andvapply()walk the columns. -
A data frame looks like a matrix, so
dim(),nrow(),ncol()and the two-index formdf[i, j]work, but each column keeps its own type. Operations that need a single type (apply(),t(),as.matrix()) convert the whole table first, which is where many surprises come from. -
The row names are part of the structure. Every data frame has them, they are unique, and subsetting carries them along.
source("scenario.R")
# A data frame is a list of equal-length columns with class "data.frame".
class(books)
#> [1] "data.frame"
typeof(books)
#> [1] "list"
is.list(books)
#> [1] TRUE
is.matrix(books)
#> [1] FALSE
length(books)
#> [1] 6
vapply(books, length, integer(1))
#> title author isbn year genre pages
#> 16 16 16 16 16 16
# The attributes are exactly the three the language definition names.
names(attributes(books))
#> [1] "names" "class" "row.names"
attributes(books)$names
#> [1] "title" "author" "isbn" "year" "genre" "pages"
# unclass() removes the class: what is left is a plain named list. The
# row names are an attribute.
small <- books[1:3, c("title", "year")]
unclass(small)
#> $title
#> [1] "The Dispossessed" "Pride and Prejudice" " the quiet streets "
#>
#> $year
#> [1] 1974 1813 2019
#>
#> attr(,"row.names")
#> [1] 1 2 3
# dput() shows the structure, with the compact form of automatic row names.
dput(small)
#> structure(list(title = c("The Dispossessed", "Pride and Prejudice",
#> " the quiet streets "), year = c(1974L, 1813L, 2019L)), row.names = c(NA,
#> 3L), class = "data.frame")
.row_names_info(small)
#> [1] 3
.row_names_info(small, type = 0L)
#> [1] NA 3
# Because it is a list, list tools work on the columns.
books[[2L]][1:2]
#> [1] "Ursula K. Le Guin" "Jane Austen"
names(Filter(is.numeric, books))
#> [1] "year" "pages"The three attributes in the output are names, class and row.names. unclass() strips the class, and what remains is
an ordinary named list with one attribute left over, the row names. dput() shows the representation that R keeps
internally. The row names of small are written c(NA, 3L): when the row names are the integers 1:n with n above 2, R stores
them in a compact form, a two-element integer vector that starts with NA, instead of a vector of length n. The help page for row.names says this form is visible only to C code or by deparsing, never
through row.names() or attr(x, "row.names"), which expand it. A negative second element, as in
the -16 of books, marks the row names as automatic (added by R because the creator gave none); the positive 3 of
small is the same sequence, kept by the subset books[1:3, ], but not marked automatic. The function .row_names_info() reports
the number of rows with a negative sign for automatic names (type = 1, the default) or the raw stored vector
(type = 0). It is documented among the internal objects of the base package, so it is shown here to explain the
structure and is not something a script should depend on.
Source: R documentation: row.names
Building a data frame
data.frame()
data.frame() is the constructor. Each argument is a column; a named argument gives the column its name; vectors of length
one are recycled to the number of rows. Its signature is data.frame(..., row.names = NULL, check.rows = FALSE,
check.names = TRUE, fix.empty.names = TRUE, stringsAsFactors = FALSE)
(R documentation: data.frame). It converts every argument
with as.data.frame(), so a matrix, a list or another data frame passed as an argument contributes one column per column,
element or variable.
# data.frame() builds a data frame from vectors; scalars are recycled.
members <- data.frame(
member = c("Ana Ruiz", "Ben Okafor", "Dev Patel"),
joined = as.Date(c("2024-03-01", "2023-11-15", "2025-01-10")),
active = c(TRUE, TRUE, FALSE),
tier = "standard"
)
members
#> member joined active tier
#> 1 Ana Ruiz 2024-03-01 TRUE standard
#> 2 Ben Okafor 2023-11-15 TRUE standard
#> 3 Dev Patel 2025-01-10 FALSE standard
str(members)
#> 'data.frame': 3 obs. of 4 variables:
#> $ member: chr "Ana Ruiz" "Ben Okafor" "Dev Patel"
#> $ joined: Date, format: "2024-03-01" "2023-11-15" ...
#> $ active: logi TRUE TRUE FALSE
#> $ tier : chr "standard" "standard" "standard"
# Since R 4.0.0 character vectors stay character; ask for factors explicitly.
class(members$member)
#> [1] "character"
as_factors <- data.frame(tier = c("standard", "gold"), stringsAsFactors = TRUE)
class(as_factors$tier)
#> [1] "factor"
levels(as_factors$tier)
#> [1] "gold" "standard"
# An empty data frame with typed columns, and its dimensions.
empty <- data.frame(title = character(), year = integer())
str(empty)
#> 'data.frame': 0 obs. of 2 variables:
#> $ title: chr
#> $ year : int
dim(empty)
#> [1] 0 2
dim(data.frame())
#> [1] 0 0Source: R documentation: data.frame
The tier column of members shows the recycling of a length-one value. str() is the quickest way to see the column
types; the Date column prints compactly, and the character columns are character vectors, not factors.
The stringsAsFactors default
Before R 4.0.0 data.frame() and read.table() (and so read.csv()) converted every character vector to a factor by
default, and a large part of the older material on the web still works around it with stringsAsFactors = FALSE. The NEWS
entry for R 4.0.0 says that R now uses a stringsAsFactors = FALSE default and so by default "no longer converts strings
to factors in calls to data.frame() and read.table()" (R NEWS:
Changes in R 4.0.0). The help page records the same change: the factory-fresh default had been TRUE and became FALSE
for R 4.0.0. The argument still exists, so stringsAsFactors = TRUE asks for factors explicitly, as the last lines of the
script above do, and factor() or as.factor() converts a column later. The consequence for code is that levels() of
a text column is now NULL, table() of it still works, and a column that really is categorical, with a fixed set of
allowed values, must be made a factor on purpose (see Factors).
Source: R NEWS: Changes in R 4.0.0
Recycling and the "differing number of rows" error
data.frame() recycles a shorter column only when the longer length is a whole multiple of it. The help page says that
atomic vectors, factors and character vectors protected by I() are recycled a whole number of times if necessary,
including as elements of list arguments. Anything else is an error.
# Columns are recycled only when the longer length is a multiple of the shorter.
data.frame(copy = 1:6, shelf = c("A", "B", "C"))
#> copy shelf
#> 1 1 A
#> 2 2 B
#> 3 3 C
#> 4 4 A
#> 5 5 B
#> 6 6 C
# Otherwise data.frame() stops, with this message.
tryCatch(
data.frame(title = c("Ten Winters", "Loud Roofs", "Paper Moons"), year = 1:2),
error = conditionMessage
)
#> [1] "arguments imply differing number of rows: 3, 2"
# A list column holds one vector per row. Assign it with $<-, or wrap it in
# I() inside data.frame() so that the list is not split into columns.
tags <- list(c("new", "popular"), "reissue")
with_lists <- data.frame(id = 1:2, tags = I(tags))
with_lists
#> id tags
#> 1 1 new, popular
#> 2 2 reissue
str(with_lists)
#> 'data.frame': 2 obs. of 2 variables:
#> $ id : int 1 2
#> $ tags:List of 2
#> ..$ : chr "new" "popular"
#> ..$ : chr "reissue"
#> ..- attr(*, "class")= chr "AsIs"
vapply(with_lists$tags, length, integer(1))
#> [1] 2 1Source: R documentation: data.frame
The message is arguments imply differing number of rows: 3, 2, listing the distinct row counts it met, in the order
found. A list column, a column whose cells are themselves vectors, is built with I() in data.frame() or by assigning
a list with $<-. The str() output shows it as a list of two elements. List columns are legitimate but most functions in
this page treat the cell as one opaque object, and the language definition warns that ragged columns are not handled
correctly everywhere.
Column names: check.names and the X prefix
check.names = TRUE, the default, passes the column names through make.names() so that they are syntactically valid and
unique: a space becomes a dot, a name that starts with a digit gets an X prefix, a reserved word such as if gets a
trailing dot, and a duplicate gets .1, .2, and so on. The help page describes this as checking that the names are
syntactically valid variable names and not duplicated, adjusting them with make.names where necessary.
read.csv() has the same argument and the same rule, which is the source of the X2025.total style of name that appears
when a CSV header starts with a digit or contains a space.
# data.frame() turns column names into syntactic names (check.names = TRUE).
odd <- data.frame(
`first name` = "Ana", `2nd loan` = 2L, `if` = TRUE, dup = 1, dup = 2
)
names(odd)
#> [1] "first.name" "X2nd.loan" "if." "dup" "dup.1"
# check.names = FALSE keeps the names; they then need backquotes to use.
raw <- data.frame(`first name` = "Ana", `2nd loan` = 2L, check.names = FALSE)
names(raw)
#> [1] "first name" "2nd loan"
raw$`first name`
#> [1] "Ana"
# make.names() is the function behind it; unique = TRUE adds .1, .2.
make.names(c("first name", "2nd loan", "if", "dup", "dup", ""), unique = TRUE)
#> [1] "first.name" "X2nd.loan" "if." "dup" "dup.1"
#> [6] "X"
# A name already syntactic is kept; read.csv() applies the same rule, which
# is where the X prefix comes from.
csv_text <- "id,2025 total,total
1,10,20"
read.csv(text = csv_text)
#> id X2025.total total
#> 1 1 10 20
names(read.csv(text = csv_text, check.names = FALSE))
#> [1] "id" "2025 total" "total"Source: R documentation: make.names
With check.names = FALSE the original names are kept, and then they need backquotes whenever they are used in code, as
the last line of the script shows. The more robust habit is to rename the columns once, at the top of the script, to
snake case names, and use those names everywhere.
read.csv(), as.data.frame() and list2DF()
read.csv("books.csv") is the usual way to get a data frame from a file; the details of delimiters, na.strings,
colClasses, encodings and large files belong to
Reading and Writing Text Data. as.data.frame() converts
other objects: a matrix becomes a data frame with columns V1, V2, and so on unless it has column names, a list
becomes one column per element (after the same recycling rule), and a table becomes the long form with a Freq column.
list2DF(), added in R 4.0.0, is the minimal constructor: it takes a list of equal-length columns and builds the data
frame directly, without name checking, recycling or conversion of the columns, so it is the fast and exact way to turn a
list you already trust into a data frame. Its nrow argument gives the row count of a data frame without columns.
source("scenario.R")
# read.csv() returns a data frame: text columns are character and whole
# numbers are integer. scenario.R reads the same file.
books_again <- read.csv("books.csv")
identical(books_again, books)
#> [1] TRUE
class(books_again$year)
#> [1] "integer"
class(books_again$genre)
#> [1] "character"
# colClasses = "character" keeps every field as text, dates included.
loans_raw <- read.csv("loans.csv", colClasses = "character")
str(loans_raw)
#> 'data.frame': 60 obs. of 5 variables:
#> $ loan_id : chr "L001" "L002" "L003" "L004" ...
#> $ isbn : chr "9780141439518" "9780306406157" "9780002000291" "9780005000571" ...
#> $ member : chr "Hugo Silva" "Ben Okafor" "Dev Patel" "Eva Lindqvist" ...
#> $ loan_date : chr "2025-01-14" "2025-01-16" "2025-01-21" "2025-01-26" ...
#> $ return_date: chr "2025-02-04" "2025-01-26" "2025-01-22" "2025-02-21" ...
loans_raw$loan_date <- as.Date(loans_raw$loan_date, format = "%Y-%m-%d")
class(loans_raw$loan_date)
#> [1] "Date"
# stringsAsFactors = TRUE asks for factors.
read_factor <- read.csv("books.csv", stringsAsFactors = TRUE)
levels(read_factor$genre)
#> [1] "Classic" "fiction" "Fiction" "Fiction "
#> [5] "Mystery" "Non-fiction" "Poetry" "science fiction"
#> [9] "Science fiction"
# as.data.frame() converts matrices, lists and tables.
as.data.frame(matrix(1:6, nrow = 2))
#> V1 V2 V3
#> 1 1 3 5
#> 2 2 4 6
as.data.frame(matrix(1:6, nrow = 2, dimnames = list(NULL, c("a", "b", "c"))))
#> a b c
#> 1 1 3 5
#> 2 2 4 6
as.data.frame(list(title = c("A", "B"), year = c(2000L, 2001L)))
#> title year
#> 1 A 2000
#> 2 B 2001
as.data.frame(table(genre = tolower(trimws(books$genre))))
#> genre Freq
#> 1 classic 2
#> 2 fiction 4
#> 3 mystery 2
#> 4 non-fiction 3
#> 5 poetry 2
#> 6 science fiction 3
# list2DF() is the minimal constructor: no name checking, no recycling.
list2DF(list(title = c("A", "B"), `page count` = c(100L, 200L)))
#> title page count
#> 1 A 100
#> 2 B 200
tryCatch(list2DF(list(a = 1:3, b = 1:2)), error = conditionMessage)
#> [1] "all variables should have the same length"
list2DF(list(), nrow = 2L)
#> data frame with 0 columns and 2 rowsSource: R documentation: list2DF
The first block shows that books from the scenario is exactly what read.csv("books.csv") returns, and that
colClasses = "character" keeps every field of loans.csv as text, so the two date columns can be converted by hand
with as.Date(format = "%Y-%m-%d"). The impossible date 2025-02-30 becomes NA in that conversion; see
Dates and Times. The error from list2DF() is all variables should
have the same length, because, unlike data.frame(), it never recycles.
| Constructor | Use it when | Checks and fixes names | Recycles |
|---|---|---|---|
|
you have vectors and want a data frame now |
yes ( |
whole multiples only |
|
the data is in a text file |
yes ( |
not applicable |
|
you have a matrix, list, table or another object |
depends on the method |
whole multiples only |
|
you have a list of equal-length columns you trust |
no |
no |
Source: R documentation: as.data.frame
Looking at a data frame
Five functions answer most questions about a table. str() prints one line per column with its type and first values;
summary() prints per-column statistics; head() and tail() show the first or last rows (a negative n means "all but
that many"); dim(), nrow() and ncol() give the size; names() or colnames() give the column names, and
dimnames() gives row and column names together as a list of two.
source("scenario.R")
str(books)
#> 'data.frame': 16 obs. of 6 variables:
#> $ title : chr "The Dispossessed" "Pride and Prejudice" " the quiet streets " "Loud Roofs" ...
#> $ author: chr "Ursula K. Le Guin" "Jane Austen" "Mara Quill" "Mara Quill" ...
#> $ isbn : chr "978-0-306-40615-7" "978-0-14-143951-8" "9780001000100" "978-0-00-200029-1" ...
#> $ year : int 1974 1813 2019 2021 2016 2008 NA 1999 2012 2023 ...
#> $ genre : chr "Science fiction" "Classic" "Fiction" "fiction" ...
#> $ pages : int 387 432 214 198 301 NA 96 280 352 256 ...
dim(books)
#> [1] 16 6
nrow(books)
#> [1] 16
ncol(books)
#> [1] 6
names(books)
#> [1] "title" "author" "isbn" "year" "genre" "pages"
dimnames(head(books[c("title", "year")], 2))
#> [[1]]
#> [1] "1" "2"
#>
#> [[2]]
#> [1] "title" "year"
#>
head(books, 3)
#> title author isbn year genre
#> 1 The Dispossessed Ursula K. Le Guin 978-0-306-40615-7 1974 Science fiction
#> 2 Pride and Prejudice Jane Austen 978-0-14-143951-8 1813 Classic
#> 3 the quiet streets Mara Quill 9780001000100 2019 Fiction
#> pages
#> 1 387
#> 2 432
#> 3 214
tail(books, 2)
#> title author isbn year genre pages
#> 15 Ten Winters Priya Raman 9780013001331 2022 Fiction NA
#> 16 Salt and Static Mara Quill 978-0-01-400145-3 2018 Mystery 244
head(books, -13)
#> title author isbn year genre
#> 1 The Dispossessed Ursula K. Le Guin 978-0-306-40615-7 1974 Science fiction
#> 2 Pride and Prejudice Jane Austen 978-0-14-143951-8 1813 Classic
#> 3 the quiet streets Mara Quill 9780001000100 2019 Fiction
#> pages
#> 1 387
#> 2 432
#> 3 214
summary(books$year)
#> Min. 1st Qu. Median Mean 3rd Qu. Max. NAs
#> 1813 2006 2016 1999 2020 2023 1
summary(books[c("year", "pages")])
#> year pages
#> Min. :1813 Min. : 88.0
#> 1st Qu.:2006 1st Qu.:202.0
#> Median :2016 Median :268.0
#> Mean :1999 Mean :268.2
#> 3rd Qu.:2020 3rd Qu.:344.5
#> Max. :2023 Max. :432.0
#> NAs :1 NAs :2
summary(books["genre"])
#> genre
#> Length :16
#> N.unique : 9
#> N.blank : 0
#> Min.nchar: 6
#> Max.nchar:15Source: R documentation: str
summary() of a data frame summarises each column according to its type: five numbers and the mean for numeric columns,
with a count of NAs for missing values, and, since R 4.6.0, a more informative summary for character columns. The NEWS
for R 4.6.0 says that the default summary() is now more informative for character vectors, optionally falling back to the
factor method with character.method = "factor", which matters when summarising a data frame; in the output above the
genre column reports Length, N.unique, N.blank, Min.nchar and Max.nchar. The same release changed the label
for missing values from NA's to NAs in the default and factor methods. Older tutorials show Class :character and
Mode :character for text columns and NA's for missing numbers; on R 4.6 the output is different. Nine unique spellings
of genre and a blank-padded title are visible at a glance in this summary, which is what a first look at a new data
frame is for.
Source: R NEWS: Changes in R 4.6.0
Treating the columns as a list
Since the columns are the elements of a list, vapply() over the data frame gives one value per column and is the
type-safe way to ask a question of every column. sapply() does the same but guesses the output shape, which fails when
the answers differ in length: a column that has two classes, such as a POSIXct date-time, makes sapply(df, class)
return a list instead of a character vector.
source("scenario.R")
# The class of every column; vapply() fixes the result type.
vapply(books, class, character(1))
#> title author isbn year genre pages
#> "character" "character" "character" "integer" "character" "integer"
vapply(loans, \(x) class(x)[[1L]], character(1))
#> loan_id isbn member loan_date return_date
#> "character" "character" "character" "Date" "Date"
# How many NA per column, and the columns that are numeric.
vapply(books, \(x) sum(is.na(x)), integer(1))
#> title author isbn year genre pages
#> 0 0 0 1 0 2
colSums(is.na(books))
#> title author isbn year genre pages
#> 0 0 0 1 0 2
names(books)[vapply(books, is.numeric, logical(1))]
#> [1] "year" "pages"
# lapply() over the columns returns a list, one result per column.
lapply(books[c("year", "pages")], range, na.rm = TRUE)
#> $year
#> [1] 1813 2023
#>
#> $pages
#> [1] 88 432
#>
# sapply() can return a list when the shapes differ: a POSIXct column has two
# classes, so the result is not a character vector.
stamped <- data.frame(
who = "Ana Ruiz",
at = as.POSIXct("2026-06-30 09:30:00", tz = "UTC")
)
sapply(stamped, class)
#> $who
#> [1] "character"
#>
#> $at
#> [1] "POSIXct" "POSIXt"
#>
vapply(stamped, \(x) class(x)[[1L]], character(1))
#> who at
#> "character" "POSIXct"colSums(is.na(books)) is the shortest way to count the missing values per column: is.na() on a data frame returns a
logical matrix of the same shape. The functions in the apply family are the subject of
The apply Family and Functionals; for data frames, the
rule of thumb is vapply() over the columns and split() with lapply() over groups of rows.
Row names
Every data frame has row names: the help page defines them as a character vector of length the number of rows with no
duplicates and no missing values. When the creator gives none, R supplies the integers 1:n and regards them as
automatic. row.names(df) always returns a character vector (use attr(df, "row.names") for the integer form);
rownames(df) is the same for a data frame, and the replacement forms rownames<- and row.names<- set them, with
NULL as the value meaning "reset to automatic".
source("scenario.R")
# Automatic row names are the integers 1..n, stored compactly.
head(rownames(books), 3)
#> [1] "1" "2" "3"
.row_names_info(books)
#> [1] -16
.row_names_info(books, type = 0L)
#> [1] NA -16
# Selecting rows keeps their old row names, so they no longer count 1..n.
shortlist <- books[c(8, 2, 5), c("title", "year")]
shortlist
#> title year
#> 8 The Lamp at Noon 1999
#> 2 Pride and Prejudice 1813
#> 5 Midnight Ledger 2016
rownames(shortlist)
#> [1] "8" "2" "5"
# Row numbers and row names are different things after a subset.
shortlist[1, ]
#> title year
#> 8 The Lamp at Noon 1999
shortlist["8", ]
#> title year
#> 8 The Lamp at Noon 1999
shortlist["1", ]
#> title year
#> NA <NA> NA
# rownames<- or row.names<- with NULL restores the automatic names.
rownames(shortlist) <- NULL
shortlist
#> title year
#> 1 The Lamp at Noon 1999
#> 2 Pride and Prejudice 1813
#> 3 Midnight Ledger 2016
# Character row names can serve as keys. Row names must be unique.
by_isbn <- books[nzchar(books$isbn), c("title", "year")]
rownames(by_isbn) <- books$isbn[nzchar(books$isbn)]
by_isbn["978-0-306-40615-7", ]
#> title year
#> 978-0-306-40615-7 The Dispossessed 1974
head(by_isbn, 2)
#> title year
#> 978-0-306-40615-7 The Dispossessed 1974
#> 978-0-14-143951-8 Pride and Prejudice 1813Source: R documentation: row.names
Subsetting keeps the old row names, so after books[c(8, 2, 5), ] the row names are 8, 2, 5, which are labels and no
longer positions. The consequence is the pair shortlist[1, ] and shortlist["8", ], which select the same row, while
shortlist["1", ] is a row of NA: a number selects by position, a string selects by label. Resetting with
rownames(df) <- NULL (the older spelling row.names(df) <- NULL works the same) is the usual last step after filtering
or sorting for display, and read.csv(..., row.names = NULL) or data.frame(..., row.names = NULL) does the same at
creation. Using a text column as the row names turns the data frame into a lookup table keyed by that column, and the
row.names argument of data.frame() and read.csv() can name the column to use. That is convenient but fragile, as the
next two examples show.
Duplicate row names
source("scenario.R")
shortlist <- books[c(8, 2, 5), c("title", "year")]
# Duplicate row names: a warning first, then an error.
seen <- character()
outcome <- withCallingHandlers(
tryCatch(
{
rownames(shortlist) <- c("a", "a", "b")
"no error"
},
error = conditionMessage
),
warning = function(w) {
seen <<- c(seen, conditionMessage(w))
invokeRestart("muffleWarning")
}
)
seen
#> [1] "non-unique value when setting 'row.names': ‘a’"
outcome
#> [1] "duplicate 'row.names' are not allowed"
# Repeating a row in a subset is allowed: R makes the row names unique.
books[c(1, 1, 2), c("title", "year")]
#> title year
#> 1 The Dispossessed 1974
#> 1.1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
# Row names supplied with the wrong length.
tryCatch(
data.frame(title = c("A", "B"), row.names = c("x", "y", "z")),
error = conditionMessage
)
#> [1] "row names supplied are of the wrong length"Source: R documentation: row.names
Setting duplicated row names gives a warning and then an error. The warning is non-unique value when setting 'row.names':
‘a’ and the error is duplicate 'row.names' are not allowed. A subset that repeats a row is allowed because R repairs the
names with make.unique(): the repeated row 1 becomes 1.1. The help page also documents a non-generic variant,
.rowNamesDF<-, with a make.names argument for the case where invalid row names should be repaired (TRUE) or replaced
by automatic ones (NA) instead of raising the error.
Partial matching of row names
The extraction methods [ and [[ for data frames match row names partially: the help page says that both partially
match row names, that neither does by default for column names, and that match() should be used for exact matching on
row names. This is easy to forget when row names are used as keys, because a prefix of a key is silently accepted, and an
ambiguous prefix gives a row of NA rather than an error.
source("scenario.R")
by_isbn <- books[nzchar(books$isbn), c("title", "year")]
rownames(by_isbn) <- books$isbn[nzchar(books$isbn)]
# Character row indexing matches partially: a unique prefix is enough.
by_isbn["978-0-306", ]
#> title year
#> 978-0-306-40615-7 The Dispossessed 1974
# An ambiguous prefix gives a row of NA, not an error.
by_isbn["978-0-00", ]
#> title year
#> NA <NA> NA
# [[ and exact column access do not do this; match() is exact.
by_isbn[match("978-0-306", rownames(by_isbn)), ]
#> title year
#> NA <NA> NA
by_isbn[rownames(by_isbn) == "978-0-306-40615-7", ]
#> title year
#> 978-0-306-40615-7 The Dispossessed 1974
# $ on a data frame also matches column names partially.
books$gen[1:3]
#> [1] "Science fiction" "Classic" "Fiction"
# options(warnPartialMatchDollar = TRUE) turns the silent match into a warning.
old <- options(warnPartialMatchDollar = TRUE)
tryCatch(books$gen, warning = conditionMessage)
#> [1] "partial match of 'gen' to 'genre'"
options(old)The last line shows a related rule: $ on a data frame, as on any list, matches a column name by a unique prefix, so
books$gen is books$genre. With options(warnPartialMatchDollar = TRUE) R warns when this happens. Code in a package
should never rely on it, and [[ with its default exact = TRUE does not do it. Prefer a column of keys and match() or
merge() (below) to row names as keys.
Selecting columns
A data frame answers to both list syntax and matrix syntax, and the two differ in what they return. The rule that explains
every case: with one index (df["a"], df[c("a", "b")]) the data frame is treated as a list, and the result is a data
frame; with two indices (df[i, j]) it is treated as a matrix, and a result with a single column is simplified to a
vector unless drop = FALSE is given; df$a and df[["a"]] extract the column itself.
| Expression | Returns | Notes |
|---|---|---|
|
the column, as a vector |
|
|
a data frame with one column |
list-style indexing, never drops |
|
a data frame |
list-style, all rows |
|
a vector |
matrix-style, one column, |
|
a data frame |
keeps the shape |
|
a one-row data frame |
one row never simplifies to a vector, unless |
|
a single value |
|
|
all columns except the first |
a negative position drops |
|
the numeric columns |
select by a property of the columns |
source("scenario.R")
# One column: $ and [[ give a vector, [ gives a data frame.
books$year[1:4]
#> [1] 1974 1813 2019 2021
books[["year"]][1:4]
#> [1] 1974 1813 2019 2021
class(books["year"])
#> [1] "data.frame"
head(books["year"], 2)
#> year
#> 1 1974
#> 2 1813
# Several columns: a character vector inside [ or [,].
head(books[c("title", "year")], 2)
#> title year
#> 1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
head(books[, c("title", "year")], 2)
#> title year
#> 1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
# A single column with [, ] drops to a vector unless drop = FALSE.
class(books[, "year"])
#> [1] "integer"
class(books[, "year", drop = FALSE])
#> [1] "data.frame"
class(books[1, "year"])
#> [1] "integer"
class(books[1, ])
#> [1] "data.frame"
books[1, , drop = TRUE]$title
#> [1] "The Dispossessed"
# By position, by exclusion and by type.
head(books[1:2], 2)
#> title author
#> 1 The Dispossessed Ursula K. Le Guin
#> 2 Pride and Prejudice Jane Austen
names(books[-(1:3)])
#> [1] "year" "genre" "pages"
names(books[vapply(books, is.numeric, logical(1))])
#> [1] "year" "pages"
# Rows by position, as a data frame.
books[c(1, 3), c("title", "year")]
#> title year
#> 1 The Dispossessed 1974
#> 3 the quiet streets 2019
books[[3, "title"]]
#> [1] " the quiet streets "The same function [ can return a vector or a data frame depending on the number of columns selected, which is the
data-frame version of the matrix pitfall in R FAQ 7.5 (R FAQ 7.5: Why do my matrices lose dimensions?).
Code that must work for any number of selected columns writes drop = FALSE explicitly. The one-index form never has the
problem.
Missing columns and out-of-range positions
source("scenario.R")
# A column that does not exist: NULL with $ and [[, an error with [.
books$publisher
#> NULL
books[["publisher"]]
#> NULL
tryCatch(books[, "publisher"], error = conditionMessage)
#> [1] "undefined columns selected"
tryCatch(books["publisher"], error = conditionMessage)
#> [1] "undefined columns selected"
tryCatch(books[, c("title", "publisher")], error = conditionMessage)
#> [1] "undefined columns selected"
# A position past the end.
tryCatch(books[[7L]], error = conditionMessage)
#> [1] "subscript out of bounds"
tryCatch(books[, 7L], error = conditionMessage)
#> [1] "undefined columns selected"
# A row past the end gives a row of NA, not an error.
books[17L, c("title", "year")]
#> title year
#> NA <NA> NA
# $ cannot be used on the atomic vector that a column is.
tryCatch(books$year$min, error = conditionMessage)
#> [1] "$ operator is invalid for atomic vectors"A column that does not exist is NULL for $ and [[, which turns a typing mistake into a silent NULL that fails later,
and the error undefined columns selected for [. The same error appears for a very common slip: forgetting the comma in
books[books$genre == "Poetry"], which asks for columns chosen by a logical vector of length 16 and fails with the
message above. A position beyond the last one is subscript out of bounds. A row beyond the last one does not fail:
it returns a row of NA with a row name like NA. And since a column is an atomic vector, $ applied to it is an error
($ operator is invalid for atomic vectors).
Filtering rows
df[condition, ], with a logical vector as the first index and nothing after the comma, keeps the rows where the
condition is TRUE. The classic trap is NA in the condition: an NA index selects a row of NA, not nothing. The
year column of books has one missing value, so books$year > 2000 has one NA, and the filtered data frame contains a
phantom row named NA.
source("scenario.R")
# A logical vector selects rows. NA in the test gives a row of NA.
recent <- books[books$year > 2000, c("title", "year")]
recent
#> title year
#> 3 the quiet streets 2019
#> 4 Loud Roofs 2021
#> 5 Midnight Ledger 2016
#> 6 A Map of Salt 2008
#> NA <NA> NA
#> 9 Rivers of Glass 2012
#> 10 THE LONG TABLE 2023
#> 11 Paper Moons 2005
#> 12 Notes on Gravity 2017
#> 13 Orchard Logic 2020
#> 14 The Slow Comet 2014
#> 15 Ten Winters 2022
#> 16 Salt and Static 2018
sum(books$year > 2000)
#> [1] NA
# which() drops the NA positions; so does subset().
books[which(books$year > 2000), c("title", "year")]
#> title year
#> 3 the quiet streets 2019
#> 4 Loud Roofs 2021
#> 5 Midnight Ledger 2016
#> 6 A Map of Salt 2008
#> 9 Rivers of Glass 2012
#> 10 THE LONG TABLE 2023
#> 11 Paper Moons 2005
#> 12 Notes on Gravity 2017
#> 13 Orchard Logic 2020
#> 14 The Slow Comet 2014
#> 15 Ten Winters 2022
#> 16 Salt and Static 2018
subset(books, year > 2000, select = c(title, year))
#> title year
#> 3 the quiet streets 2019
#> 4 Loud Roofs 2021
#> 5 Midnight Ledger 2016
#> 6 A Map of Salt 2008
#> 9 Rivers of Glass 2012
#> 10 THE LONG TABLE 2023
#> 11 Paper Moons 2005
#> 12 Notes on Gravity 2017
#> 13 Orchard Logic 2020
#> 14 The Slow Comet 2014
#> 15 Ten Winters 2022
#> 16 Salt and Static 2018
# Name the missing values explicitly when they matter.
books[is.na(books$year), c("title", "year")]
#> title year
#> 7 Garden of Small Fires NA
books[!is.na(books$year) & books$year > 2000, "title"]
#> [1] " the quiet streets " "Loud Roofs" "Midnight Ledger"
#> [4] "A Map of Salt" "Rivers of Glass" "THE LONG TABLE"
#> [7] "Paper Moons" "Notes on Gravity" "Orchard Logic"
#> [10] "The Slow Comet" "Ten Winters" "Salt and Static"
# Combine conditions with & and |, and test membership with %in%.
poetry_or_mystery <- books$genre %in% c("Poetry", "Mystery")
books[poetry_or_mystery & books$pages < 250, c("title", "genre", "pages")]
#> title genre pages
#> 7 Garden of Small Fires Poetry 96
#> 11 Paper Moons Poetry 88
#> 16 Salt and Static Mystery 244
sum(books$year > 2000, na.rm = TRUE)
#> [1] 12
mean(books$year > 2000, na.rm = TRUE)
#> [1] 0.8Four ways to avoid the phantom row, from most explicit to most convenient:
-
which(cond)turns the logical vector into positions and dropsNAandFALSEalike; -
!is.na(x) & condstates the intent; -
subset(df, cond)treatsNAasFALSE; -
sum(cond, na.rm = TRUE)andmean(cond, na.rm = TRUE)count and take proportions of the known values.
books[books$year > 2000, "title"] fails differently: the vector result contains an NA instead of a phantom row, and a
later nchar() or paste() on it quietly propagates it. The page
Missing and Special Values explains why a comparison with NA
is NA.
subset() and its non-standard evaluation
subset(df, condition, select = columns) is the readable form: the condition is evaluated inside the data frame, so the
columns are written as bare names, and select accepts bare names, ranges and negative selections
(select = -c(author, pages)). The help page warns that it is meant for interactive use and
recommends the standard subsetting functions for programming. The reason is the same as for transform() and with():
the condition is not evaluated in the calling function but in the data frame first, and a column whose name equals a
variable of the caller wins.
source("scenario.R")
# subset() evaluates its condition inside the data frame (non-standard
# evaluation), which is convenient interactively...
subset(books, pages > 400, select = c(title, pages))
#> title pages
#> 2 Pride and Prejudice 432
#> 12 Notes on Gravity 410
# ...and wrong when a parameter has the same name as a column: the column wins.
published_before <- function(df, year) {
subset(df, year < year, select = c(title, year))
}
published_before(books, 1900L)
#> [1] title year
#> <0 rows> (or 0-length row.names)
# The standard subsetting spells out where each name comes from.
published_before_std <- function(df, cutoff) {
df[which(df$year < cutoff), c("title", "year")]
}
published_before_std(books, 1900L)
#> title year
#> 2 Pride and Prejudice 1813
# Since R 4.3.0 subset() warns about extra arguments, which catches "=" typed
# instead of "==".
tryCatch(
subset(books, genre = "Poetry"),
warning = \(w) writeLines(conditionMessage(w))
)
#> In subset.data.frame(books, genre = "Poetry") :
#> extra argument ‘genre’ will be disregardedSource: R documentation: subset
In published_before(books, 1900L) the parameter year is shadowed by the column year, so the condition is
year < year for every row and the result has zero rows, with no error and no warning, while the right answer is one
book. The standard form with a differently named parameter, df[which(df$year < cutoff), ], has no such ambiguity. Since R
4.3.0 subset() warns about extraneous arguments, which catches the single = typed instead of ==; the NEWS entry
describes the warning as aimed at exactly that slip, and the message shown above is what R prints.
Source: R NEWS: Changes in R 4.3.0
Empty results, complete.cases() and na.omit()
A filter that selects nothing returns a data frame with zero rows and the same columns, which prints as
<0 rows> (or 0-length row.names). Test for it with nrow(df) == 0L (or NROW() when the object might be a vector);
testing if (df) is an error, and length(df) is the number of columns, which is not zero.
complete.cases(df) returns one logical per row, TRUE when no column in that row is NA; it also accepts several
vectors, as in complete.cases(books$year, books$pages), to restrict the test to the columns that matter. na.omit(df)
drops the incomplete rows and records their positions in the attribute na.action, which print() hides. Dropping rows
silently is how a careless pipeline loses data, so count what was dropped (the na.action attribute, or
sum(!complete.cases(df))) and say so.
source("scenario.R")
# A filter that selects nothing returns a data frame with zero rows.
none <- books[which(books$year > 3000), ]
none
#> [1] title author isbn year genre pages
#> <0 rows> (or 0-length row.names)
nrow(none)
#> [1] 0
nrow(none) == 0L
#> [1] TRUE
dim(none)
#> [1] 0 6
str(none$title)
#> chr(0)
# Test the row count, not the data frame itself.
if (nrow(none) == 0L) "nothing on the shelf from the future"
#> [1] "nothing on the shelf from the future"
# complete.cases() flags rows with no NA in any column; na.omit() drops them.
complete.cases(books)
#> [1] TRUE TRUE TRUE TRUE TRUE FALSE FALSE TRUE TRUE TRUE TRUE TRUE
#> [13] TRUE TRUE FALSE TRUE
books[complete.cases(books$year, books$pages), c("title", "year", "pages")]
#> title year pages
#> 1 The Dispossessed 1974 387
#> 2 Pride and Prejudice 1813 432
#> 3 the quiet streets 2019 214
#> 4 Loud Roofs 2021 198
#> 5 Midnight Ledger 2016 301
#> 8 The Lamp at Noon 1999 280
#> 9 Rivers of Glass 2012 352
#> 10 THE LONG TABLE 2023 256
#> 11 Paper Moons 2005 88
#> 12 Notes on Gravity 2017 410
#> 13 Orchard Logic 2020 175
#> 14 The Slow Comet 2014 322
#> 16 Salt and Static 2018 244
cleaned <- na.omit(books)
nrow(cleaned)
#> [1] 13
attr(cleaned, "na.action")
#> 6 7 15
#> 6 7 15
#> attr(,"class")
#> [1] "omit"
colSums(is.na(books))
#> title author isbn year genre pages
#> 0 0 0 1 0 2Source: R documentation: complete.cases
Rows 6, 7 and 15 are the incomplete ones: A Map of Salt and Ten Winters have no page count and
Garden of Small Fires has no year. na.omit(books) therefore leaves 13 rows, and colSums(is.na(books)) shows where
the holes are.
Adding and removing columns
A column is added or replaced by assigning to df$name or df[["name"]]. The assigned vector needs one value per row; a
shorter one is recycled if its length divides the row count and is an error otherwise. The error message is the one to
recognise: replacement has 3 rows, data has 16. cbind(df, new = ...) builds a new data frame with the extra column and
transform(df, new = expression) evaluates its arguments inside the data frame, like subset(). All three leave the
original untouched until the result is assigned, because R copies on modification
(Assignment, Names and Values).
source("scenario.R")
catalogue <- books[c("title", "year", "pages")]
# $<- and [[<- add a column; a length-one value is recycled.
catalogue$decade <- catalogue$year %/% 10L * 10L
catalogue[["thick"]] <- catalogue$pages > 300
catalogue$source <- "front desk"
head(catalogue, 3)
#> title year pages decade thick source
#> 1 The Dispossessed 1974 387 1970 TRUE front desk
#> 2 Pride and Prejudice 1813 432 1810 TRUE front desk
#> 3 the quiet streets 2019 214 2010 FALSE front desk
# The new column must have one value per row, or a divisor of that number.
tryCatch(catalogue$rank <- 1:3, error = conditionMessage)
#> [1] "replacement has 3 rows, data has 16"
tryCatch(catalogue$rank <- 1:5, error = conditionMessage)
#> [1] "replacement has 5 rows, data has 16"
# cbind() on data frames dispatches to data.frame().
extra <- data.frame(shelf = rep(c("A", "B"), length.out = nrow(catalogue)))
head(cbind(catalogue, extra), 3)
#> title year pages decade thick source shelf
#> 1 The Dispossessed 1974 387 1970 TRUE front desk A
#> 2 Pride and Prejudice 1813 432 1810 TRUE front desk B
#> 3 the quiet streets 2019 214 2010 FALSE front desk A
# transform() adds or replaces columns, evaluating its arguments in the data.
head(transform(catalogue, pages_k = pages / 1000, source = NULL), 3)
#> title year pages decade thick pages_k
#> 1 The Dispossessed 1974 387 1970 TRUE 0.387
#> 2 Pride and Prejudice 1813 432 1810 TRUE 0.432
#> 3 the quiet streets 2019 214 2010 FALSE 0.214
# [<- with a logical index changes some cells; NA cells need is.na().
catalogue$pages[is.na(catalogue$pages)] <- 0L
catalogue[catalogue$pages == 0L, c("title", "pages")]
#> title pages
#> 6 A Map of Salt 0
#> 15 Ten Winters 0Source: R documentation: transform
transform() has two limits: its arguments are all evaluated in the original data frame, so one new column cannot use
another created in the same call (shown with within() below), and it uses the same non-standard evaluation as
subset(). Assigning NULL through transform(source = NULL) removes a column, as the example shows.
catalogue$pages[is.na(catalogue$pages)] <- 0L replaces the missing values in place; the last two lines show the
result, and the choice of 0L there is only to make the replaced rows visible: a missing page count is not zero pages.
To remove columns, assign NULL (df$col <- NULL or df[["col"]] <- NULL), or select the columns to keep. The safe form
of "all columns except these" is df[setdiff(names(df), drop_names)] or df[!names(df) %in% drop_names] or
subset(df, select = -c(a, b)); they all do the right thing when a name to drop is absent. The negative-index form
df[-which(names(df) == "x")] is a trap: when "x" is not a column, which() returns integer(0), -integer(0) is
integer(0), and the result is an empty selection that drops every column.
source("scenario.R")
catalogue <- books[c("title", "author", "year", "pages")]
catalogue$source <- "front desk"
catalogue$temp <- NA
# Assign NULL to remove a column.
catalogue$temp <- NULL
catalogue[["source"]] <- NULL
names(catalogue)
#> [1] "title" "author" "year" "pages"
# Keep every column except some. setdiff() is safe when the name is absent.
names(catalogue[setdiff(names(catalogue), c("author", "missing"))])
#> [1] "title" "year" "pages"
names(catalogue[!names(catalogue) %in% c("author", "missing")])
#> [1] "title" "year" "pages"
names(subset(catalogue, select = -c(author, pages)))
#> [1] "title" "year"
# A negative index built by which() is a trap: when nothing matches,
# -integer(0) is an empty index and every column is dropped.
dim(catalogue[-which(names(catalogue) == "author")])
#> [1] 16 3
dim(catalogue[-which(names(catalogue) == "missing")])
#> [1] 16 0
# Rename and reorder.
names(catalogue)[names(catalogue) == "pages"] <- "page_count"
names(catalogue)
#> [1] "title" "author" "year" "page_count"
names(catalogue[c("year", "title", "author", "page_count")])
#> [1] "year" "title" "author" "page_count"
catalogue <- setNames(catalogue, toupper(names(catalogue)))
names(catalogue)
#> [1] "TITLE" "AUTHOR" "YEAR" "PAGE_COUNT"Source: R documentation: names
names(df) is a plain character vector: assign to it, or to one element of it, to rename columns; setNames(df, new)
returns a renamed copy; colnames<- is the same function by another name for a data frame; and df[c("b", "a", "c")]
reorders. The documentation for names<- is at
R documentation: names.
Adding rows
rbind() on data frames appends rows. The second argument can be another data frame, matched to the first by column
name, not by position, or a list, matched by position. If the names differ or the number of columns differs, the error
is names do not match previous names or numbers of columns of arguments do not match. Since R 4.2.0, binding a vector
whose length is not a divisor of the number of columns also warns.
source("scenario.R")
core <- books[1:2, c("title", "year")]
# rbind() matches columns by name, not by position.
new_row <- data.frame(year = 2024L, title = "Shelf Notes")
rbind(core, new_row)
#> title year
#> 1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
#> 3 Shelf Notes 2024
# A list is matched by position.
rbind(core, list("Open Stacks", 2025L))
#> title year
#> 1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
#> 3 Open Stacks 2025
# Different names or a different number of columns are errors.
tryCatch(
rbind(core, data.frame(name = "Shelf Notes", year = 2024L)),
error = conditionMessage
)
#> [1] "names do not match previous names"
tryCatch(
rbind(core, data.frame(title = "Shelf Notes", year = 2024L, pages = 10L)),
error = conditionMessage
)
#> [1] "numbers of columns of arguments do not match"
# Assigning past the last row also adds one, but copies the whole frame.
core[nrow(core) + 1L, ] <- list("Quiet Hours", 2026L)
core
#> title year
#> 1 The Dispossessed 1974
#> 2 Pride and Prejudice 1813
#> 3 Quiet Hours 2026
# Build many rows in a list and bind once, instead of growing in a loop.
pieces <- lapply(1:3, \(i) {
data.frame(title = paste("Volume", i), year = 2020L + i)
})
do.call(rbind, pieces)
#> title year
#> 1 Volume 1 2021
#> 2 Volume 2 2022
#> 3 Volume 3 2023Source: R documentation: cbind and rbind
The idiom df[nrow(df) + 1L, ] <- list(...) also appends, but both forms copy the whole data frame, so appending in a loop
is quadratic: collect the new rows in a list and bind them once with do.call(rbind, pieces). The NEWS entry for R 4.2.0
records the warning for vectors that do not recycle wholly. Row names of the result are renumbered when the inputs have
automatic row names; otherwise they are kept and made unique, which is the subject of the section on do.call(rbind, ...)
below.
Source: R NEWS: Changes in R 4.2.0
with(), within() and why not attach()
with(df, expr) evaluates expr with the columns visible as variables and returns the value of the expression;
within(df, expr) does the same and returns the modified data frame, so assignments inside the braces create or change
columns and rm() removes them. Both are in the help page with, and both are the safe replacements for attach():
the data frame is visible only for the duration of the expression.
source("scenario.R")
# with() evaluates an expression with the columns visible as variables.
with(books, mean(pages, na.rm = TRUE))
#> [1] 268.2143
with(books, table(tolower(trimws(genre))))
#>
#> classic fiction mystery non-fiction poetry
#> 2 4 2 3 2
#> science fiction
#> 3
with(books, title[which.max(pages)])
#> [1] "Pride and Prejudice"
# within() returns the modified data frame. New columns are added in reverse
# order of creation, and rm() removes columns.
tidy <- within(books[c("title", "year", "pages")], {
age <- 2026L - year
long <- pages > 300
rm(pages)
})
names(tidy)
#> [1] "title" "year" "long" "age"
head(tidy, 3)
#> title year long age
#> 1 The Dispossessed 1974 TRUE 52
#> 2 Pride and Prejudice 1813 TRUE 213
#> 3 the quiet streets 2019 FALSE 7
# transform() keeps the order of its arguments, but all of them are evaluated
# in the original data, so one argument cannot use another new column.
names(transform(
books[c("title", "year")],
age = 2026L - year, stale = year < 1900L
))
#> [1] "title" "year" "age" "stale"
tryCatch(
transform(books[c("title", "year")], age = 2026L - year, old = age > 100L),
error = conditionMessage
)
#> [1] "object 'age' not found"Source: R documentation: with and within
within() has a quirk worth knowing: variables created inside it are added to the data frame in the reverse of the order in
which they were created (long before age, although age was assigned first). transform() keeps the order of its
arguments but cannot refer to a column created in the same call, as the error object 'age' not found shows. For a
sequence of dependent steps, plain df$new <- ... statements are the most readable.
attach(): why older tutorials are wrong
An Introduction to R devotes a whole section to attach() and detach() (6.3.2) and a convention for working with data
frames that attaches one at position 2 of the search path, works with bare names, and detaches it at the end
(An Introduction to R: attach() and detach(),
Working with data frames). The same
manual lists the caveats: what is attached is a copy of the data frame, so changes to the data frame are not visible to
the attached names until it is detached and attached again; assigning to an attached name creates a new variable in the
workspace, which masks the attached one, instead of changing the data frame; lists and data frames can only be attached
at position 2 or later; and detach() should name what it detaches. Section 6.3.4 of that manual extends attach() to any list, and with() likewise accepts a list or an environment
(Attaching arbitrary lists).
The R FAQ repeats the position restriction in
R FAQ 3.3.3: Others, in the list of differences between R and S. Modern code avoids attach() for these reasons: two data frames with the same column
names mask each other silently, a script that attaches inside a function leaves the search path changed on error, and a
reader cannot tell where a name comes from.
The script below shows the older style, caught and shown so that the effects are visible; it is not a pattern to copy.
source("scenario.R")
# An older tutorial: attach() puts a copy of the columns on the search path.
year <- 1900L # a workspace variable with the name of a column
masked <- capture.output(attach(books), type = "message")
masked
#> [1] "The following object is masked _by_ .GlobalEnv:"
#> [2] ""
#> [3] " year"
#> [4] ""
search()[1:3]
#> [1] ".GlobalEnv" "books" "package:stats"
pages[1:3]
#> [1] 387 432 214
year[1:3]
#> [1] 1900 NA NA
# Changing the data frame does not change the attached copy.
books$pages[1] <- 1L
pages[1]
#> [1] 387
books$pages[1]
#> [1] 1
# Assigning to an attached name creates a workspace variable instead.
pages <- pages + 1L
books$pages[2]
#> [1] 432
detach(books)
search()[1:2]
#> [1] ".GlobalEnv" "package:stats"
# The same work without attach().
with(books, mean(pages, na.rm = TRUE))
#> [1] 240.6429The message The following object is masked _by_ .GlobalEnv: and the variable year name the clash: the workspace variable
year that was created before attach() hides the attached column, so year[1:3] is 1900 NA NA and not the first
three years. books$pages[1] <- 1L changed the data frame, but the attached pages still starts with 387, and
pages <- pages + 1L created a workspace variable and left books$pages[2] at 432. After detach(books) the search path
is back to normal. The replacements are with(), within(), transform(), the data argument that most modelling
functions accept, or a plain df$col.
Cleaning the shelf data
Real data frames arrive with blanks, inconsistent case and encoded missing values, and the cleaning is ordinary column
arithmetic: trimws() removes leading and trailing blanks, tolower() or toupper() fixes the case, nchar() measures,
and gsub() with a regular expression collapses repeated blanks. The patterns are in
Strings and Regular Expressions.
source("scenario.R")
# books$genre has nine spellings for six genres.
table(books$genre)
#>
#> Classic fiction Fiction Fiction Mystery
#> 2 1 2 1 2
#> Non-fiction Poetry science fiction Science fiction
#> 3 2 1 2
nchar(books$genre)[1:4]
#> [1] 15 7 7 7
which(nchar(books$genre) != nchar(trimws(books$genre)))
#> [1] 10
# trimws() removes the blanks, tolower() the case differences.
books$genre <- tolower(trimws(books$genre))
table(books$genre)
#>
#> classic fiction mystery non-fiction poetry
#> 2 4 2 3 2
#> science fiction
#> 3
# The same for titles and authors: collapse repeated blanks with a regular
# expression (see the strings page).
books$title <- trimws(books$title)
books$author <- gsub("\\s+", " ", trimws(books$author))
books[c(3, 10), c("title", "author")]
#> title author
#> 3 the quiet streets Mara Quill
#> 10 THE LONG TABLE Iris Fenwick
# loans$member: blanks, case, and an empty string that should be NA.
sort(unique(loans$member))
#> [1] "" "Ana Ruiz" "ana ruiz " "Ben Okafor"
#> [5] "Chloe Martin" "Dev Patel" "Eva Lindqvist" "Farid Karimi"
#> [9] "Grace Liu" "Hugo Silva"
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
sort(unique(loans$member))
#> [1] "Ana Ruiz" "Ben Okafor" "Chloe Martin" "Dev Patel"
#> [5] "Eva Lindqvist" "Farid Karimi" "Grace Liu" "Hugo Silva"Source: R documentation: trimws
Nine spellings of the genre become six: fiction, Fiction and Fiction with a trailing blank become fiction,
science fiction and Science fiction become science fiction, and the other four only change case. Comparing nchar(x) with
nchar(trimws(x)) finds the strings that have padding, and which() reports the rows. tools::toTitleCase() turns
ana ruiz into Ana Ruiz, and the empty member string becomes NA so that it is not mistaken for a member. The second
spelling of Ursula K. Le Guin (Le Guin, Ursula K.) is a different problem: no rule fixes it, and a lookup table or a
manual replacement is the honest answer.
The loans need one derived column, the number of days a loan was held: until the return date when there is one, and until
shelf_today (2026-06-30) when the book is still out. A loan is overdue when it was held for more than loan_days (21)
days.
source("scenario.R")
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
# Days held: until the return date, or until shelf_today when still out.
end <- loans$return_date
end[is.na(end)] <- shelf_today
loans$held <- as.integer(end - loans$loan_date)
loans$overdue <- loans$held > loan_days
head(loans[c("loan_id", "member", "held", "overdue")], 4)
#> loan_id member held overdue
#> 1 L001 Hugo Silva 21 FALSE
#> 2 L002 Ben Okafor 10 FALSE
#> 3 L003 Dev Patel 1 FALSE
#> 4 L004 Eva Lindqvist 26 TRUE
# Rows that need a look: a return before the loan, and an impossible date.
odd_rows <- which(loans$held < 0L | is.na(loans$held))
loans[odd_rows, c("loan_id", "loan_date", "return_date", "held")]
#> loan_id loan_date return_date held
#> 12 L012 2025-04-23 2025-04-20 -3
#> 20 L020 <NA> 2025-07-15 NA
table(loans$overdue, useNA = "ifany")
#>
#> FALSE TRUE <NA>
#> 27 32 1Source: R documentation: Dates
The two rows listed at the end are data problems that the arithmetic exposed: loan L012 was returned three days before
it was made, and L020 has the impossible loan date 2025-02-30, which became NA when loans.csv was read. Both are
FALSE/NA for overdue and both need a decision (fix, drop, or flag) that belongs to the owner of the data; the code
above only finds them. Of the 60 loans, 32 are overdue, 27 are not, and one is NA.
Sorting
R has no ORDER BY. A data frame is sorted by computing a permutation of the row numbers with order() and indexing the
rows with it: df[order(df$a, df$b), ]. The R FAQ answers the question "How can I sort the rows of a data frame?" with
exactly that expression
(R FAQ 7.23), and adds that from
R 4.4.0 sort_by() provides a less verbose alternative with a formula interface. sort() is not the tool: it sorts one
vector and returns the sorted values, not the permutation.
order()
order(..., na.last = TRUE, decreasing = FALSE, method = c("auto", "shell", "radix")) takes one or more keys of the same
length and returns the integer permutation. Later keys break ties in earlier ones; the sort is stable, so rows that tie on
every key keep their original order; NA go last by default.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
shelf <- books[c("title", "genre", "year")]
# order() returns the permutation that sorts; NA go last.
order(shelf$year)
#> [1] 2 1 8 11 6 9 14 5 12 16 3 13 4 15 10 7
shelf[order(shelf$year), ]
#> title genre year
#> 2 Pride and Prejudice classic 1813
#> 1 The Dispossessed science fiction 1974
#> 8 The Lamp at Noon classic 1999
#> 11 Paper Moons poetry 2005
#> 6 A Map of Salt non-fiction 2008
#> 9 Rivers of Glass non-fiction 2012
#> 14 The Slow Comet science fiction 2014
#> 5 Midnight Ledger mystery 2016
#> 12 Notes on Gravity science fiction 2017
#> 16 Salt and Static mystery 2018
#> 3 the quiet streets fiction 2019
#> 13 Orchard Logic non-fiction 2020
#> 4 Loud Roofs fiction 2021
#> 15 Ten Winters fiction 2022
#> 10 THE LONG TABLE fiction 2023
#> 7 Garden of Small Fires poetry NA
# Several keys: later keys break ties in the earlier ones.
shelf[order(shelf$genre, shelf$year), ]
#> title genre year
#> 2 Pride and Prejudice classic 1813
#> 8 The Lamp at Noon classic 1999
#> 3 the quiet streets fiction 2019
#> 4 Loud Roofs fiction 2021
#> 15 Ten Winters fiction 2022
#> 10 THE LONG TABLE fiction 2023
#> 5 Midnight Ledger mystery 2016
#> 16 Salt and Static mystery 2018
#> 6 A Map of Salt non-fiction 2008
#> 9 Rivers of Glass non-fiction 2012
#> 13 Orchard Logic non-fiction 2020
#> 11 Paper Moons poetry 2005
#> 7 Garden of Small Fires poetry NA
#> 1 The Dispossessed science fiction 1974
#> 14 The Slow Comet science fiction 2014
#> 12 Notes on Gravity science fiction 2017
# decreasing = TRUE applies to every key; minus negates a numeric key.
head(shelf[order(shelf$year, decreasing = TRUE), ], 3)
#> title genre year
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
shelf[order(shelf$genre, -shelf$year), ]
#> title genre year
#> 8 The Lamp at Noon classic 1999
#> 2 Pride and Prejudice classic 1813
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
#> 3 the quiet streets fiction 2019
#> 16 Salt and Static mystery 2018
#> 5 Midnight Ledger mystery 2016
#> 13 Orchard Logic non-fiction 2020
#> 9 Rivers of Glass non-fiction 2012
#> 6 A Map of Salt non-fiction 2008
#> 11 Paper Moons poetry 2005
#> 7 Garden of Small Fires poetry NA
#> 12 Notes on Gravity science fiction 2017
#> 14 The Slow Comet science fiction 2014
#> 1 The Dispossessed science fiction 1974Source: R documentation: order
decreasing = TRUE reverses every key. To sort one key ascending and another descending, negate the numeric key
(-shelf$year). That fails for text and factors, where there is no unary minus, and -xtfrm(x) is the general form: the
xtfrm() function converts any sortable vector to numbers that sort the same way, and the negation reverses them.
Alternatively method = "radix" accepts a decreasing vector with one entry per key.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
books$title <- trimws(books$title)
shelf <- books[c("title", "genre", "year")]
# A character key cannot be negated; -xtfrm() works on any sortable type.
tryCatch(order(-shelf$genre), error = conditionMessage)
#> [1] "invalid argument to unary operator"
shelf[order(shelf$genre, -xtfrm(shelf$title)), ][1:5, ]
#> title genre year
#> 8 The Lamp at Noon classic 1999
#> 2 Pride and Prejudice classic 1813
#> 3 the quiet streets fiction 2019
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
# Mixed directions with method = "radix" take a logical vector.
mixed <- order(
shelf$genre, shelf$year,
decreasing = c(FALSE, TRUE), method = "radix"
)
shelf[mixed, ][1:5, ]
#> title genre year
#> 8 The Lamp at Noon classic 1999
#> 2 Pride and Prejudice classic 1813
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
tryCatch(
order(shelf$genre, shelf$year, decreasing = c(FALSE, TRUE)),
error = conditionMessage
)
#> [1] "'decreasing' of length > 1 is only for method = \"radix\""
# na.last controls NA: TRUE (default), FALSE, or NA to drop them.
order(shelf$year, na.last = FALSE)[1:3]
#> [1] 7 2 1
length(order(shelf$year, na.last = NA))
#> [1] 15
# Ties keep their original order; rev(order(x)) reverses them.
ties <- c(2, 1, 2, 1)
order(ties, decreasing = TRUE)
#> [1] 1 3 2 4
rev(order(ties))
#> [1] 3 1 4 2
# A data frame cannot be a sort key.
tryCatch(order(shelf), error = conditionMessage)
#> [1] "cannot xtfrm data frames"Source: R documentation: xtfrm
The error for a negated character key is invalid argument to unary operator; a decreasing vector without the radix
method is 'decreasing' of length > 1 is only for method = "radix"; and a data frame handed to order() as a key is
cannot xtfrm data frames (pass the columns, not the frame). rev(order(x)) is not the same as
order(x, decreasing = TRUE) when there are ties: the first reverses the stable order of ties, the second keeps it. The
help page also carries a warning for programmatic use: do not name the ... arguments, and do not write
do.call(order, df) for a data frame without removing the names (do.call(order, unname(as.list(df)))), since a column
called decreasing or method would be taken as a control argument.
Sorting text depends on the locale
For character keys, every method except "radix" follows the collating sequence of the locale, and "radix" always
compares code points as in the C locale. The two disagree on case. In an English UTF-8 locale (the example locale name is
en_US.UTF-8; the container that produced the output below used it) "Ten Winters" sorts before "THE LONG TABLE", and in
the radix order every upper-case letter sorts before every lower-case one, so "THE LONG TABLE" comes first. A script
that must give the same order on every machine either sorts on tolower(x) or sets method = "radix" and accepts the
code-point order; see Encodings, Locales and
Internationalization.
source("scenario.R")
# Character keys are compared with the collation rules of the locale, unless
# method = "radix" is used, which always compares code points (C locale).
titles <- c(
"the quiet streets", "THE LONG TABLE", "Ten Winters", "A Map of Salt"
)
titles[order(titles)]
#> [1] "A Map of Salt" "Ten Winters" "THE LONG TABLE"
#> [4] "the quiet streets"
titles[order(titles, method = "radix")]
#> [1] "A Map of Salt" "THE LONG TABLE" "Ten Winters"
#> [4] "the quiet streets"
sort(titles)
#> [1] "A Map of Salt" "Ten Winters" "THE LONG TABLE"
#> [4] "the quiet streets"
sort(titles, method = "radix")
#> [1] "A Map of Salt" "THE LONG TABLE" "Ten Winters"
#> [4] "the quiet streets"
# Lower-case the key when the order must not depend on the locale.
titles[order(tolower(titles))]
#> [1] "A Map of Salt" "Ten Winters" "THE LONG TABLE"
#> [4] "the quiet streets"Source: R documentation: sort
sort_by()
sort_by() is a generic function added in R 4.4.0. The NEWS entry describes it as primarily useful for its data frame
method, which sorts the rows of a data frame by one or more columns (R NEWS:
Changes in R 4.4.0). For a data frame, y is typically a formula whose terms are evaluated inside the data frame:
~ g or ~ list(g) sorts by g, and ~ g1 + g2 or ~ list(g1, g2) by several keys with the later ones breaking
ties. It is a front end to order(): the keys are collected and passed to it, and the remaining arguments (decreasing,
na.last, method) go to order() too. If y is not a formula, a vector or a list of vectors is used as given, with no
non-standard evaluation. The default method sorts a vector by another vector, x[order(y, ...)].
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
shelf <- books[c("title", "genre", "year")]
# sort_by() (R 4.4.0): the formula names the sort keys, evaluated in the data.
sort_by(shelf, ~ genre + year)[1:5, ]
#> title genre year
#> 2 Pride and Prejudice classic 1813
#> 8 The Lamp at Noon classic 1999
#> 3 the quiet streets fiction 2019
#> 4 Loud Roofs fiction 2021
#> 15 Ten Winters fiction 2022
# ~ list() takes any expression per key, including a negated one.
sort_by(shelf, ~ list(genre, -year))[1:5, ]
#> title genre year
#> 8 The Lamp at Noon classic 1999
#> 2 Pride and Prejudice classic 1813
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
# Without a formula the keys are ordinary vectors (no non-standard evaluation).
sort_by(shelf, list(shelf$genre, -shelf$year))[1:3, ]
#> title genre year
#> 8 The Lamp at Noon classic 1999
#> 2 Pride and Prejudice classic 1813
#> 10 THE LONG TABLE fiction 2023
sort_by(shelf, shelf$year, decreasing = TRUE)[1:3, ]
#> title genre year
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
# In a pipe.
shelf |> sort_by(~ list(genre, title)) |> head(3)
#> title genre year
#> 2 Pride and Prejudice classic 1813
#> 8 The Lamp at Noon classic 1999
#> 3 the quiet streets fiction 2019
# The sum form is a terms formula: "- year" removes year, so the sort is
# silently ascending on year (compare the rows with ~ list(-year)).
sort_by(shelf, ~ -year)[1:3, ]
#> title genre year
#> 2 Pride and Prejudice classic 1813
#> 1 The Dispossessed science fiction 1974
#> 8 The Lamp at Noon classic 1999
sort_by(shelf, ~ list(-year))[1:3, ]
#> title genre year
#> 10 THE LONG TABLE fiction 2023
#> 15 Ten Winters fiction 2022
#> 4 Loud Roofs fiction 2021
# The default method sorts a vector by another vector.
sort_by(shelf$title, shelf$year)[1:3]
#> [1] "Pride and Prejudice" "The Dispossessed" "The Lamp at Noon"Source: R documentation: sort_by
Two points are not obvious. First, the formula ~ genre + year is read as a model formula, so - does not mean
"descending": ~ -year removes year from the terms and the data frame is sorted by year ascending, with no error and
no warning, as the next-to-last block shows (the first rows are 1813, 1974, 1999). To sort a key descending inside a formula,
use the ~ list() form with an expression, ~ list(genre, -year) or ~ list(-year); the first example of the help page
uses the same form. Second, decreasing = TRUE is a plain argument for order() and applies to every key, as with
order() itself. When the keys are text and the direction is mixed, -xtfrm(x) inside ~ list() still works.
Merging and joins
merge(x, y, by = ..., all = ...) joins two data frames on one or more key columns, as an SQL join does. By default
(by = intersect(names(x), names(y))) it uses the columns the two data frames have in common. The arguments that choose
the kind of join are all.x and all.y, with all as shorthand for both:
| SQL join | Arguments | Rows in the result |
|---|---|---|
inner join (natural join) |
defaults, |
only keys present in both |
left outer join |
|
every row of |
right outer join |
|
every row of |
full outer join |
|
every row of both |
cross join |
|
every combination, |
Source: R documentation: merge
The loans refer to books by ISBN, so the join is merge(loans, books, by = "isbn"), with one preparation: the ISBNs in
books.csv are written with and without hyphens and the ISBNs in loans.csv have none, so the key has to be normalised
first with normalise_isbn() from scenario.R, which keeps only digits and X. A join on keys that look the same but are
formatted differently silently matches nothing.
source("scenario.R")
books$isbn <- normalise_isbn(books$isbn)
loans$year <- as.integer(format(loans$loan_date, "%Y"))
# Inner join on a key: only keys present in both frames.
joined <- merge(loans, books, by = "isbn")
dim(joined)
#> [1] 59 11
names(joined)
#> [1] "isbn" "loan_id" "member" "loan_date" "return_date"
#> [6] "year.x" "title" "author" "year.y" "genre"
#> [11] "pages"
# year exists in both frames, so the non-key columns get suffixes.
joined2 <- merge(loans, books, by = "isbn", suffixes = c(".loan", ".published"))
head(joined2[c("loan_id", "title", "year.loan", "year.published")], 3)
#> loan_id title year.loan year.published
#> 1 L008 the quiet streets 2025 2019
#> 2 L050 the quiet streets 2026 2019
#> 3 L009 the quiet streets 2025 2019
# The result is sorted on the key; sort = FALSE keeps the order of loans.
head(joined$isbn, 3)
#> [1] "9780001000100" "9780001000100" "9780001000100"
head(merge(loans, books, by = "isbn", sort = FALSE)$loan_id, 3)
#> [1] "L028" "L046" "L001"
# Which loans found no book? An anti-join with %in% (a short ISBN here).
loans[!loans$isbn %in% books$isbn, c("loan_id", "isbn", "member")]
#> loan_id isbn member
#> 5 L005 978030640615 Ben OkaforSource: R documentation: merge
Fifty-nine of the 60 loans find a book. The unmatched one is L005, whose ISBN 978030640615 is one digit short, and the
anti-join with %in% finds it. Both data frames have a column named year (the year of the loan was added with
loans$year, the publication year is in books), so merge() appends the suffixes .x and .y to tell them apart; the
suffixes argument renames them, and, since R 3.5.0, no.dups = TRUE also suffixes a column of y whose name equals a by.x key. The result is sorted on the key columns (sort = TRUE), which is why the first rows are the loans of one book and
not L001; sort = FALSE leaves them in an unspecified order (here L028, L046, L001, which is not the order of loans either). The columns come back as
the key, then the remaining columns of x, then those of y.
source("scenario.R")
books$isbn <- normalise_isbn(books$isbn)
books$title <- trimws(books$title)
slim <- books[c("isbn", "title")]
# all.x = TRUE: a left join keeps every loan, with NA where no book matched.
left <- merge(loans, slim, by = "isbn", all.x = TRUE)
nrow(left)
#> [1] 60
left[is.na(left$title), c("loan_id", "isbn", "title")]
#> loan_id isbn title
#> 53 L005 978030640615 <NA>
# all.y = TRUE: a right join keeps every book, including those never loaned.
right <- merge(loans, slim, by = "isbn", all.y = TRUE)
right[is.na(right$loan_id), c("isbn", "title", "loan_id")]
#> isbn title loan_id
#> 1 Rivers of Glass <NA>
# all = TRUE: a full join.
c(inner = nrow(merge(loans, slim, by = "isbn")), left = nrow(left),
right = nrow(right), full = nrow(merge(loans, slim, by = "isbn", all = TRUE)))
#> inner left right full
#> 59 60 60 61
# by.x and by.y when the key columns have different names.
catalogue <- setNames(slim, c("book_isbn", "title"))
by_other_name <- merge(loans, catalogue, by.x = "isbn", by.y = "book_isbn")
head(by_other_name[c("loan_id", "isbn", "title")], 3)
#> loan_id isbn title
#> 1 L008 9780001000100 the quiet streets
#> 2 L050 9780001000100 the quiet streets
#> 3 L009 9780001000100 the quiet streets
# match() looks values up without a join: it keeps the rows of loans, in order.
loans$title <- slim$title[match(loans$isbn, slim$isbn)]
head(loans[c("loan_id", "title")], 3)
#> loan_id title
#> 1 L001 Pride and Prejudice
#> 2 L002 The Dispossessed
#> 3 L003 Loud RoofsSource: R documentation: merge
The row counts of the four joins are 59, 60, 60 and 61: the left join adds loan L005 with an NA title, the right join
adds Rivers of Glass, which has no ISBN and so was never loaned, and the full join adds both. Two alternatives are worth
knowing. by.x and by.y join columns with different names (the key column of the result takes the name of by.x). And
match() looks up one column of y for each row of x, as in slim$title[match(loans$isbn, slim$isbn)]: it keeps
the rows and the order of loans, never multiplies rows, and is much cheaper than a join when a single column is wanted
and the key is unique in y.
What can go wrong in a merge
source("scenario.R")
# Duplicate keys multiply: two copies of one book make two rows per loan.
copies <- data.frame(
isbn = c("9780141439518", "9780141439518", "9780306406157"),
copy = c("A", "B", "A")
)
loans_of_two <- loans[loans$isbn %in% copies$isbn, c("loan_id", "isbn")]
nrow(loans_of_two)
#> [1] 10
nrow(merge(loans_of_two, copies, by = "isbn"))
#> [1] 13
# NA keys match each other by default; incomparables = NA stops that.
a <- data.frame(isbn = c("x", NA), left = 1:2)
b <- data.frame(isbn = c("x", NA), right = 3:4)
merge(a, b, by = "isbn")
#> isbn left right
#> 1 x 1 3
#> 2 <NA> 2 4
merge(a, b, by = "isbn", incomparables = NA)
#> isbn left right
#> 1 x 1 3
# A key that is not a column of both frames.
tryCatch(merge(loans, a, by = "member"), error = conditionMessage)
#> [1] "'by' must specify a uniquely valid column"
# by = NULL (or character(0)) gives every combination: a cross join.
dim(merge(data.frame(shelf = c("A", "B")), data.frame(slot = 1:3), by = NULL))
#> [1] 6 2Source: R documentation: merge
-
Duplicate keys multiply. The help page says that if there is more than one match, all possible matches contribute one row each. Joining
loans_of_two(10 loans) tocopies(two copies of one book and one of another) gives 13 rows, not 10. A join result with more rows than either input is the sign of duplicate keys; check withanyDuplicated(y$key)before a join that is meant to add a column. -
NAkeys match each other. The ISBNNAinamatched theNAinb. Databases do not matchNULLrecords, and the help page notes that this corresponds toincomparables = NAin R. Remove the missing keys first or passincomparables = NA. -
A key missing from either frame.
by = "member"fails with'by' must specify a uniquely valid columnwhenmemberis not a column of both data frames (or of the pair named byby.xandby.y). -
Row order is lost. The default
sort = TRUEre-sorts the result on the key. If the original order matters, usematch()for a lookup, or keep a row-number column and sort again. -
Cross joins are quadratic.
by = NULLreturnsnrow(x) * nrow(y)rows (6 for the two-by-three example above).
Several summaries by the same key are combined by folding merge() with Reduce():
source("scenario.R")
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
end <- loans$return_date
end[is.na(end)] <- shelf_today
loans$held <- as.integer(end - loans$loan_date)
loans$overdue <- loans$held > loan_days
# Several summaries by member, merged one after the other with Reduce().
per_member <- list(
loans = aggregate(loan_id ~ member, data = loans, FUN = length),
overdue = aggregate(overdue ~ member, data = loans, FUN = sum),
held = aggregate(held ~ member, data = loans, FUN = mean)
)
Reduce(\(x, y) merge(x, y, by = "member", all = TRUE), per_member)
#> member loan_id overdue held
#> 1 Ana Ruiz 14 8 25.30769
#> 2 Ben Okafor 5 3 23.20000
#> 3 Chloe Martin 9 6 27.33333
#> 4 Dev Patel 6 4 33.66667
#> 5 Eva Lindqvist 6 2 27.00000
#> 6 Farid Karimi 2 0 10.00000
#> 7 Grace Liu 7 4 21.28571
#> 8 Hugo Silva 10 5 22.80000The aggregate() calls in that script are explained in the next section. They use the formula method, which drops rows with
NA in any variable it uses, so the loan with the empty member (L033) never appears, and the held column is
averaged over the 58 loans that have both a member and a loan date; the section on aggregate() below explains the na.action that does this.
Grouping and summarising
"Compute something for each group" is the most common operation on a table, and R has one underlying pattern and a
family of shortcuts. The pattern is split-apply-combine: split the data frame into pieces by a key, apply a function to
each piece, and combine the results. split() does the first step, lapply() or vapply() the second, and
do.call(rbind, ...) (for data frames) or unlist() (for scalars) the third. The base functions aggregate(),
tapply(), by(), ave() and rowsum() package the pattern for the common cases.
Source: R documentation: split
The solid path is the explicit version: three steps that can each be inspected. The dotted paths are the shortcuts that
do all the steps in one call: aggregate(), tapply() and by() collapse each group to a summary, while ave() returns
a vector as long as the input, one value per original row, so it can be assigned back as a new column. The explicit form
is the one to reach for when the function per group is not a simple summary (the top two books of every genre, a model per
group), and the shortcut when it is.
split(), lapply() and do.call(rbind, …)
split(x, f) takes a vector or a data frame x and a factor (or a list of factors, or, since R 4.1.0 for data frames, a
formula such as ~ genre) and returns a named list with one element per level. unsplit(value, f) reverses it. The
function applied to each piece decides what the combine step looks like: a data frame per group gives
do.call(rbind, pieces); one number per group gives vapply(pieces, f, numeric(1)).
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
# split(): a data frame becomes a named list of data frames, one per group.
by_genre <- split(books, books$genre)
names(by_genre)
#> [1] "classic" "fiction" "mystery" "non-fiction"
#> [5] "poetry" "science fiction"
by_genre$poetry[c("title", "pages")]
#> title pages
#> 7 Garden of Small Fires 96
#> 11 Paper Moons 88
# apply a function to every piece...
pieces <- lapply(by_genre, \(d) {
data.frame(
genre = d$genre[[1L]],
n = nrow(d),
mean_pages = mean(d$pages, na.rm = TRUE)
)
})
# ...and combine. do.call(rbind, ...) uses the list names in the row names.
combined <- do.call(rbind, pieces)
combined
#> genre n mean_pages
#> classic classic 2 356.0000
#> fiction fiction 4 222.6667
#> mystery mystery 2 272.5000
#> non-fiction non-fiction 3 263.5000
#> poetry poetry 2 92.0000
#> science fiction science fiction 3 373.0000
rownames(combined) <- NULL
combined
#> genre n mean_pages
#> 1 classic 2 356.0000
#> 2 fiction 4 222.6667
#> 3 mystery 2 272.5000
#> 4 non-fiction 3 263.5000
#> 5 poetry 2 92.0000
#> 6 science fiction 3 373.0000
# split() also takes a formula (R 4.1.0). unsplit() puts the pieces back in the
# original row order, but its row names are text until they are reset.
names(split(books, ~ genre))
#> [1] "classic" "fiction" "mystery" "non-fiction"
#> [5] "poetry" "science fiction"
restored <- unsplit(by_genre, books$genre)
identical(restored, books)
#> [1] FALSE
rownames(restored) <- NULL
identical(restored, books)
#> [1] TRUESource: R documentation: split
do.call(rbind, pieces) uses the names of the list as a prefix for the row names (classic, fiction, …) and, when a
piece has several rows, appends the original row names (classic.2, classic.8); resetting them with
rownames(combined) <- NULL is the usual last step. unsplit() puts the pieces back in the original row order, but its
row names are character strings until they are reset, which is why the first identical() is FALSE and the second TRUE.
split() drops nothing by default: a factor level with no rows gives an empty data frame in the list, and drop = TRUE
removes those pieces.
The scenario’s headline question, overdue loans per member, is the same pattern three ways:
source("scenario.R")
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
end <- loans$return_date
end[is.na(end)] <- shelf_today
loans$held <- as.integer(end - loans$loan_date)
loans$overdue <- loans$held > loan_days
# Overdue loans per member, three ways. All of them leave out the loan that
# has no member.
counts <- aggregate(overdue ~ member, data = loans, FUN = sum)
counts[order(-counts$overdue, counts$member), ]
#> member overdue
#> 1 Ana Ruiz 8
#> 3 Chloe Martin 6
#> 8 Hugo Silva 5
#> 4 Dev Patel 4
#> 7 Grace Liu 4
#> 2 Ben Okafor 3
#> 5 Eva Lindqvist 2
#> 6 Farid Karimi 0
pieces <- lapply(split(loans, loans$member), \(d) {
data.frame(member = d$member[[1L]], overdue = sum(d$overdue, na.rm = TRUE))
})
by_hand <- do.call(rbind, unname(pieces))
by_hand[order(-by_hand$overdue, by_hand$member), ]
#> member overdue
#> 1 Ana Ruiz 8
#> 3 Chloe Martin 6
#> 8 Hugo Silva 5
#> 4 Dev Patel 4
#> 7 Grace Liu 4
#> 2 Ben Okafor 3
#> 5 Eva Lindqvist 2
#> 6 Farid Karimi 0
tapply(loans$overdue, loans$member, sum, na.rm = TRUE)
#> Ana Ruiz Ben Okafor Chloe Martin Dev Patel Eva Lindqvist
#> 8 3 6 4 2
#> Farid Karimi Grace Liu Hugo Silva
#> 0 4 5
# A named list returned by split() is easier to keep as a vector with vapply().
vapply(split(loans$overdue, loans$member), sum, integer(1), na.rm = TRUE)
#> Ana Ruiz Ben Okafor Chloe Martin Dev Patel Eva Lindqvist
#> 8 3 6 4 2
#> Farid Karimi Grace Liu Hugo Silva
#> 0 4 5Source: R documentation: lapply
Ana Ruiz has 8 overdue loans, Chloe Martin 6 and Hugo Silva 5; Farid Karimi has none. The three results agree. All three
leave out the loan with no member, and sum(d$overdue, na.rm = TRUE) leaves out the loan with the impossible date.
aggregate()
aggregate() is the one-call version for summaries. It has two calling forms.
-
Formula:
aggregate(pages ~ genre, data = books, FUN = mean). The left side names what to summarise (cbind(pages, year)for several columns, or.for all the others), the right side names the grouping columns (genre + decadefor several), anddatasupplies them. A formula is the readable form, but it appliesna.action = na.omitby default: any row with anNAin any variable of the formula is dropped before the function sees it. -
Non-formula:
aggregate(x, by = list(genre = books$genre), FUN = mean), wherexis a vector or a data frame andbyis a named list of grouping vectors (a data frame of keys works too). No rows are dropped:NAvalues reachFUN, and a group whose key isNAis left out of the result.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
# Formula form: left side = what to summarise, right side = how to group.
aggregate(pages ~ genre, data = books, FUN = mean)
#> genre pages
#> 1 classic 356.0000
#> 2 fiction 222.6667
#> 3 mystery 272.5000
#> 4 non-fiction 263.5000
#> 5 poetry 92.0000
#> 6 science fiction 373.0000
# Several columns on the left, and na.action = na.omit drops whole rows:
# the poetry mean of pages is 88 here, not 92, because the row with the
# missing year is gone.
aggregate(cbind(pages, year) ~ genre, data = books, FUN = mean)
#> genre pages year
#> 1 classic 356.0000 1906.000
#> 2 fiction 222.6667 2021.000
#> 3 mystery 272.5000 2017.000
#> 4 non-fiction 263.5000 2016.000
#> 5 poetry 88.0000 2005.000
#> 6 science fiction 373.0000 2001.667
aggregate(. ~ genre, data = books[c("genre", "year", "pages")], FUN = mean)
#> genre year pages
#> 1 classic 1906.000 356.0000
#> 2 fiction 2021.000 222.6667
#> 3 mystery 2017.000 272.5000
#> 4 non-fiction 2016.000 263.5000
#> 5 poetry 2005.000 88.0000
#> 6 science fiction 2001.667 373.0000
# na.rm = TRUE is passed on to FUN; na.action = na.pass keeps all rows.
aggregate(
pages ~ genre,
data = books, FUN = mean, na.action = na.pass, na.rm = TRUE
)
#> genre pages
#> 1 classic 356.0000
#> 2 fiction 222.6667
#> 3 mystery 272.5000
#> 4 non-fiction 263.5000
#> 5 poetry 92.0000
#> 6 science fiction 373.0000
# A function that returns several values makes a matrix column.
by_range <- aggregate(pages ~ genre, data = books, FUN = range)
str(by_range)
#> 'data.frame': 6 obs. of 2 variables:
#> $ genre: chr "classic" "fiction" "mystery" "non-fiction" ...
#> $ pages: int [1:6, 1:2] 280 198 244 175 88 322 432 256 301 352 ...
do.call(data.frame, by_range)
#> genre pages.1 pages.2
#> 1 classic 280 432
#> 2 fiction 198 256
#> 3 mystery 244 301
#> 4 non-fiction 175 352
#> 5 poetry 88 96
#> 6 science fiction 322 410Source: R documentation: aggregate
The difference is visible in the poetry row: cbind(pages, year) ~ genre gives a mean of 88 pages, because the row for
Garden of Small Fires (96 pages) has no year and was dropped as a whole, while the single-column formula gives 92. The
help page recommends na.action = na.pass with na.rm = TRUE to handle missing values per variable, as the fourth call
does. A function that returns several values (here range) produces a matrix column: str() shows
$ pages: int [1:6, 1:2], and do.call(data.frame, result) flattens it into pages.1 and pages.2. The NEWS entry for R
4.2.0 notes that the first argument of the formula method is now named x, so a call that names it must now use the name x
(earlier versions needed formula), and portable code should leave it unnamed.
Source: R NEWS: Changes in R 4.2.0
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
# Non-formula form: x, then by = a named list of grouping vectors.
aggregate(books$pages, by = list(genre = books$genre), FUN = mean)
#> genre x
#> 1 classic 356.0
#> 2 fiction NA
#> 3 mystery 272.5
#> 4 non-fiction NA
#> 5 poetry 92.0
#> 6 science fiction 373.0
# No rows are dropped, so NA propagate unless FUN handles them.
aggregate(books$pages, by = list(genre = books$genre), FUN = mean, na.rm = TRUE)
#> genre x
#> 1 classic 356.0000
#> 2 fiction 222.6667
#> 3 mystery 272.5000
#> 4 non-fiction 263.5000
#> 5 poetry 92.0000
#> 6 science fiction 373.0000
# x may be a data frame; by may be a data frame (or a list) of several keys.
books$decade <- books$year %/% 10L * 10L
aggregate(
books["pages"],
by = books[c("genre", "decade")], FUN = sum, na.rm = TRUE
)
#> genre decade pages
#> 1 classic 1810 432
#> 2 science fiction 1970 387
#> 3 classic 1990 280
#> 4 non-fiction 2000 0
#> 5 poetry 2000 88
#> 6 fiction 2010 214
#> 7 mystery 2010 545
#> 8 non-fiction 2010 352
#> 9 science fiction 2010 732
#> 10 fiction 2020 454
#> 11 non-fiction 2020 175
# Rows whose group is NA are not reported.
nrow(books)
#> [1] 16
sum(aggregate(books["pages"], by = books["decade"], FUN = length)$pages)
#> [1] 15Source: R documentation: aggregate
With the non-formula form the default summary column is named x (give a data frame as x to keep the column names), the
fiction and non-fiction means are NA until na.rm = TRUE is passed to FUN, and the groups are ordered with the first key varying
fastest (all genres of the earliest decade first). The last two lines show that rows whose key is NA are not reported: the 16 books yield 15 counted in
the decade groups, because the book with no year has no decade.
tapply() and by()
tapply(X, INDEX, FUN) applies FUN to the groups of a vector formed by one factor (the result is a named vector) or by
several (the result is an array, a matrix for two factors, with NA in the cells that have no data). simplify = FALSE
returns a list, and default = 0 replaces the NA of the empty cells (for functions such as length() where zero is the
right value). Since R 4.3.0 tapply() accepts a data frame as X with a formula as INDEX, and by() accepts a
formula as INDICES. by(data, INDICES, FUN) is the data frame counterpart: FUN receives the sub-data-frame, and the
result is an object of class "by" that prints one block per group. Bind its data frame results back with
do.call(rbind, ...).
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
books$decade <- books$year %/% 10L * 10L
# tapply(): one vector, one or more factors, a function. The result is an
# array: a named vector for one factor, a matrix for two.
tapply(books$pages, books$genre, mean, na.rm = TRUE)
#> classic fiction mystery non-fiction poetry
#> 356.0000 222.6667 272.5000 263.5000 92.0000
#> science fiction
#> 373.0000
tapply(books$pages, list(books$genre, books$decade >= 2000), sum, na.rm = TRUE)
#> FALSE TRUE
#> classic 712 NA
#> fiction NA 668
#> mystery NA 545
#> non-fiction NA 527
#> poetry NA 88
#> science fiction 387 732
# simplify = FALSE returns a list; default = 0 fills the empty cells.
ranges <- tapply(
books$pages, books$genre, range,
na.rm = TRUE, simplify = FALSE
)
ranges[["poetry"]]
#> [1] 88 96
modern <- books$decade >= 2000
tapply(books$pages, list(books$genre, modern), length, default = 0L)
#> FALSE TRUE
#> classic 2 0
#> fiction 0 4
#> mystery 0 2
#> non-fiction 0 3
#> poetry 0 1
#> science fiction 1 2
# The formula method of tapply() (R 4.3.0) takes a data frame.
tapply(books, ~ genre, \(d) nrow(d))
#> genre
#> classic fiction mystery non-fiction poetry
#> 2 4 2 3 2
#> science fiction
#> 3
# by(): the function receives the sub-data-frames, and the result prints
# with a header per group.
by(books, books$genre, \(d) mean(d$pages, na.rm = TRUE))
#> books$genre: classic
#> [1] 356
#> ------------------------------------------------------------
#> books$genre: fiction
#> [1] 222.6667
#> ------------------------------------------------------------
#> books$genre: mystery
#> [1] 272.5
#> ------------------------------------------------------------
#> books$genre: non-fiction
#> [1] 263.5
#> ------------------------------------------------------------
#> books$genre: poetry
#> [1] 92
#> ------------------------------------------------------------
#> books$genre: science fiction
#> [1] 373
# A by object whose pieces are data frames binds back with do.call(rbind, ...).
sizes <- by(books, books$genre, \(d) {
data.frame(genre = d$genre[[1L]], n = nrow(d))
})
do.call(rbind, sizes)
#> genre n
#> classic classic 2
#> fiction fiction 4
#> mystery mystery 2
#> non-fiction non-fiction 3
#> poetry poetry 2
#> science fiction science fiction 3
# An argument length mismatch is an error.
tryCatch(tapply(books$pages, books$genre[1:3], mean), error = conditionMessage)
#> [1] "arguments must have same length"Source: R documentation: tapply
The help page of by() is R documentation: by. The error
arguments must have same length is the one tapply() gives when the grouping vector is not as long as the data.
ave(): group statistics as a column
ave(x, ..., FUN = mean) returns a vector the same length as x in which every element is replaced by FUN of its
group. That is the tool for "the mean of the group on every row", for filling a missing value with the group mean, and for
a running count within each group: with FUN = cumsum it is a cumulative sum by group, and with seq_along it is the
position within the group. A row whose grouping value is NA is left as it was.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
# ave(): a group summary as long as the input (a window, not a collapse).
mean_na <- \(x) mean(x, na.rm = TRUE)
books$genre_mean <- ave(books$pages, books$genre, FUN = mean_na)
books[c("title", "genre", "pages", "genre_mean")][c(1, 3, 4, 7, 11), ]
#> title genre pages genre_mean
#> 1 The Dispossessed science fiction 387 373.0000
#> 3 the quiet streets fiction 214 222.6667
#> 4 Loud Roofs fiction 198 222.6667
#> 7 Garden of Small Fires poetry 96 92.0000
#> 11 Paper Moons poetry 88 92.0000
# Fill the missing pages with the genre mean.
books$pages_filled <- ifelse(
is.na(books$pages), round(books$genre_mean), books$pages
)
books[is.na(books$pages), c("title", "pages", "pages_filled")]
#> title pages pages_filled
#> 6 A Map of Salt NA 264
#> 15 Ten Winters NA 223
# Running count per member, in date order: cumsum() by group. A row whose
# group is NA is left alone by ave(), so it is dropped first.
by_date <- loans[order(loans$member, loans$loan_date), ]
by_date <- by_date[!is.na(by_date$member), ]
by_date$nth_loan <- ave(rep(1L, nrow(by_date)), by_date$member, FUN = cumsum)
head(by_date[c("loan_id", "member", "loan_date", "nth_loan")], 5)
#> loan_id member loan_date nth_loan
#> 8 L008 Ana Ruiz 2025-03-27 1
#> 14 L014 Ana Ruiz 2025-05-07 2
#> 15 L015 Ana Ruiz 2025-06-04 3
#> 19 L019 Ana Ruiz 2025-07-02 4
#> 28 L028 Ana Ruiz 2025-09-18 5
# The same with seq_along(), no cumsum() needed.
rows <- seq_len(nrow(by_date))
by_date$nth_again <- ave(rows, by_date$member, FUN = seq_along)
identical(by_date$nth_loan, by_date$nth_again)
#> [1] TRUESource: R documentation: ave
Filling the missing page counts with the genre mean (264 and 223) is a modelling choice, not a cleaning step; it is
shown to demonstrate the mechanism. The running count requires the data frame to be sorted first by member and date; ave()
works in the order of the rows it is given.
table(), xtabs() and rowsum()
table() counts combinations of one or more factors; useNA = "ifany" shows the NA that table() would otherwise hide.
xtabs() takes a formula: ~ genre counts, and pages ~ genre sums a numeric column by group. as.data.frame() turns the
table into a long data frame with a Freq column, prop.table() into proportions, addmargins() adds totals. A sum with
a missing value in it is printed by xtabs() as a blank cell, not as NA, which is easy to read as zero; ask for
na.rm = TRUE.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
# table(): counts of one or two factors; useNA makes the NA visible.
table(books$genre)
#>
#> classic fiction mystery non-fiction poetry
#> 2 4 2 3 2
#> science fiction
#> 3
books$decade <- books$year %/% 10L * 10L
table(books$genre, books$decade, useNA = "ifany")
#>
#> 1810 1970 1990 2000 2010 2020 <NA>
#> classic 1 0 1 0 0 0 0
#> fiction 0 0 0 0 1 3 0
#> mystery 0 0 0 0 2 0 0
#> non-fiction 0 0 0 1 1 1 0
#> poetry 0 0 0 1 0 0 1
#> science fiction 0 1 0 0 2 0 0
# xtabs(): the same from a formula; a left side sums a numeric column. A sum
# that contains NA is printed as a blank cell, so ask for na.rm = TRUE.
xtabs(~ genre, data = books)
#> genre
#> classic fiction mystery non-fiction poetry
#> 2 4 2 3 2
#> science fiction
#> 3
xtabs(pages ~ genre, data = books)
#> genre
#> classic fiction mystery non-fiction poetry
#> 712 545 184
#> science fiction
#> 1119
xtabs(pages ~ genre, data = books, na.rm = TRUE)
#> genre
#> classic fiction mystery non-fiction poetry
#> 712 668 545 527 184
#> science fiction
#> 1119
# A table converts to a long data frame, ready for further processing.
head(as.data.frame(table(genre = books$genre, decade = books$decade)), 4)
#> genre decade Freq
#> 1 classic 1810 1
#> 2 fiction 1810 0
#> 3 mystery 1810 0
#> 4 non-fiction 1810 0
# proportions and margins
round(prop.table(table(books$genre)), 2)
#>
#> classic fiction mystery non-fiction poetry
#> 0.12 0.25 0.12 0.19 0.12
#> science fiction
#> 0.19
addmargins(table(books$genre, books$decade >= 2000))
#>
#> FALSE TRUE Sum
#> classic 2 0 2
#> fiction 0 4 4
#> mystery 0 2 2
#> non-fiction 0 3 3
#> poetry 0 1 1
#> science fiction 1 2 3
#> Sum 3 12 15Source: R documentation: xtabs
rowsum(x, group) is a fast grouped sum for vectors, matrices and data frames; the result has one row per group, sorted by
group, with the group names as row names. interaction(f1, f2) makes one factor with a level for every combination, to be
used as a single key; drop = TRUE removes the combinations that never occur. split() and tapply() accept a list of
factors, so interaction() is needed only when a single key column is wanted.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
books$decade <- books$year %/% 10L * 10L
# rowsum(): fast grouped sums, sorted by group, for vectors, matrices and
# data frames.
rowsum(books$pages, books$genre, na.rm = TRUE)
#> [,1]
#> classic 712
#> fiction 668
#> mystery 545
#> non-fiction 527
#> poetry 184
#> science fiction 1119
rowsum(books[c("year", "pages")], books$genre, na.rm = TRUE)
#> year pages
#> classic 3812 712
#> fiction 8085 668
#> mystery 4034 545
#> non-fiction 6040 527
#> poetry 2005 184
#> science fiction 6005 1119
# interaction(): one factor for every combination, to use as a single key.
key <- interaction(books$genre, books$decade >= 2000, drop = TRUE)
levels(key)
#> [1] "classic.FALSE" "science fiction.FALSE" "fiction.TRUE"
#> [4] "mystery.TRUE" "non-fiction.TRUE" "poetry.TRUE"
#> [7] "science fiction.TRUE"
tapply(books$pages, key, sum, na.rm = TRUE)
#> classic.FALSE science fiction.FALSE fiction.TRUE
#> 712 387 668
#> mystery.TRUE non-fiction.TRUE poetry.TRUE
#> 545 527 88
#> science fiction.TRUE
#> 732
# split() with a list of factors does the same, and drop = TRUE removes the
# empty groups.
pieces <- split(books, list(books$genre, books$decade >= 2000), drop = TRUE)
sizes <- vapply(pieces, nrow, integer(1))
sizes
#> classic.FALSE science fiction.FALSE fiction.TRUE
#> 2 1 4
#> mystery.TRUE non-fiction.TRUE poetry.TRUE
#> 2 3 1
#> science fiction.TRUE
#> 2Source: R documentation: rowsum
Reshaping: long and wide
A table is long when each row is one observation (one member in one quarter) and wide when one of the identifying
variables is spread across columns (one row per member, one column per quarter). Reports are usually wide and analysis
is usually long. Base R converts between them with reshape(), and with the simpler stack() and unstack().
The wide table of loans per member per quarter has two routes. table() makes it directly from the rows, with a zero for
every combination that does not occur. aggregate() makes the long form with only the combinations that do occur.
source("scenario.R")
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
# Quarter of the loan date, e.g. "2025-Q3"; rows without a date or member go.
loans <- loans[!is.na(loans$loan_date) & !is.na(loans$member), ]
year <- format(loans$loan_date, "%Y")
loans$quarter <- paste(year, quarters(loans$loan_date), sep = "-")
# The wide table straight from table(): members down, quarters across.
table(loans$member, loans$quarter)
#>
#> 2025-Q1 2025-Q2 2025-Q3 2025-Q4 2026-Q1 2026-Q2
#> Ana Ruiz 1 2 2 4 2 2
#> Ben Okafor 2 1 1 1 0 0
#> Chloe Martin 0 2 2 2 2 1
#> Dev Patel 1 0 1 1 1 2
#> Eva Lindqvist 1 0 3 1 0 1
#> Farid Karimi 0 0 0 1 1 0
#> Grace Liu 1 0 2 1 3 0
#> Hugo Silva 3 3 1 1 1 1
# The long form of the same counts: one row per member and quarter that occurs.
counts <- aggregate(loan_id ~ member + quarter, data = loans, FUN = length)
names(counts)[names(counts) == "loan_id"] <- "n"
head(counts, 4)
#> member quarter n
#> 1 Ana Ruiz 2025-Q1 1
#> 2 Ben Okafor 2025-Q1 2
#> 3 Dev Patel 2025-Q1 1
#> 4 Eva Lindqvist 2025-Q1 1
nrow(counts)
#> [1] 36Source: R documentation: table
reshape(): long to wide
reshape(data, direction = "wide", idvar, timevar, v.names) takes the rows of the long form: idvar names the column that
identifies a unit (member), timevar the column whose values become the new column suffixes (quarter), and v.names
the column(s) holding the values (n). The new columns are called v.names, a separator (sep = "." by default) and the
value of timevar. A combination that does not occur is NA.
source("scenario.R")
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
loans <- loans[!is.na(loans$loan_date) & !is.na(loans$member), ]
year <- format(loans$loan_date, "%Y")
loans$quarter <- paste(year, quarters(loans$loan_date), sep = "-")
counts <- aggregate(loan_id ~ member + quarter, data = loans, FUN = length)
names(counts)[names(counts) == "loan_id"] <- "n"
counts <- counts[order(counts$member, counts$quarter), ]
# Long to wide: one row per idvar, one column per timevar value.
wide <- reshape(
counts,
direction = "wide", idvar = "member", timevar = "quarter", v.names = "n"
)
wide
#> member n.2025-Q1 n.2025-Q2 n.2025-Q3 n.2025-Q4 n.2026-Q1 n.2026-Q2
#> 1 Ana Ruiz 1 2 2 4 2 2
#> 2 Ben Okafor 2 1 1 1 NA NA
#> 9 Chloe Martin NA 2 2 2 2 1
#> 3 Dev Patel 1 NA 1 1 1 2
#> 4 Eva Lindqvist 1 NA 3 1 NA 1
#> 23 Farid Karimi NA NA NA 1 1 NA
#> 5 Grace Liu 1 NA 2 1 3 NA
#> 6 Hugo Silva 3 3 1 1 1 1
names(wide)
#> [1] "member" "n.2025-Q1" "n.2025-Q2" "n.2025-Q3" "n.2025-Q4" "n.2026-Q1"
#> [7] "n.2026-Q2"
# Combinations that never occur are NA; fill them with zero.
wide[is.na(wide)] <- 0L
wide[, -1L]
#> n.2025-Q1 n.2025-Q2 n.2025-Q3 n.2025-Q4 n.2026-Q1 n.2026-Q2
#> 1 1 2 2 4 2 2
#> 2 2 1 1 1 0 0
#> 9 0 2 2 2 2 1
#> 3 1 0 1 1 1 2
#> 4 1 0 3 1 0 1
#> 23 0 0 0 1 1 0
#> 5 1 0 2 1 3 0
#> 6 3 3 1 1 1 1
# reshape() records how it was made, so it can be undone with no arguments.
back <- reshape(wide)
head(back, 3)
#> member quarter n
#> Ana Ruiz.2025-Q1 Ana Ruiz 2025-Q1 1
#> Ben Okafor.2025-Q1 Ben Okafor 2025-Q1 2
#> Chloe Martin.2025-Q1 Chloe Martin 2025-Q1 0
attr(wide, "reshapeWide")$timevar
#> [1] "quarter"Source: R documentation: reshape
Three details of the output. The row names of the wide data frame are the row names of the first row of each member in the
long data frame (1, 2, 9, 3, …), so reset them with rownames(wide) <- NULL if they are shown. The columns keep the
hyphen of 2025-Q1 in their names (n.2025-Q1), which is not a syntactic name, so any code that names the column must put it in backquotes;
renaming the quarters to q2025_1 avoids that. And reshape() stores the instructions it used in the attribute
reshapeWide, so reshape(wide) with no other argument undoes the reshaping: the
rows come back with row names like Ana Ruiz.2025-Q1, and the NA that were replaced by zero come back as zeros, not as
missing rows.
reshape(): wide to long
For the other direction direction = "long" needs varying, the names (or positions) of the columns to stack, v.names
for the new value column, timevar for the new column that records where each value came from, times for its values,
and idvar for the identifying column. When the column names follow the pattern name.time, a character vector for varying is enough and sep splits each name into
the value name and the time; varying may be a list of vectors when several measurements are stacked together.
source("scenario.R")
# A small wide table: loans per member and half-year (made by hand here).
wide <- data.frame(
member = c("Ana Ruiz", "Ben Okafor", "Hugo Silva"),
h1_2025 = c(3L, 3L, 6L),
h2_2025 = c(8L, 3L, 2L),
h1_2026 = c(4L, 0L, 2L)
)
# Wide to long: varying names the columns to stack, v.names names the new value
# column, timevar the new column that says which wide column a row came from.
long <- reshape(
wide,
direction = "long",
idvar = "member",
varying = c("h1_2025", "h2_2025", "h1_2026"),
v.names = "loans",
timevar = "half",
times = c("h1_2025", "h2_2025", "h1_2026")
)
long
#> member half loans
#> Ana Ruiz.h1_2025 Ana Ruiz h1_2025 3
#> Ben Okafor.h1_2025 Ben Okafor h1_2025 3
#> Hugo Silva.h1_2025 Hugo Silva h1_2025 6
#> Ana Ruiz.h2_2025 Ana Ruiz h2_2025 8
#> Ben Okafor.h2_2025 Ben Okafor h2_2025 3
#> Hugo Silva.h2_2025 Hugo Silva h2_2025 2
#> Ana Ruiz.h1_2026 Ana Ruiz h1_2026 4
#> Ben Okafor.h1_2026 Ben Okafor h1_2026 0
#> Hugo Silva.h1_2026 Hugo Silva h1_2026 2
rownames(long) <- NULL
long
#> member half loans
#> 1 Ana Ruiz h1_2025 3
#> 2 Ben Okafor h1_2025 3
#> 3 Hugo Silva h1_2025 6
#> 4 Ana Ruiz h2_2025 8
#> 5 Ben Okafor h2_2025 3
#> 6 Hugo Silva h2_2025 2
#> 7 Ana Ruiz h1_2026 4
#> 8 Ben Okafor h1_2026 0
#> 9 Hugo Silva h1_2026 2
# Groups of columns of different lengths cannot be stacked.
tryCatch(
reshape(
wide,
direction = "long", idvar = "member",
varying = list(2:3, 4L), v.names = c("a", "b", "c")
),
error = conditionMessage
)
#> [1] "'varying' arguments must be the same length"Source: R documentation: reshape
The row names of the long form are id.time pairs (Ana Ruiz.h1_2025), which is useful for tracing and safe to reset.
The error in the last block is 'varying' arguments must be the same length: when varying is a list, every element must
list the same number of columns. The argument names of reshape() come from the setting of repeated measurements over time; it is verbose, but it does both directions and remembers how it reshaped.
stack(), unstack() and why not t()
stack(x) takes a list or the columns of a data frame and returns a two-column data frame values and ind, with ind
a factor naming the original column. unstack() reverses it, returning a data frame when the groups have equal lengths
and a list when they do not. These are the right tools for plotting or modelling several columns together, and for turning
a list of vectors into a table.
A data frame is not a matrix, and t() is the wrong way to turn rows into columns. t(df) first converts the data frame
with as.matrix(): if any column is not numeric the whole matrix becomes character, the numbers are formatted as text,
and the result has lost its column types. For numeric columns only, t() gives a numeric matrix, which is correct, but
it is a matrix and no longer a data frame. data.matrix() converts a data frame to a numeric matrix by turning character
columns into integer codes of a factor, which is almost never what is meant (the title column of the last example became
the codes 3, 2, 1). To turn the rows of a data frame into columns, use reshape(), or as.data.frame(t(...)) after
selecting only the columns that can be transposed.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
# stack(): columns of a data frame (or elements of a list) into values + ind.
stacked <- stack(books[c("year", "pages")])
head(stacked, 3)
#> values ind
#> 1 1974 year
#> 2 1813 year
#> 3 2019 year
table(stacked$ind)
#>
#> year pages
#> 16 16
tail(stacked, 2)
#> values ind
#> 31 NA pages
#> 32 244 pages
# unstack() reverses it when the groups have equal length; otherwise a list.
unstack(stacked)[1:3, ]
#> year pages
#> 1 1974 387
#> 2 1813 432
#> 3 2019 214
str(unstack(stack(split(books$pages, books$genre))))
#> List of 6
#> $ classic : int [1:2] 432 280
#> $ fiction : int [1:4] 214 198 256 NA
#> $ mystery : int [1:2] 301 244
#> $ non-fiction : int [1:3] NA 352 175
#> $ poetry : int [1:2] 96 88
#> $ science fiction: int [1:3] 387 410 322
# t() on a data frame converts it to a character matrix first.
t(books[1:3, c("title", "year", "pages")])
#> 1 2 3
#> title "The Dispossessed" "Pride and Prejudice" " the quiet streets "
#> year "1974" "1813" "2019"
#> pages "387" "432" "214"
str(t(books[1:3, c("year", "pages")]))
#> int [1:2, 1:3] 1974 387 1813 432 2019 214
#> - attr(*, "dimnames")=List of 2
#> ..$ : chr [1:2] "year" "pages"
#> ..$ : chr [1:3] "1" "2" "3"
class(as.matrix(books[1:3, c("year", "pages")]))
#> [1] "matrix" "array"
data.matrix(books[1:3, c("title", "year")])
#> title year
#> 1 3 1974
#> 2 2 1813
#> 3 1 2019Source: R documentation: stack
Duplicates
duplicated(x) returns TRUE for every element that already occurred earlier in x; fromLast = TRUE scans from the
end instead; unique(x) returns the distinct values, in order of first appearance; anyDuplicated(x) returns the index
of the first duplicate, or 0 when there is none, which is the cheapest test of "is this key unique?". All three have data
frame methods that compare rows, over all columns or over a subset of columns selected first
(duplicated(books[c("author", "genre")])). A key that must be unique is checked with anyDuplicated(df$key) == 0L.
source("scenario.R")
books$author <- gsub("\\s+", " ", trimws(books$author))
books$author[books$author == "Le Guin, Ursula K."] <- "Ursula K. Le Guin"
books$genre <- tolower(trimws(books$genre))
# duplicated() flags the second and later occurrences; unique() keeps the first.
duplicated(books$author)
#> [1] FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE
#> [13] TRUE FALSE TRUE TRUE
unique(books$author)
#> [1] "Ursula K. Le Guin" "Jane Austen" "Mara Quill"
#> [4] "Iris Fenwick" "Tomas Arden" "Noor Haddad"
#> [7] "Elias Moreau" "Priya Raman"
anyDuplicated(books$author)
#> [1] 4
anyDuplicated(books$isbn)
#> [1] 0
# On a data frame, duplicated() compares whole rows (or the columns you pass).
sum(duplicated(books))
#> [1] 0
repeat_authors <- duplicated(books$author) |
duplicated(books$author, fromLast = TRUE)
head(books[repeat_authors, c("author", "title")], 4)
#> author title
#> 1 Ursula K. Le Guin The Dispossessed
#> 3 Mara Quill the quiet streets
#> 4 Mara Quill Loud Roofs
#> 5 Iris Fenwick Midnight Ledger
# The first book of every author, and the number of books per author.
first_books <- books[!duplicated(books$author), c("author", "title")]
nrow(first_books)
#> [1] 8
names(which(table(books$author) > 1L))
#> [1] "Elias Moreau" "Iris Fenwick" "Mara Quill"
#> [4] "Noor Haddad" "Priya Raman" "Tomas Arden"
#> [7] "Ursula K. Le Guin"
# Two columns together define the key; unique() works on rows too.
unique(books[c("author", "genre")])[1:4, ]
#> author genre
#> 1 Ursula K. Le Guin science fiction
#> 2 Jane Austen classic
#> 3 Mara Quill fiction
#> 5 Iris Fenwick mystery
anyDuplicated(books[c("author", "genre")])
#> [1] 4Source: R documentation: duplicated
Note the order of operations. duplicated(books$author) finds eight repeated entries, and anyDuplicated() reports position 4, the
second book by Mara Quill. The second book by Ursula K. Le Guin is only found because the script first replaced the other
spelling, Le Guin, Ursula K.: duplicates in dirty text hide until the text is normalised. anyDuplicated(books$isbn) is 0: the ISBN is a unique key of books, even though one is
the empty string. To keep the first row of each key and drop the rest, index with !duplicated(df$key); to keep all the
rows that share a key, combine duplicated() with fromLast = TRUE as the script does.
More tools for tables
expand.grid(), cut() and counting
expand.grid() builds the data frame of all combinations of its arguments, with the first factor varying fastest (its
stringsAsFactors argument still defaults to TRUE, which is why the script passes FALSE). A
frequent use is to complete a table of counts so that combinations with no observations appear with a zero: build the
grid, merge(grid, counts, all.x = TRUE), and replace the NA with 0L. cut(x, breaks) bins a numeric vector into
intervals, and table() of the result is a histogram; right = FALSE makes the intervals closed on the left, as
decades are conventionally written, and dig.lab keeps the labels from switching to scientific notation.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
loans <- loans[!is.na(loans$loan_date) & !is.na(loans$member), ]
year <- format(loans$loan_date, "%Y")
loans$quarter <- paste(year, quarters(loans$loan_date), sep = "-")
# expand.grid(): every combination, to include the cells that never occur.
grid <- expand.grid(
member = sort(unique(loans$member)),
quarter = sort(unique(loans$quarter)),
stringsAsFactors = FALSE
)
dim(grid)
#> [1] 48 2
counts <- aggregate(loan_id ~ member + quarter, data = loans, FUN = length)
full <- merge(grid, counts, by = c("member", "quarter"), all.x = TRUE)
full$loan_id[is.na(full$loan_id)] <- 0L
head(full[order(full$member, full$quarter), ], 4)
#> member quarter loan_id
#> 1 Ana Ruiz 2025-Q1 1
#> 2 Ana Ruiz 2025-Q2 2
#> 3 Ana Ruiz 2025-Q3 2
#> 4 Ana Ruiz 2025-Q4 4
# cut() bins a number; table() counts the bins: a decade histogram.
decades <- cut(
books$year,
breaks = seq(1810, 2030, by = 10), right = FALSE, dig.lab = 4
)
tab <- table(decades)
tab[tab > 0L]
#> decades
#> [1810,1820) [1970,1980) [1990,2000) [2000,2010) [2010,2020) [2020,2030)
#> 1 1 1 2 6 4
used <- tab[tab > 0L]
writeLines(sprintf("%-11s %s", names(used), strrep("#", used)))
#> [1810,1820) #
#> [1970,1980) #
#> [1990,2000) #
#> [2000,2010) ##
#> [2010,2020) ######
#> [2020,2030) ####Source: R documentation: expand.grid
The text histogram uses strrep() to repeat one character per count. Six books were published in the 2010s, four in the
2020s, two in the 2000s, and one each in the 1810s, 1970s and 1990s; the book with no year does not appear in any bin
because cut() gives it NA and table() omits NA unless asked otherwise.
Source: R documentation: cut
Working row by row
Row-wise work on a data frame is the case where it behaves least like a matrix. apply(df, 1, f) converts the data frame
with as.matrix() first, so with a mixed-type data frame every row is a character vector and numbers are formatted
with a common width: the page count 88 arrives as " 88", with a leading blank. Work with whole columns instead
(sprintf() and paste() are vectorised), use mapply() or Map() over the columns when a function takes one value
from each column, loop over seq_len(nrow(df)) when a step needs the whole row, or split(df, seq_len(nrow(df))) for a list
of one-row data frames.
source("scenario.R")
shelf <- books[c(11, 1, 2), c("title", "year", "pages")]
# apply() turns the data frame into a matrix first; with mixed types that is a
# character matrix, and as.matrix() pads the numbers to a common width.
apply(shelf, 1, \(row) row[["pages"]])
#> 11 1 2
#> " 88" "387" "432"
apply(shelf, 1, \(row) paste0(row[["title"]], ": ", row[["pages"]], " pages"))
#> 11 1
#> "Paper Moons: 88 pages" "The Dispossessed: 387 pages"
#> 2
#> "Pride and Prejudice: 432 pages"
# Work on the columns instead: vectorised functions, or mapply() over them.
sprintf("%s: %d pages", shelf$title, shelf$pages)
#> [1] "Paper Moons: 88 pages" "The Dispossessed: 387 pages"
#> [3] "Pride and Prejudice: 432 pages"
label <- \(title, year) sprintf("%s (%d)", title, year)
mapply(label, shelf$title, shelf$year, USE.NAMES = FALSE)
#> [1] "Paper Moons (2005)" "The Dispossessed (1974)"
#> [3] "Pride and Prejudice (1813)"
# A loop over row numbers is fine when a step needs the whole row.
for (i in seq_len(nrow(shelf))) {
cat(i, shelf$title[[i]], shelf$year[[i]], "\n")
}
#> 1 Paper Moons 2005
#> 2 The Dispossessed 1974
#> 3 Pride and Prejudice 1813
# split() by row gives one data frame per row, with the row names as names.
names(split(shelf, seq_len(nrow(shelf))))
#> [1] "1" "2" "3"Source: R documentation: apply
Factor columns
A factor column keeps its full set of levels after the rows are filtered, so table() still lists the empty levels and
tapply() still reports them. droplevels() removes the unused levels from every factor column of a data frame.
Assigning a value that is not a level gives NA and the warning invalid factor level, NA generated, while rbind()
with a character value extends the levels. Since R 4.0.0 text columns are character by default, so these effects appear only
when a column is made a factor on purpose.
source("scenario.R")
tidy <- read.csv("books.csv", stringsAsFactors = TRUE)
tidy$genre <- factor(tolower(trimws(as.character(tidy$genre))))
# Subsetting rows keeps every level of a factor column.
keep <- tidy$genre %in% c("poetry", "mystery")
poetry_mystery <- tidy[keep, c("title", "genre")]
levels(poetry_mystery$genre)
#> [1] "classic" "fiction" "mystery" "non-fiction"
#> [5] "poetry" "science fiction"
table(poetry_mystery$genre)
#>
#> classic fiction mystery non-fiction poetry
#> 0 0 2 0 2
#> science fiction
#> 0
# droplevels() removes the unused levels from every factor column.
levels(droplevels(poetry_mystery)$genre)
#> [1] "mystery" "poetry"
# rbind() with character values adds new levels...
extra <- data.frame(title = "Cookbook", genre = "cooking")
levels(rbind(poetry_mystery, extra)$genre)
#> [1] "classic" "fiction" "mystery" "non-fiction"
#> [5] "poetry" "science fiction" "cooking"
# ...but assigning a value that is not a level gives NA and a warning.
tryCatch(
poetry_mystery$genre[1L] <- "cooking",
warning = conditionMessage
)
#> [1] "invalid factor level, NA generated"
# Convert back to character for string work.
class(as.character(poetry_mystery$genre))
#> [1] "character"Source: R documentation: droplevels
do.call(rbind, …) and row names
Binding a list of data frames with do.call(rbind, pieces) is the standard combine step, and it mangles row names when the
list has names: the list name is joined to the row name of each piece with a dot. unname(pieces) keeps the original row
names; make.row.names = FALSE (an argument of the rbind method for data frames) renumbers the rows from one; and
rownames(result) <- NULL afterwards does the same.
source("scenario.R")
books$genre <- tolower(trimws(books$genre))
by_genre <- split(books[c("title", "genre", "pages")], books$genre)
# The two longest books of every genre, as a list of data frames...
top2 <- lapply(by_genre, \(d) head(d[order(-d$pages), ], 2L))
# ...bound with do.call(): the list names are prefixed to the row names.
mangled <- do.call(rbind, top2)
head(mangled, 4)
#> title genre pages
#> classic.2 Pride and Prejudice classic 432
#> classic.8 The Lamp at Noon classic 280
#> fiction.10 THE LONG TABLE fiction 256
#> fiction.3 the quiet streets fiction 214
rownames(mangled)[1:4]
#> [1] "classic.2" "classic.8" "fiction.10" "fiction.3"
# unname() keeps the original row names; make.row.names = FALSE renumbers.
head(do.call(rbind, unname(top2)), 3)
#> title genre pages
#> 2 Pride and Prejudice classic 432
#> 8 The Lamp at Noon classic 280
#> 10 THE LONG TABLE fiction 256
head(do.call(rbind, c(unname(top2), make.row.names = FALSE)), 3)
#> title genre pages
#> 1 Pride and Prejudice classic 432
#> 2 The Lamp at Noon classic 280
#> 3 THE LONG TABLE fiction 256
rownames(mangled) <- NULL
head(mangled, 3)
#> title genre pages
#> 1 Pride and Prejudice classic 432
#> 2 The Lamp at Noon classic 280
#> 3 THE LONG TABLE fiction 256Source: R documentation: cbind and rbind
Printing
print() of a data frame has arguments of its own, and several options control the rest. row.names = FALSE hides the
row names, right = FALSE left-aligns text, digits sets the significant digits, quote = TRUE quotes strings, and max
caps the number of entries. The global options are width (the line width in characters, which decides where wide data
frames wrap their columns), digits, and max.print, which is the maximum number of entries printed by default: for a
data frame it prints as many whole rows as fit in max.print divided by the number of columns, followed by the message
[ reached 'max' / getOption("max.print") -- omitted 11 rows ]. The default is 99999. format() converts a data frame to a
data frame of character columns, which is the starting point for custom tables.
source("scenario.R")
shelf <- books[c("title", "author", "year", "pages")]
shelf$title <- trimws(shelf$title)
# print() options: no row names, left-aligned text.
print(head(shelf, 3), row.names = FALSE)
#> title author year pages
#> The Dispossessed Ursula K. Le Guin 1974 387
#> Pride and Prejudice Jane Austen 1813 432
#> the quiet streets Mara Quill 2019 214
print(head(shelf, 3), right = FALSE)
#> title author year pages
#> 1 The Dispossessed Ursula K. Le Guin 1974 387
#> 2 Pride and Prejudice Jane Austen 1813 432
#> 3 the quiet streets Mara Quill 2019 214
# options(width) decides where wide frames wrap their columns.
old <- options(width = 50)
head(books, 2)
#> title author
#> 1 The Dispossessed Ursula K. Le Guin
#> 2 Pride and Prejudice Jane Austen
#> isbn year genre pages
#> 1 978-0-306-40615-7 1974 Science fiction 387
#> 2 978-0-14-143951-8 1813 Classic 432
options(old)
# max.print caps the number of entries printed (rows * columns).
old <- options(max.print = 20)
shelf
#> title author year pages
#> 1 The Dispossessed Ursula K. Le Guin 1974 387
#> 2 Pride and Prejudice Jane Austen 1813 432
#> 3 the quiet streets Mara Quill 2019 214
#> 4 Loud Roofs Mara Quill 2021 198
#> 5 Midnight Ledger Iris Fenwick 2016 301
#> [ reached 'max' / getOption("max.print") -- omitted 11 rows ]
options(old)
# format() turns a data frame into text, cell by cell.
format(head(shelf, 2), nsmall = 1)
#> title author year pages
#> 1 The Dispossessed Ursula K. Le Guin 1974 387
#> 2 Pride and Prejudice Jane Austen 1813 432options() returns the old values invisibly when it sets new ones, and options(old) restores them: the pattern in the
script is how a script should change width or max.print for one printout without leaving the session changed. The
options are described in R documentation: options. Writing a
data frame to a file is covered by
Reading and Writing Text Data.
Putting it together
A last script joins the pieces: clean the keys, compute the overdue flag, join loans to books, summarise by genre, and list the three most-loaned titles.
source("scenario.R")
# Clean the keys and the text columns.
books$isbn <- normalise_isbn(books$isbn)
books$genre <- tolower(trimws(books$genre))
books$title <- trimws(books$title)
loans$member <- tools::toTitleCase(trimws(loans$member))
loans$member[!nzchar(loans$member)] <- NA
# Days held, and whether the loan is overdue.
end <- loans$return_date
end[is.na(end)] <- shelf_today
loans$held <- as.integer(end - loans$loan_date)
loans$overdue <- loans$held > loan_days
# Join loans to books, then summarise by genre.
joined <- merge(loans, books[c("isbn", "title", "genre", "pages")], by = "isbn")
joined$n <- 1L
joined$late <- as.integer(joined$overdue)
by_genre <- aggregate(cbind(loans = n, overdue = late) ~ genre, joined, sum)
by_genre$share <- round(by_genre$overdue / by_genre$loans, 2)
by_genre[order(-by_genre$share, by_genre$genre), ]
#> genre loans overdue share
#> 5 poetry 6 4 0.67
#> 1 classic 5 3 0.60
#> 4 non-fiction 5 3 0.60
#> 2 fiction 23 12 0.52
#> 3 mystery 4 2 0.50
#> 6 science fiction 15 7 0.47
# The three books loaned most often.
head(sort(table(joined$title), decreasing = TRUE), 3)
#>
#> the quiet streets Ten Winters The Dispossessed
#> 8 7 7Source: R documentation: aggregate
Poetry has the highest share of overdue loans (4 of 6) and science fiction the lowest (7 of 15). Fiction is the most loaned genre, with 23 of the 58 loans that matched a book and have a known overdue flag. The result depends on the choices made above (what counts as overdue, how the loans without a member or a date are treated, which key is normalised), and a report built from it should state them.
Tibbles and data.table
Two add-on packages provide alternatives to the base data frame, and both are covered in their own pages. They are labelled packages, not part of base R, and the base functions in this page keep working on both of them.
tibble (checked at version 3.3.1) is the data frame of the tidyverse. A tibble has class c("tbl_df", "tbl",
"data.frame"), so every data frame function still applies, and it differs in a few places that remove the traps above:
it drops row names on conversion, [ with one column returns a tibble and not a vector, $ does not match a partial
name (it warns Unknown or uninitialised column and returns NULL), and it does not recycle anything except length-one
values. It prints the type of each column under its name and shows only the first rows that fit. It is created with
tibble() or as_tibble().
source("scenario.R")
shelf <- books[c("title", "year", "pages")]
# A tibble is a data frame (class "tbl_df" first); printing differs.
tb <- tibble::as_tibble(shelf)
class(tb)
#> [1] "tbl_df" "tbl" "data.frame"
tb[1:3, ]
#> # A tibble: 3 × 3
#> title year pages
#> <chr> <int> <int>
#> 1 "The Dispossessed" 1974 387
#> 2 "Pride and Prejudice" 1813 432
#> 3 " the quiet streets " 2019 214
# [ keeps the tibble when one column is chosen; $ does not match partially.
class(tb[, "year"])
#> [1] "tbl_df" "tbl" "data.frame"
tryCatch(tb$ye, warning = conditionMessage)
#> [1] "Unknown or uninitialised column: `ye`."
shelf$ye[1:3]
#> [1] 1974 1813 2019The strings are printed in quotes in the tibble output, which makes the padded third title visible. The
dplyr verbs that work on tibbles (filter(), mutate(), group_by(), summarise(), joins) are the subject of
The tidyverse and dplyr, and pivot_longer() and pivot_wider(), the
tidyr replacements for reshape(), are in tidyr, purrr and stringr.
data.table (checked at version 1.18.6.1) is a package for large tables that extends the data frame (a data.table is a
data.frame too) with a terse DT[i, j, by] syntax, modification by reference (:=), keyed joins and fast grouping and
reading (fread()). Because it modifies in place, it breaks the copy-on-modify rule that this page relies on: DT[, new := 1]
changes DT without an assignment. It is the better choice when a table has millions of rows or the same grouped
computation runs repeatedly, and its syntax is learned separately: see
data.table. No data.table code is run in this page. The package documentation
is at data.table package documentation.
Common errors and warnings
The table lists the messages met on this page, with the exact text R 4.6.1 printed; the script captures the ones that the
earlier examples did not show. The message column is conditionMessage(), without the Error in ... prefix the console
adds.
source("scenario.R")
shelf <- books[1:2, c("title", "year")]
# what() runs an expression and reports the first condition it signals.
what <- function(expr) {
tryCatch(
{
expr
"no condition"
},
error = \(e) paste("error:", conditionMessage(e)),
warning = \(w) paste("warning:", conditionMessage(w))
)
}
# Shape and name problems.
what(cbind(shelf, 1:5))
#> [1] "error: arguments imply differing number of rows: 2, 5"
what(rbind(shelf, 1:3))
#> [1] "warning: number of columns of result, 2, is not a multiple of vector length 3 of arg 2"
what(books[books$genre == "Poetry"])
#> [1] "error: undefined columns selected"
what(order(books$year, 1:3))
#> [1] "error: argument lengths differ"
what(aggregate(pages ~ nope, data = books, FUN = mean))
#> [1] "error: object 'nope' not found"
what(aggregate(pages ~ genre, data = books[0, ], FUN = mean))
#> [1] "error: no rows to aggregate"
what(split(books, books$genre[1:3]))
#> [1] "warning: data length is not a multiple of split variable"
# A data frame used where a vector is expected.
what(books + 1)
#> [1] "error: non-numeric argument to binary operator"
what(mean(books))
#> [1] "warning: argument is not numeric or logical: returning NA"
what(sum(books))
#> [1] "error: only defined on a data frame with all numeric-alike variables"
what(rowSums(books))
#> [1] "error: 'x' must be numeric or complex"
what(as.numeric(books))
#> [1] "error: 'list' object cannot be coerced to type 'double'"| Message | Raised by | Cause and fix |
|---|---|---|
|
|
Columns that are not recycled wholly. Make the lengths equal or a divisor of the longest. |
|
|
Row names must be unique. Reset with |
|
|
The |
|
|
A column name or position that does not exist, or a missing comma that made a row filter a column index. |
|
|
A position beyond the last column. |
|
|
|
|
|
The new column has the wrong length. Give one value per row. |
|
|
The data frames to bind have different column names. |
|
|
The data frames have a different number of columns. |
|
|
A vector appended as a row whose length does not divide the number of columns (since R 4.2.0). |
|
|
A key is not a column of both data frames. |
|
|
The sort keys have different lengths. |
|
|
A data frame was passed as one key. Pass its columns. |
|
|
Unary minus on text. Use |
|
|
Add |
|
|
An argument uses a column that does not exist yet, or a name that is not a column of the data. |
|
|
The data frame (or the rows left after |
|
|
The grouping vector and the data differ in length. |
|
|
The grouping vector is shorter than the data. |
|
|
Arithmetic on a data frame that has a non-numeric column. Select the numeric columns first. |
|
|
|
|
|
A summary function on a data frame with a text column. |
|
|
A factor column was assigned a value that is not one of its levels. |
|
|
The elements of the |
|
|
No recycling in |
|
tibble |
No partial matching, no column of that name. |
|
|
A column name was abbreviated. |
Pitfalls
-
df[, "x"]is a vector,df["x"]is a data frame. Usedrop = FALSEwhen the number of columns is not fixed (R FAQ 7.5 is the matrix version of the same rule). -
NAin a row filter gives a row ofNA. Usewhich(),subset(), or!is.na(x) & cond. -
subset(),transform()andwith()look inside the data first. A parameter named like a column loses; use a different name ordf[which(df$year < cutoff), ]in functions. -
Row names survive subsetting. Numbers select by position and strings by label; reset with
rownames(df) <- NULL.[matches row names partially,$matches column names partially. -
aggregate()with a formula drops whole rows with anyNA. Usena.action = na.passwithna.rm = TRUE. -
merge()re-sorts, multiplies rows for duplicate keys and matchesNAwithNA. CheckanyDuplicated()on the key and normalise key text first. -
-in asort_by()formula is not "descending". Use~ list(a, -b). -
apply(),t()andas.matrix()on a mixed data frame give character. Work on columns. -
df[-which(...)]drops every column when nothing matches. Usesetdiff()or%in%. -
rbind()in a loop is quadratic anddo.call(rbind, named_list)mangles row names. Bind once;unname()ormake.row.names = FALSE. -
Converting a factor to numbers.
as.numeric(f)gives the level codes; convert throughas.character()first (R FAQ 7.10: How do I convert factors to numeric?).
Version notes
| Release | Change that affects data frames |
|---|---|
R 4.0.0 |
|
R 4.1.0 |
The |
R 4.2.0 |
The data frame method of |
R 4.3.0 |
|
R 4.4.0 |
|
R 4.6.0 |
|
Where older tutorials differ
Many pages on the web were written before the 2020 change of default and use idioms that a current R either handles differently or discourages. When one of them disagrees with this page, the table says why.
| Older tutorial | Current practice | Why | Version |
|---|---|---|---|
|
|
attached names are a copy, assignments create masking workspace variables, and two attached data frames mask each other; An Introduction to R itself lists the caveats |
always |
|
leave the default, and pass |
the default became |
R 4.0.0 |
text columns of a data frame are factors and |
text columns are character; call |
a character column has no levels and sorts and groups as text |
R 4.0.0 |
|
|
|
R 4.4.0 |
|
|
the default |
R 4.6.0 |
|
|
|
always |
|
|
|
always |
|
collect the pieces in a list and call |
each |
always |
|
vectorised functions on columns, |
|
always |
|
|
|
always |
|
|
the formula method drops incomplete rows by default |
always |
|
|
|
always |
|
base |
the base functions need no extra package; the packages have pages of their own |
always |
For the full list of changes by release see What’s New and Migrating Older Code.
Where to go next
-
Lists for the type a data frame is built on, and Subsetting and Indexing for the full rules of
[,[[and$. -
Factors for categorical columns, levels and
droplevels(). -
Missing and Special Values for
NAhandling, and Attributes and Names fornames,row.namesandclass. -
Strings and Regular Expressions and Dates and Times for cleaning the text and date columns.
-
The apply Family and Functionals for
lapply(),vapply()andMap()over columns and groups. -
Reading and Writing Text Data for
read.csv()andwrite.csv(). -
The tidyverse and dplyr, tidyr, purrr and stringr and data.table for the add-on ecosystems.
-
Descriptive Statistics and Tests and Statistical Models for what is done with the data frame next.
References
-
An Introduction to R: Data frames, Making data frames, attach() and detach(), Working with data frames, Attaching arbitrary lists
-
R NEWS: Changes in R 4.0.0, 4.1.0, 4.2.0, 4.3.0, 4.4.0 and 4.6.0
-
row.names, Extract.data.frame, subset, transform, with and within, attach
-
merge, split and unsplit, tapply, by, aggregate, ave, rowsum, xtabs, table
-
reshape, stack and unstack, duplicated, expand.grid, cut, print.data.frame
-
str, summary, head and tail, complete.cases, cbind and rbind, lapply, sapply and vapply
-
tibble package documentation: as_tibble and data.table package documentation (add-on packages: the tibble example runs tibble 3.3.1, and data.table is described but not run)