Merge dataframes of different sizes

by

From your description I understand that you want to replace the z values in d1 with the z values in d2 when x & y match.

Using base R:

d3 <- merge(d1, d2, by = c("x","y"), all.x = TRUE)
d3[is.na(d3$z.y),"z.y"] <- d3[is.na(d3$z.y),"z.x"]
d3 <- d3[,-3]
names(d3)[3] <- "z"

which gives:

> d3
   x  y   z
1 10 10 100
2 10 12   6
3 11 10 200
4 11 12   2
5 12 10   1
6 12 12 400

Using the data.table-package:

library(data.table)

setDT(d1) # convert the data.frame to a data.table
setDT(d2) # idem

# join the two data.table's and replace the values
d1[d2, on = .(x, y), z := i.z]

or in one go:

setDT(d1)[setDT(d2), on = .(x, y), z := i.z]

which gives:

> d1
    x  y   z
1: 10 10 100
2: 10 12   6
3: 11 10 200
4: 11 12   2
5: 12 10   1
6: 12 12 400

Using the dplyr package:

d3 <- left_join(d1, d2, by = c("x","y")) %>%
  mutate(z.y = ifelse(is.na(z.y), z.x, z.y)) %>%
  select(-z.x) %>%
  rename(z = z.y)

Since release 0.5.0 you can also use the coalesce-function for this (thx to Laurent Hostert for bringing it to my attention):

d3 <- left_join(d1, d2, by = c("x","y")) %>% 
  mutate(z = coalesce(z.y, z.x)) %>% 
  select(-c(z.x, z.y))

More Related Contents:

  1. Repeat each row of data.frame the number of times specified in a column
  2. Filter data.frame rows by a logical condition
  3. Split column at delimiter in data frame [duplicate]
  4. Pass a data.frame column name to a function
  5. How to convert a list consisting of vector of different lengths to a usable data frame in R?
  6. Select groups based on number of unique / distinct values
  7. Assign multiple columns using := in data.table, by group
  8. Remove an entire column from a data.frame in R
  9. Selecting multiple odd or even columns/rows for dataframe
  10. Sample n random rows per group in a dataframe
  11. Deleting reversed duplicates with R
  12. Select every nth row from dataframe
  13. Remove Rows From Data Frame where a Row matches a String
  14. duplicates in multiple columns
  15. R – add column that counts sequentially within groups but repeats for duplicates
  16. R self reference
  17. Change row order in a matrix/dataframe
  18. Subsetting a data frame based on contents of another data frame
  19. Check whether values in one data frame column exist in a second data frame
  20. Unlist a data frame by rows, not columns
  21. Deleting columns from a data.frame where NA is more than 15% of the column length [duplicate]
  22. Using lapply to apply a function over list of data frames and saving output to files with different names
  23. Return df with a columns values that occur more than once [duplicate]
  24. Row-wise sum of values grouped by columns with same name
  25. Replicate each row of data.frame and specify the number of replications for each row?
  26. Count unique values for every column
  27. Divide each value by the sum of values by group
  28. Convert all data frame character columns to factors
  29. What you can do with a data.frame that you can’t with a data.table?
  30. Override column types when importing data using readr::read_csv() when there are many columns

Leave a Comment