1

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
Mataunited18
  • 598
  • 1
  • 7
  • 20
  • 1
    Not clear what you need. Also how is `50 / 200` Inf? – Sotos May 29 '18 at 12:40
  • @Sotos I added another example, but I am not sure if it helps. I corrected the error now regarding Inf. – Mataunited18 May 29 '18 at 12:42
  • You noticed that the values 20 are divided by 200,100 and 300, which refers back to `dat1`. `dat1[1,1]` has value 2 which means that it should be divided by the corresponding value in `dat3` (i.e. `dat3[2,1]` or 200). – Mataunited18 May 29 '18 at 12:47

1 Answers1

1

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
PKumar
  • 10,971
  • 6
  • 37
  • 52
  • Seems like what I am looking for. However, how can I do it for my data set with length 80 for dat1? – Mataunited18 May 29 '18 at 13:29
  • I get a debug error message when I try using 1:80 with my data set with message " invalid subscript type 'list'". Do you happen to know what might the issue be? – Mataunited18 May 29 '18 at 13:41
  • 1
    @Mataunited17 Copy and paste entire error, unable to tell without seeing i – PKumar May 29 '18 at 13:47
  • Here it is: Error in dat3[x, dat1[x, ]] : invalid subscript type 'list' Called from: FUN(X[[i]], ...) – Mataunited18 May 29 '18 at 13:54
  • 2
    @Mataunited17 so its seems your matrix is not uniform after all. You have to make a better example to cover your problem in detail. The error means that that given dimesion(s) is/are not matching – PKumar May 29 '18 at 14:02
  • I've added output from my data set. I thought I made good examples reflecting my real data set. Thanks for the help btw. I have been struggling with this issue for a couple days now.@PKumar – Mataunited18 May 29 '18 at 14:15
  • 1
    @Mataunited17 hmmm, try adding dimrnsion info as welll in your question, like no of actual rows and cols in all the three matrix. It will give more perspective. O m hopeful that you will be able to solve it. Thanks – PKumar May 29 '18 at 14:19
  • Just did! `dat1` and `dat2` have the same column names, and reflect values for the same variables, but `dat3` is the `rowSum` of `dat2` (for my data). – Mataunited18 May 29 '18 at 14:27
  • 1
    @Mataunited17, give me some time i will update my solution – PKumar May 29 '18 at 15:01
  • It should be very to easy to do so in Excel, but much harder to do in R. – Mataunited18 May 29 '18 at 15:45
  • 1
    @Mataunited17 Updated the answer, No its easier in R :) – PKumar May 29 '18 at 16:41
  • 1
    Thank you so much!!! You are the best. This was creating so much headache for me. I can't thank you enough!! – Mataunited18 May 30 '18 at 19:10