Adding columns to each in a list of dataframes

Question

This is a follow-up to my earlier question at Matching and transposing data between dataframes in R. I have a list of dataframes, for example:

dfs <- structure(list(df1 = structure(list(id = structure(c(1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "A", class = "factor")), .Names = "id", class = "data.frame", row.names = c(NA, 
-12L)), df2 = structure(list(id = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "B", class = "factor")), .Names = "id", class = "data.frame", row.names = c(NA, 
-12L)), df3 = structure(list(id = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "C", class = "factor")), .Names = "id", class = "data.frame", row.names = c(NA, 
-12L))), .Names = c("df1", "df2", "df3"))

In each dataframe in the list, I would like to create a new column data based on matching and transposing from a fourth dataframe df4:

df4 <- structure(list(id = structure(1:3, .Label = c("A", "B", "C"), class = "factor"), 
    x1 = c(9L, 4L, 9L), x2 = c(7L, 2L, 8L), x3 = c(7L, 6L, 7L
    ), x4 = c(9L, 5L, 5L), x5 = c(8L, 8L, 4L), x6 = c(7L, 4L, 
    6L), x7 = c(9L, 8L, 5L), x8 = c(7L, 7L, 8L), x9 = c(5L, 5L, 
    5L), x10 = c(4L, 2L, 8L), x11 = c(9L, 1L, 4L), x12 = c(8L, 
    6L, 5L)), .Names = c("id", "x1", "x2", "x3", "x4", "x5", 
"x6", "x7", "x8", "x9", "x10", "x11", "x12"), class = "data.frame", row.names = c(NA, 
-3L))

I can achieve this using separate lines of code for each dataframe in the list, such as

dfs$df1$data <- t(df4[unique(match(dfs$df1$id, df4$id)), 2:13])
dfs$df2$data <- t(df4[unique(match(dfs$df2$id, df4$id)), 2:13])
dfs$df3$data <- t(df4[unique(match(dfs$df3$id, df4$id)), 2:13])

but i'm sure there must be a more efficient and shorter way to do this. I'm pretty sure I need to use lapply but cannot figure out how to make that work. For example, I can use

lapply(dfs, function(d) t(df4[unique(match(d$id, df4$id)), 2:13]))

to give the result as vectors, but I can't figure out how to insert these as new columns called data in each dataframe in the list. Does anyone know how I could do this?

Thanks!

Maybe Map(cbind, dfs, lapply(dfs, function(d) t(df4[unique(match(d$id, df4$id)), -1])))? — David Arenburg
– David Arenburg, Commented Sep 29, 2014 at 22:17
When you get to these levels of pain in R, I suggest you may want to consider Python pandas. If that's acceptable to you, I'll show you the pandas answer, which will be infinitely more readable and maintainable. — smci
– smci, Commented Sep 29, 2014 at 22:23
@GSee, I think that's irrelevant. It's certainly relevant to dataframes,lapply. Edit the tags as you see fit. — smci
– smci, Commented Sep 29, 2014 at 22:25
smci - haven't used python before although it's been recommended to me before and i'm thus becoming more and more interested. i'm just looking for a quick fix now but thanks for the suggestion. — Thomas
– Thomas, Commented Sep 29, 2014 at 22:28

thelatemail · Accepted Answer · 2014-09-30 00:28:59Z

3

Here's an attempt using lapply:

lapply(dfs, function(x) {
  cbind(
       x,
       new=unlist(df4[match(x$id[1],df4$id),-1])
       )
})

#$df1
#    id new
#x1   A   9
#x2   A   7
#x3   A   7
#...
#
#$df2
#    id new
#x1   B   4
#x2   B   2
#x3   B   6
#...
# 
#$df3
#    id new
#x1   C   9
#x2   C   8
#x3   C   7
#...

edited Sep 30, 2014 at 0:28

answered Sep 29, 2014 at 22:25

thelatemail

94.3k12 gold badges140 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Over a year ago

brilliant, thanks thelatemail! you pretty much saved me a bazillion lines of code

Collectives™ on Stack Overflow

Adding columns to each in a list of dataframes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related