I have a dataframe with 163 columns with unique names, say (x1, x2, x3, x4 ....) each of these columns has a series of returns (0.248, -0.00384, etc).
**DF1**
x1 x2 x3 x4 .....
0.248 -0.00384 0.0394 0.0053 .....
In another dataframe I have one column with a list of the same unique names (x1, x2, x3, x4 ...) and another with a list of numbers (0.005, .001, etc).
**DF2**
x1 0.005
x2 0.001
x3 0.005
x4 0.0005
...
My ultimate goal is to return a dataframe with the 163 columns (x1, x2, etc) with each columns net returns (so the x1 column is the original returns subtracted by 0.005, x2 column is the original returns subtracted by 0.001, etc).
So far I tried sorting the second dataframe, transposing and using unlist to get a vector of numbers, which I could then subtract from each column (but I cant figure out how to subtract from each column). Any help with figuring out how to do this would be really helpful. The list of unique names in the second dataframe are the same as the first dataframe, which is also sorted alphabetically, so it doesnt need to be matched up (both sorted will line up).
In case, the colnames of DF1 and rownames of DF2 are not in the same order, you can use match
DF1-DF2[match(colnames(DF1), rownames(DF2)),1][col(DF1)]
Should work if either or both datasets are not in the same order. For example
set.seed(65)
DF1N <- DF1[,sample(colnames(DF1))]
DF1N-DF2[match(colnames(DF1N), rownames(DF2)),1][col(DF1N)]
If they are in order
DF1-DF2[,1][col(DF1)]
set.seed(24)
DF1 <- as.data.frame(matrix(rnorm(40*10), ncol=10,
dimnames=list(NULL, paste0("x", 1:10))) )
set.seed(42)
DF2 <- data.frame(Col1=rnorm(10, 0.01))
row.names(DF2) <- sample(paste0("x",1:10))