I have three data frames with values. They are related to each other and have the same column names but show different things.
dat1 = cbind(a = c(2,1,3),
b = c(2,2,3),
c = c(2,2,2))
a b c
[1,] 2 2 2
[2,] 1 2 2
[3,] 3 3 2
dat2 = cbind(a = c(20, 20, 20),
b = c(30, 30, 30),
c = c(50, 50, 50))
a b c
[1,] 20 30 50
[2,] 20 30 50
[3,] 20 30 50
dat3 = cbind("1" = c(100,100,100),
"2" = c(200, 200, 200),
"3" = c(300, 300, 300))
1 2 3
[1,] 100 200 300
[2,] 100 200 300
[3,] 100 200 300
Ignore the pattern of the values in dat2 and dat3. It is just for illustration. My data set has more random values.
to get result
a b c
[1,] 0.100 0.150 0.25
[2,] 0.200 0.150 0.25
[3,] 0.067 0.067 0.25
which is basically
1 2 3
[1,] "20/200" "30/200" "50/200"
[2,] "20/100" "30/200" "50/200"
[3,] "20/300" "30/300" "50/200"
The rationale is that if the values in dat1 has the value 1 then take the corresponding value from dat2 and divide by the corresponding values and column in dat3. Similarly, if the values in dat1 has the value 2 in dat1, then take the corresponding value from dat2 (with the same "position") and divide by the corresponding value and column in dat3. The same goes for the values of 3.
For example, let's look at dat1 and values of 1. We only have one data point with the value 1, and it is dat2[2,1] with value 20. Take 20 and divide by dat3[2,1]. Or look at dat2[3,1] with value 3 from dat1. Take dat2[3,1] (with value 20) and divide by 300 in dat3 because it is assigned value 3 in dat1.
Is there a way to do this?
Here is output from my data using dput() for dat1 and dat3 respectively.
structure(list(DNB = c(1, 1, 1), `NORSK HYDRO` = c(2, 2, 2),
ORKLA = c(1, 1, 1), STOREBRAND = c(1, 1, 2), ATEA = c(1,
1, 1), `SCHIBSTED A` = c(2, 2, 2), BONHEUR = c(1, 1, 1),
EKORNES = c(1, 1, 1), `KONGSBERG GRUPPEN` = c(2, 2, 2), `TOMRA SYSTEMS` = c(1,
1, 1), VEIDEKKE = c(1, 1, 2), `ARENDALS FOSSEKOMPANI` = c(2,
2, 2), `OLAV THON EIEP.` = c(2, 2, 2), `PETROLEUM GEO SERVICES` = c(2,
2, 2), `SPAREBANK 1 SR BANK` = c(2, 2, 2), `STOLT-NIELSEN` = c(2,
2, 2), `ODFJELL 'A'` = c(1, 1, 1), `SPAREBANK 1 NORD-NORGE` = c(2,
2, 2), `SPAREBANK 1 SMN` = c(2, 2, 2), `WILHS.WILHELMSEN HDG.'A'` = c(2,
2, 2), `NORDEA BANK (~NK)` = c(2, 2, 2), `ATLAS COPCO 'A' (~NK)` = c(2,
2, 2), `VOLVO 'B' (~NK)` = c(2, 2, 2), `SANDVIK (~NK)` = c(2,
2, 2), `SWEDBANK 'A' (~NK)` = c(1, 1, 1), `ERICSSON 'B' (~NK)` = c(1,
1, 1), `SVENSKA HANDBKN.'A' (~NK)` = c(1, 1, 1), `HENNES & MAURITZ 'B' (~NK)` = c(2,
2, 2), `SEB 'A' (~NK)` = c(2, 2, 2), `INVESTOR 'B' (~NK)` = c(1,
1, 1), `SWEDISH MATCH (~NK)` = c(1, 1, 1), `ELECTROLUX 'B' (~NK)` = c(2,
2, 2), `SKANSKA 'B' (~NK)` = c(2, 2, 1), `SCA 'B' (~NK)` = c(2,
2, 2), `SECURITAS 'B' (~NK)` = c(2, 2, 2), `HOLMEN 'B' (~NK)` = c(2,
2, 2), `SSAB 'A' (~NK)` = c(1, 1, 1), `ERICSSON 'A' (~NK)` = c(2,
2, 2), `INVESTOR 'A' (~NK)` = c(2, 2, 2), `VOLVO 'A' (~NK)` = c(2,
2, 2), `NOVO NORDISK 'B' (~NK)` = c(2, 2, 2), `DANSKE BANK (~NK)` = c(1,
1, 1), `COLOPLAST 'B' (~NK)` = c(2, 2, 3), `CARLSBERG 'B' (~NK)` = c(2,
2, 2), `A P MOLLER - MAERSK 'B' (~NK)` = c(2, 2, 2), `TDC (~NK)` = c(2,
2, 2), `TOPDANMARK (~NK)` = c(2, 2, 2), `WILLIAM DEMANT HLDG. (~NK)` = c(3,
3, 2), `JYSKE BANK (~NK)` = c(1, 1, 1), `KOBENHAVNS LUFTHAVNE (~NK)` = c(2,
2, 1), `NKT (~NK)` = c(1, 1, 1), `ROCKWOOL 'B' (~NK)` = c(2,
2, 2), `SYDBANK (~NK)` = c(2, 2, 2), `FLSMIDTH & CO.'B' (~NK)` = c(2,
2, 1), `GN STORE NORD (~NK)` = c(2, 2, 2), `ALK-ABELLO (~NK)` = c(2,
2, 2), `BANG & OLUFSEN 'B' (~NK)` = c(3, 3, 2), `SANTA FE GROUP (~NK)` = c(2,
2, 2), `CARLSBERG 'A' (~NK)` = c(2, 2, 2), `ROCKWOOL 'A' (~NK)` = c(2,
2, 2), `NOKIA (~NK)` = c(1, 1, 1), `SAMPO 'A' (~NK)` = c(1,
1, 1), `KONE 'B' (~NK)` = c(2, 2, 2), `UPM-KYMMENE (~NK)` = c(1,
1, 1), `WARTSILA (~NK)` = c(1, 1, 1), `METSO (~NK)` = c(1,
1, 1), `STORA ENSO 'R' (~NK)` = c(2, 2, 2), `HUHTAMAKI (~NK)` = c(1,
1, 1), `FINNAIR (~NK)` = c(2, 2, 2), `KEMIRA (~NK)` = c(1,
1, 1), `UPONOR (~NK)` = c(1, 1, 1), `KESKO 'B' (~NK)` = c(1,
1, 2), `ORION 'B' (~NK)` = c(2, 2, 2), `OUTOKUMPU 'A' (~NK)` = c(2,
2, 2), `RAISIO (~NK)` = c(2, 2, 2), `TIETO OYJ (~NK)` = c(1,
1, 1), `METSA BOARD 'B' (~NK)` = c(2, 2, 2), `ORION 'A' (~NK)` = c(2,
2, 2), `STOCKMANN 'A' (~NK)` = c(2, 2, 2), `STORA ENSO 'A' (~NK)` = c(2,
2, 2)), .Names = c("DNB", "NORSK HYDRO", "ORKLA", "STOREBRAND",
"ATEA", "SCHIBSTED A", "BONHEUR", "EKORNES", "KONGSBERG GRUPPEN",
"TOMRA SYSTEMS", "VEIDEKKE", "ARENDALS FOSSEKOMPANI", "OLAV THON EIEP.",
"PETROLEUM GEO SERVICES", "SPAREBANK 1 SR BANK", "STOLT-NIELSEN",
"ODFJELL 'A'", "SPAREBANK 1 NORD-NORGE", "SPAREBANK 1 SMN", "WILHS.WILHELMSEN HDG.'A'",
"NORDEA BANK (~NK)", "ATLAS COPCO 'A' (~NK)", "VOLVO 'B' (~NK)",
"SANDVIK (~NK)", "SWEDBANK 'A' (~NK)", "ERICSSON 'B' (~NK)",
"SVENSKA HANDBKN.'A' (~NK)", "HENNES & MAURITZ 'B' (~NK)", "SEB 'A' (~NK)",
"INVESTOR 'B' (~NK)", "SWEDISH MATCH (~NK)", "ELECTROLUX 'B' (~NK)",
"SKANSKA 'B' (~NK)", "SCA 'B' (~NK)", "SECURITAS 'B' (~NK)",
"HOLMEN 'B' (~NK)", "SSAB 'A' (~NK)", "ERICSSON 'A' (~NK)", "INVESTOR 'A' (~NK)",
"VOLVO 'A' (~NK)", "NOVO NORDISK 'B' (~NK)", "DANSKE BANK (~NK)",
"COLOPLAST 'B' (~NK)", "CARLSBERG 'B' (~NK)", "A P MOLLER - MAERSK 'B' (~NK)",
"TDC (~NK)", "TOPDANMARK (~NK)", "WILLIAM DEMANT HLDG. (~NK)",
"JYSKE BANK (~NK)", "KOBENHAVNS LUFTHAVNE (~NK)", "NKT (~NK)",
"ROCKWOOL 'B' (~NK)", "SYDBANK (~NK)", "FLSMIDTH & CO.'B' (~NK)",
"GN STORE NORD (~NK)", "ALK-ABELLO (~NK)", "BANG & OLUFSEN 'B' (~NK)",
"SANTA FE GROUP (~NK)", "CARLSBERG 'A' (~NK)", "ROCKWOOL 'A' (~NK)",
"NOKIA (~NK)", "SAMPO 'A' (~NK)", "KONE 'B' (~NK)", "UPM-KYMMENE (~NK)",
"WARTSILA (~NK)", "METSO (~NK)", "STORA ENSO 'R' (~NK)", "HUHTAMAKI (~NK)",
"FINNAIR (~NK)", "KEMIRA (~NK)", "UPONOR (~NK)", "KESKO 'B' (~NK)",
"ORION 'B' (~NK)", "OUTOKUMPU 'A' (~NK)", "RAISIO (~NK)", "TIETO OYJ (~NK)",
"METSA BOARD 'B' (~NK)", "ORION 'A' (~NK)", "STOCKMANN 'A' (~NK)",
"STORA ENSO 'A' (~NK)"), row.names = c(NA, 3L), class = "data.frame")
structure(c(572008.53, 617720.24, 654277.81, 686839.49, 736058.9,
714108.91, 8344.65, 9753.26, 5407.72), .Dim = c(3L, 3L), .Dimnames = list(
c("1", "2", "3"), c("1", "2", "3")))
Here are the dimensions of the actual data.
dat1 using dim() gives
[1] 252 80
dat2 using dim() gives
[1] 252 80
dat3 using dim() gives
[1] 252 3
You looking for something like this I believe:
YOu need to convert the dat1 and dat2 to matrix not data frames.
dat1 <- as.matrix(dat1)
dat2 <- as.matrix(dat2)
dfx <- do.call('rbind', lapply(1:nrow(dat1),function(x)dat3[x,dat1[x,]]))
To recieve the ratio you need to divide dat2 by dfx(since no dat2 provided, I am leaving this for OP to solve):
dat2/dfx #This should give you final answer
Output:
> dat2/dfx
a b c
[1,] 0.10000000 0.15 0.25
[2,] 0.20000000 0.15 0.25
[3,] 0.06666667 0.10 0.25
Sample output
> do.call('rbind', lapply(1:nrow(dat1),function(x)dat3[x,c(dat1[x,])]))
1 2 1 1
[1,] 572008.5 686839.5 572008.5 572008.5
[2,] 617720.2 736058.9 617720.2 617720.2
[3,] 654277.8 714108.9 654277.8 714108.9
1 2 1 1
[1,] 572008.5 686839.5 572008.5 572008.5
[2,] 617720.2 736058.9 617720.2 617720.2
[3,] 654277.8 714108.9 654277.8 654277.8