CODEWITHSUNDEEP
rdataframedata.table
discipulus
discipulus

Reputation: 2725

Copy column from another data frame to based on row names from a different data frame

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

Answers (2)

David Arenburg
David Arenburg

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

statespace
statespace

Reputation: 1664

try match():

df2$m1 <- df1$m1[match(row.names(df2), row.names(df1))]

Upvotes: 2

Related Questions