Pull coefficients from a data frame based on information in another data frame

3
votes

Right now I have two data frames in R, contains some data that looks like this:

> data
  p a         i
1 1 1 2.2561469
2 5 2 0.2316390
3 2 3 0.4867456
4 3 1 0.1511705
5 4 2 0.8838884

And one the contains coefficients that looks like this:

> coef
         3        2        1
1 29420.50 31029.75 29941.96
2 26915.00 27881.00 27050.00
3 27756.00 28904.00 28699.40
4 28345.33 29802.33 28377.56
5 28217.00 29409.00 28738.67

These data frames are connected as each value in data$a corresponds to a column name in coef and data$p corresponds to row names in coef.

I need to apply these coefficients to multiply these coefficients by the values in data$i by matching the row and column names in coef to data$a and data$p.

In other words, for each row in data, I need to use data$a and data$p for each row to pull a specific number from coef that will be multiplied by the value of data$i for that row to create a new vector in data that looks something like this:

> data
  p a         i          z
1 1 1 2.2561469      67553
2 5 2 0.2316390       6812
3 2 3 0.4867456         .
4 3 1 0.1511705         .
5 4 2 0.8838884         .

I was thinking I should create factors in my coef data frame based on the row and column names but am unsure of where to go from there.

Thanks in advance,

Ian

1
r

1 Answers

2
votes

If you order your coef data.frame, you can just index them as though the column names weren't there.

coef <- coef[,order(names(coef))]

Then apply a function to each row:

myfun <- function(x) {
  x[3]*coef[x[1], x[2]]
}

data$z <- apply(data, 1, myfun)

> data
  p a         i         z
1 1 1 2.2561469 67553.460
2 5 2 0.2316390  6812.271
3 2 3 0.4867456 13100.758
4 3 1 0.1511705  4338.503
5 4 2 0.8838884 26341.934
>