left_join a dataframe across multiple dataframes in a list

user_7465731 0
Viewed 574

I'm trying to left_join a dataframe across multiple dataframes in a list, here is an example of the list and the dataframe:

list1 = lapply(1:3, function(x) data.frame(x = rnorm(10), y = rnorm(10), z = rnorm(10), fac = sample(c("new","old"), 10, replace = TRUE)))

df_1 = data.frame(fac = sample(c("new","old"),10,replace = TRUE), t = rnorm(10))

Now, I'm sure I can use a for loop and the following to add the dataframe to each component of the list. However, I thought there should be an easy way to do this using lapply, something like:

lapply(list1,function(x) x %>% left_join(x,df_1, by = c("fac")))

Though this isn't working, where am I going wrong? I'm guessing you could also use map somehow from purrr. One important aspect that isn't fully represented in the above example is that I need the dataframe to be joined by matching factors, thus a simple cbind would not suffice.

Thanks

dplyr tidyverse left-join r
1 Answers

I think you're looking for merge. However, as I already stated in comments, your merge column needs to be unique. Example:

list1   
# [[1]]
#            x          y          z fac
# 1  1.3709584  0.6328626  1.51152200   2
# 2 -0.5646982  0.4042683 -0.09465904   3
# 3  0.3631284 -0.1061245  2.01842371   1
# 
# [[2]]
#            x          y          z fac
# 1 -1.0861326 1.3149588  0.4822047   2
# 2  1.6133728 0.9781675  0.9657529   1
# 3  0.0356312 0.8817912 -0.8145709   3
# 
# [[3]]
#            x          y          z fac
# 1 -1.7813084  1.8951935 -1.7631631   1
# 2 -0.1719174 -0.4304691  0.4600974   2
# 3  1.2146747 -0.2572694 -0.6399949   3

df_1
#   fac u          t
# 1   1 1  0.1674409
# 2   2 2 -0.8798365
# 3   3 3  0.9469132

Now merge:

res <- lapply(list1, merge, df_1, by="fac")
res
# [[1]]
#   fac          x          y           z u          t
# 1   1  0.3631284 -0.1061245  2.01842371 1  0.1674409
# 2   2  1.3709584  0.6328626  1.51152200 2 -0.8798365
# 3   3 -0.5646982  0.4042683 -0.09465904 3  0.9469132
# 
# [[2]]
#   fac          x         y          z u          t
# 1   1  1.6133728 0.9781675  0.9657529 1  0.1674409
# 2   2 -1.0861326 1.3149588  0.4822047 2 -0.8798365
# 3   3  0.0356312 0.8817912 -0.8145709 3  0.9469132
# 
# [[3]]
#   fac          x          y          z u          t
# 1   1 -1.7813084  1.8951935 -1.7631631 1  0.1674409
# 2   2 -0.1719174 -0.4304691  0.4600974 2 -0.8798365
# 3   3  1.2146747 -0.2572694 -0.6399949 3  0.9469132

Data:

set.seed(42)
list1 <- replicate(3, data.frame(x=rnorm(3), y=rnorm(3), z=rnorm(3), 
                                 fac=sample(1:3, 3, replace=F)), simplify=F)

df_1 <- data.frame(fac=1:3, u=1:3, t=rnorm(3))
user_65740380
Related