| data_merge {datawizard} | R Documentation |
Merge (join) two data frames, or a list of data frames
Description
Merge (join) two data frames, or a list of data frames. However, unlike
base R's merge(), data_merge() offers a few more methods to join data
frames, and it does not drop data frame nor column attributes.
Usage
data_merge(x, ...)
data_join(x, ...)
## S3 method for class 'data.frame'
data_merge(x, y, join = "left", by = NULL, id = NULL, verbose = TRUE, ...)
## S3 method for class 'list'
data_merge(x, join = "left", by = NULL, id = NULL, verbose = TRUE, ...)
Arguments
x, y |
A data frame to merge. |
... |
Not used. |
join |
Character vector, indicating the method of joining the data frames.
Can be |
by |
Specifications of the columns used for merging. |
id |
Optional name for ID column that will be created to indicate the
source data frames for appended rows. Only applies if |
verbose |
Toggle warnings. |
Details
Merging data frames
Merging data frames is performed by adding rows (cases), columns
(variables) or both from the source data frame (y) to the target
data frame (x). This usually requires one or more variables which
are included in both data frames and that are used for merging, typically
indicated with the by argument. When by contains a variable present
in both data frames, cases are matched and filtered by identical values
of by in x and y.
Left- and right-joins
Left- and right joins usually don't add new rows (cases), but only new
columns (variables) for existing cases in x. For join = "left" or
join = "right" to work, by must indicate one or more columns that
are included in both data frames. For join = "left", if by is an
identifier variable, which is included in both x and y, all variables
from y are copied to x, but only those cases from y that have
matching values in their identifier variable in x (i.e. all cases
in x that are also found in y get the related values from the new
columns in y). If there is no match between identifiers in x and y,
the copied variable from y will get a NA value for this particular
case. Other variables that occur both in x and y, but are not used
as identifiers (with by), will be renamed to avoid multiple identical
variable names. Cases in y where values from the identifier have no
match in x's identifier are removed. join = "right" works in
a similar way as join = "left", just that only cases from x that
have matching values in their identifier variable in y are chosen.
In base R, these are equivalent to merge(x, y, all.x = TRUE) and
merge(x, y, all.y = TRUE).
Full joins
Full joins copy all cases from y to x. For matching cases in both
data frames, values for new variables are copied from y to x. For
cases in y not present in x, these will be added as new rows to x.
Thus, full joins not only add new columns (variables), but also might
add new rows (cases).
In base R, this is equivalent to merge(x, y, all = TRUE).
Inner joins
Inner joins merge two data frames, however, only those rows (cases) are
kept that are present in both data frames. Thus, inner joins usually
add new columns (variables), but also remove rows (cases) that only
occur in one data frame.
In base R, this is equivalent to merge(x, y).
Binds
join = "bind" row-binds the complete second data frame y to x.
Unlike simple rbind(), which requires the same columns for both data
frames, join = "bind" will bind shared columns from y to x, and
add new columns from y to x.
Value
A merged data frame.
See Also
Functions to rename stuff:
data_rename(),data_rename_rows(),data_addprefix(),data_addsuffix()Functions to reorder or remove columns:
data_reorder(),data_relocate(),data_remove()Functions to reshape, pivot or rotate data frames:
data_to_long(),data_to_wide(),data_rotate()Functions to recode data:
rescale(),reverse(),categorize(),recode_values(),slide()Functions to standardize, normalize, rank-transform:
center(),standardize(),normalize(),ranktransform(),winsorize()Split and merge data frames:
data_partition(),data_merge()Functions to find or select columns:
data_select(),data_find()Functions to filter rows:
data_match(),data_filter()
Examples
x <- data.frame(a = 1:3, b = c("a", "b", "c"), c = 5:7, id = 1:3)
y <- data.frame(c = 6:8, d = c("f", "g", "h"), e = 100:102, id = 2:4)
x
y
# "by" will default to all shared columns, i.e. "c" and "id". new columns
# "d" and "e" will be copied from "y" to "x", but there are only two cases
# in "x" that have the same values for "c" and "id" in "y". only those cases
# have values in the copied columns, the other case gets "NA".
data_merge(x, y, join = "left")
# we change the id-value here
x <- data.frame(a = 1:3, b = c("a", "b", "c"), c = 5:7, id = 1:3)
y <- data.frame(c = 6:8, d = c("f", "g", "h"), e = 100:102, id = 3:5)
x
y
# no cases in "y" have the same matching "c" and "id" as in "x", thus
# copied variables from "y" to "x" copy no values, all get NA.
data_merge(x, y, join = "left")
# one case in "y" has a match in "id" with "x", thus values for this
# case from the remaining variables in "y" are copied to "x", all other
# values (cases) in those remaining variables get NA
data_merge(x, y, join = "left", by = "id")
data(mtcars)
x <- mtcars[1:5, 1:3]
y <- mtcars[28:32, 4:6]
# add ID common column
x$id <- 1:5
y$id <- 3:7
# left-join, add new variables and copy values from y to x,
# where "id" values match
data_merge(x, y)
# right-join, add new variables and copy values from x to y,
# where "id" values match
data_merge(x, y, join = "right")
# full-join
data_merge(x, y, join = "full")
data(mtcars)
x <- mtcars[1:5, 1:3]
y <- mtcars[28:32, c(1, 4:5)]
# add ID common column
x$id <- 1:5
y$id <- 3:7
# left-join, no matching rows (because columns "id" and "disp" are used)
# new variables get all NA values
data_merge(x, y)
# one common value in "mpg", so one row from y is copied to x
data_merge(x, y, by = "mpg")
# only keep rows with matching values in by-column
data_merge(x, y, join = "semi", by = "mpg")
# only keep rows with non-matching values in by-column
data_merge(x, y, join = "anti", by = "mpg")
# merge list of data frames. can be of different rows
x <- mtcars[1:5, 1:3]
y <- mtcars[28:31, 3:5]
z <- mtcars[11:18, c(1, 3:4, 6:8)]
x$id <- 1:5
y$id <- 4:7
z$id <- 3:10
data_merge(list(x, y, z), join = "bind", by = "id", id = "source")