I have two data tables with lots of columns. The columns are the same but they are from different time points (one is from 2015 and one is from today). The structure of the data tables is roughly something like this:
library(data.table)
dt1 <- data.table(id = c("A", "B", "C"), i = c(2,4,6), a = c(1,2,3), w = c(2,3,4), f = c(2,3,5))
old_dt1 <- data.table(id = c("A", "B", "C"), i = c(1,2,6), a = c(1,1,1), w = c(2,1,2), f = c(1,3,1))
I would like to join them by id but I want that the columns with the same name are placed next to each other. My problem is that when I merge (which is expected) I get the following result:
> merge(dt1, old_dt1, by = "id", suffixes = c("", "-2015"))
id i a w f i-2015 a-2015 w-2015 f-2015
1: A 2 1 2 2 1 1 2 1
2: B 4 2 3 3 2 1 1 3
3: C 6 3 4 5 6 1 2 1
I know I can manually reorder the data table by setcolorder but I was wondering if I am missing something simple (unfortunately the columns are not in alphabetical order so that is not an option...)
What I would like to get is the following:
result <- merge(dt1, old_dt1, by = "id", suffixes = c("", "-2015"))
setcolorder(result, c(1,2,6,3,7,4,8,5,9))
> result
id i i-2015 a a-2015 w w-2015 f f-2015
1: A 2 1 1 1 2 2 2 1
2: B 4 2 2 1 3 1 3 3
3: C 6 6 3 1 4 2 5 1