dbplyr: pivot_longer.tbl_lazy – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

pivot_longer.tbl_lazy

Pivot data from wide to long

Description

pivot_longer() "lengthens" data, increasing the number of rows and decreasing the number of columns. The inverse transformation is 'tidyr::pivot_wider()]

Learn more in vignette("pivot", "tidyr").

While most functionality is identical there are some differences to pivot_longer() on local data frames:

the output is sorted differently/not explicitly,
the coercion of mixed column types is left to the database,
values_ptypes NOT supported.

Note that build_longer_spec() and pivot_longer_spec() do not work with remote tables.

Usage

pivot_longer.tbl_lazy(
  data,
  cols,
  names_to = "name",
  names_prefix = NULL,
  names_sep = NULL,
  names_pattern = NULL,
  names_ptypes = list(),
  names_transform = list(),
  names_repair = "check_unique",
  values_to = "value",
  values_drop_na = FALSE,
  values_ptypes,
  values_transform = list(),
  ...
)

Arguments

`data`	A data frame to pivot.
`cols`	Columns to pivot into longer format.
`names_to`	A string specifying the name of the column to create from the data stored in the column names of `data`.
`names_prefix`	A regular expression used to remove matching text from the start of each variable name.
`names_sep, names_pattern`	If `names_to` contains multiple values, these arguments control how the column name is broken up.
`names_ptypes`	A list of column name-prototype pairs.
`names_transform, values_transform`	A list of column name-function pairs.
`names_repair`	What happens if the output has invalid column names?
`values_to`	A string specifying the name of the column to create from the data stored in cell values. If `names_to` is a character containing the special `.value` sentinel, this value will be ignored, and the name of the value column will be derived from part of the existing column names.
`values_drop_na`	If `TRUE`, will drop rows that contain only `NA`s in the `value_to` column.
`values_ptypes`	Not supported.
`...`	Additional arguments passed on to methods.

Details

The SQL translation basically works as follows:

split the specification by its key columns i.e. by variables crammed into the column names.
for each part in the splitted specification transmute() data into the following columns

id columns i.e. columns that are not pivotted
key columns
value columns i.e. columns that are pivotted

combine all the parts with union_all()

Examples

# See vignette("pivot") for examples and explanation

# Simplest case where column names are character data
if (require("tidyr", quietly = TRUE)) {
  memdb_frame(
    id = c("a", "b"),
    x = 1:2,
    y = 3:4
  ) %>%
    pivot_longer(-id)
}

dbplyr

A 'dplyr' Back End for Databases

v2.1.1

MIT + file LICENSE

Authors

Hadley Wickham [aut, cre], Maximilian Girlich [aut], Edgar Ruiz [aut], RStudio [cph, fnd]

Initial release