CODEWITHSUNDEEP
rmatch
arezaie
arezaie

Reputation: 345

R:Matching columns with other columns in data frames and saving them transposed

I've got 2 data frames df1 and df2.

df1 <- data.frame(ID = c("aa", "ab", "ac", "ad", "ae", "af"), y = 1:6 )
df2 <- data.frame(x = c("aa","ac", "aa", "ac", "ab","aa","fa"), obj=c("F70", "F60", "F71", "F82", "F92", "F62"))

I want to matches the "ID" and "x" column df1 with df2, then save the obj column in df2 in df1 transposed with that match. Such that I get:

ID y 
aa 1 F70 F71 F62
ab 2 F92 NA  NA
ac 3 F60 F82 NA
ad 4 NA  NA  NA
ae 5 NA  NA  NA
af 6 NA  NA  NA

Upvotes: 0

Views: 42

Answers (1)

mt1022
mt1022

Reputation: 17299

you can do this by reformat df2 with data.table and then merge with df1:

library(data.table)
df1 <- data.frame(ID = c("aa", "ab", "ac", "ad", "ae", "af"), y = 1:6 )
df2 <- data.frame(x = c("aa","ac", "aa", "ac", "ab","aa"), obj=c("F70", "F60", "F71", "F82", "F92", "F62"))

# split and collapse df2
df2.collapse <- sapply(split(df2$obj, f = df2$x), function(x) paste(x, collapse = ','))

# make a new df2
df2.new <- data.frame(ID = names(df2.collapse), tstrsplit(df2.collapse, ','))

# merge with df1, you may want to change the colnames of new columns
merge(df1, df2.new, by = 'ID', all.x = T)
#   ID y c..F70....F92....F60.. c..F71...NA...F82.. c..F62...NA..NA.
# 1 aa 1                    F70                 F71              F62
# 2 ab 2                    F92                <NA>             <NA>
# 3 ac 3                    F60                 F82             <NA>
# 4 ad 4                   <NA>                <NA>             <NA>
# 5 ae 5                   <NA>                <NA>             <NA>
# 6 af 6                   <NA>                <NA>             <NA>

Upvotes: 1

Related Questions