I have two dataframes.
a <- structure(list(sd.3.IID = structure(c(7L, 1L, 4L, 6L, 2L, 9L,
3L, 8L, 10L, 5L), .Label = c("0_62000_1", "0_62004_5", "0_62070_19",
"0_62070_23", "0_62070_27", "0_62070_33", "ADNI_1092", "ADNI_1263",
"ADNI_1283", "ADNI_1334"), class = "factor")), row.names = c(NA,
-10L), class = "data.frame")
b <- structure(list(sd.3 = c("0_62000_1", "0_62004_5", "0_62070_19",
"0_62070_23", "0_62070_27", "0_62070_33", "0_62071_1", "0_62071_15"
), sd.4 = c("0_62000_1", "0_62004_5", "0_62070_19", "0_62070_23",
"0_62070_27", "0_62070_33", "0_62071_1", "0_62071_15"), sd.5 = c("0_62000_1",
"0_62004_5", "0_62070_19", "0_62070_23", "0_62070_27", "0_62070_33",
"0_62071_1", "0_62071_15")), row.names = c(NA, 8L), class = "data.frame")
I want to merge these two and keep all column values based on by.x = "sd.3.IID", by.y = "sd.3". I tried
merge(x = a, y = b, by.x = "sd.3.IID", by.y = "sd.3", all = T) but it doesn’t keep sd.3 column. What do I need to do to get this right?
>Solution :
We can transform to create a new column before the merge and change the by.y with the new column
merge(x = a, y = transform(b, sd.3t = sd.3), by.x = "sd.3.IID",
by.y = "sd.3t", all = TRUE)
-output
sd.3.IID sd.3 sd.4 sd.5
1 0_62000_1 0_62000_1 0_62000_1 0_62000_1
2 0_62004_5 0_62004_5 0_62004_5 0_62004_5
3 0_62070_19 0_62070_19 0_62070_19 0_62070_19
4 0_62070_23 0_62070_23 0_62070_23 0_62070_23
5 0_62070_27 0_62070_27 0_62070_27 0_62070_27
6 0_62070_33 0_62070_33 0_62070_33 0_62070_33
7 ADNI_1092 <NA> <NA> <NA>
8 ADNI_1263 <NA> <NA> <NA>
9 ADNI_1283 <NA> <NA> <NA>
10 ADNI_1334 <NA> <NA> <NA>
11 0_62071_1 0_62071_1 0_62071_1 0_62071_1
12 0_62071_15 0_62071_15 0_62071_15 0_62071_15