multiply two dataframes by matching rows and columns
I would like to multiply two data frames or matrices by matching the rows in Df1 with column names in DF2. The data frames look like
Df1<-
| ID |Value|
|:---- |:----|
| 105 |213|
| 105 |214|
| 90 |215|
| 90 |216|
| 100 |217|
Df2<-
| 90 | 100 | 105 |
|:---- |:------:| -----:|
| 10 | 11 | 12 |
| 15 | 20 | 25 |
| 20 | 25 | 30 |
My goal is to match the 'ID' in Df1 with the colnames in Df2 and multiply the Value in Df1 with the matched columns in the Df2. The output should look like:
output<-
C1 | C2 | C3 |
---|---|---|
213*12 | 214*12 | 215*10 |
213*25 | 214*25 | 215*15 |
213*30 | 214*30 | 215*20 |
What would be the fastest and convenient method to do this. I am relatively new to the R and any help is highly appreciated.
Update
We can try
with(
df1,
as.data.frame(t(t(unname(df2[as.character(ID)])) * Value))
)
which gives
V1 V2 V3 V4 V5
1 2556 2568 2150 2160 2387
2 5325 5350 3225 3240 4340
3 6390 6420 4300 4320 5425
Maybe this base R code with reshape
+ merge
could help
reshape(
transform(
type.convert(
merge(df1, stack(df2), by.x = "ID", by.y = "ind", all = TRUE),
as.is = TRUE
),
idx = ave(ID, ID, Value, FUN = seq_along),
p = Value * values
)[c("Value", "idx", "p")],
direction = "wide",
idvar = "idx",
timevar = "Value"
)
which gives
idx p.215 p.216 p.217 p.213 p.214
1 1 3225 3240 5425 6390 6420
2 2 2150 2160 2387 2556 2568
3 3 4300 4320 4340 5325 5350
Data
df1 <- data.frame(
ID = c(105, 105, 90, 90, 100),
Value = 213:217
)
df2 <- data.frame(
`90` = c(10, 15, 20),
`100` = c(11, 20, 25),
`105` = c(12, 25, 30),
check.names = FALSE
)