Bind rows

Stack tables on top of each other

SAS: SET with several datasets; PROC SQL UNION / OUTER UNION CORR

dplyr::bind_rows() stacks tables vertically. Columns are matched by name. Columns present in only one table are filled with NA in the other rows.

Quick reference

library(dplyr)

bind_rows(ae_site_a, ae_site_b)

bind_rows(
  list(SITEA = ae_site_a, SITEB = ae_site_b),
  .id = "SITE"
)

R	SAS
`bind_rows(a, b)`	`set a b;` (stack; align names with `rename=` if needed)
`bind_rows(..., .id = "SRC")`	add a source variable before `set`
`bind_rows()` by column name	`PROC SQL` `OUTER UNION CORR`

Trap: bind_rows() matches columns by name, not position. A type clash on the same name (character vs numeric) errors or coerces; fix types before stacking.

Worked examples below.

Stack two tables

library(dplyr)


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

ae_site_a <- data.frame(
  USUBJID = c("STD-001-0001", "STD-001-0002"),
  AETERM = c("NAUSEA", "HEADACHE"),
  AESEV = c("MILD", "MODERATE")
)

ae_site_b <- data.frame(
  USUBJID = c("STD-001-0003"),
  AETERM = c("FATIGUE"),
  AESEV = c("MILD"),
  AEREL = c("RELATED")
)

bind_rows(ae_site_a, ae_site_b)

       USUBJID   AETERM    AESEV   AEREL
1 STD-001-0001   NAUSEA     MILD    <NA>
2 STD-001-0002 HEADACHE MODERATE    <NA>
3 STD-001-0003  FATIGUE     MILD RELATED

AEREL is NA for site A rows. SAS set ae_site_a ae_site_b; is the usual analogue when variable names already match.

Mark the source table

bind_rows(
  list(SITEA = ae_site_a, SITEB = ae_site_b),
  .id = "SITE"
)

   SITE      USUBJID   AETERM    AESEV   AEREL
1 SITEA STD-001-0001   NAUSEA     MILD    <NA>
2 SITEA STD-001-0002 HEADACHE MODERATE    <NA>
3 SITEB STD-001-0003  FATIGUE     MILD RELATED

.id adds a column with the list names. Useful when you need to know which input row came from.

Match by name, not position

bind_rows() does not line up column 1 with column 1. Rename first when the same concept has different names. When the same name has different types, fix the type before stacking.

For side-by-side combines on keys, use joins (see Joins), not bind_rows().