Reputation: 2725
I have a couple of data frames, some of the row names in both are the same. I want to copy a column from first data frame for all the row names that are present in second data frame. First data frame (df1) looks like
m1 m2 m3
P001 60.00 2.0 1
P002 14.30 2.077 1
P003 29.60 2.077 1
P004 10.30 2.077 1
P006 79.30 2.077 1
P008 9.16 2.077 1
and the second data frame (df2) looks like
n1 n2 n3
P001 12.00 2.0 1
P003 17.60 1.7 1
P005 22.30 2.7 1
P006 26.30 1.7 1
I want to have variable m1 (df1$m1) for all row names present in second data frame( i.e P001, P003, P005, and P006). If some row names does not exist in df1 (eg P005), it could be replaced with NA or 0.
The answer could be something like this
m1
P001 60.00
P003 29.60
P005 NA
P006 79.30
The longer option would be to use loops but I am sure R should have a shortcut.
Upvotes: 1
Views: 3169
Reputation: 92310
data.table
object doesn't keep row names (although it has it stored as an attribute) but you can convert the row names to an additional column while converting to data.table
using keep.rownames = TRUE
and then, after keying, you can join the data sets using the binary join and while updating df2
by reference (similarly to @eddies link)
setDT(df1, keep.rownames = TRUE)
setkey(setDT(df2, keep.rownames = TRUE), rn)
df2[df1, m1 := i.m1][]
# rn n1 n2 n3 m1
# 1: P001 12.0 2.0 1 60.0
# 2: P003 17.6 1.7 1 29.6
# 3: P005 22.3 2.7 1 NA
# 4: P006 26.3 1.7 1 79.3
Upvotes: 3
Reputation: 1664
try match()
:
df2$m1 <- df1$m1[match(row.names(df2), row.names(df1))]
Upvotes: 2