0

I have an 'Exp' data set which looks like this: 

  Locals   Res   Ind
1    112 7.865 4.248
2    113 4.248 5.666
3    114 5.666 2.444
4    115 2.444 7.865
5    116 7.865 4.248
6    117 4.248 6.983
7    118 5.666 3.867
8    119 2.444 2.987

And I have another data set called 'Com' as below: 

113 112 113
112 114 119
116 118 119
118 118 119
117 117 119
117 117 119

Based on the value in the 'Com' data set, I have pulled a corresponding value as part of the 'Res' and 'Ind' matrix. For eg - The first value in the Com data set is 113 so in the Res matrix the first value will be 4.248 - because the corresponding value in Exp's first column of 113 is 4.248. Such that the 'Res' matrix looks something like this

4.248   7.865   4.248
7.865   5.666   2.444
7.865   5.666   2.444
5.666   5.666   2.444
4.248   4.248   2.444
4.248   4.248   2.444

Now based on the above 'Res' data set, I want to deduct the first row by 2 and rest of the rows by 5. Then I want to add up the values of the above data set based on the value of the Com data set. For eg there are two 113 in the Com data set, the corresponding values in the Res data set is (4.248 - 2) + (4.248 - 2). So it is equal to 4.496. The Output table should look like something below:

Locals  Dam
112 8.73
113 4.496
114 0.666
115 0
116 2.865
117 -3.008
118 1.998
119 -12.78

Can anybody please suggest a easy way to do this on R. If this is done on a list of data then what is the best way

Thanks in advance

Saurabh Chauhan
  • 3,161
  • 2
  • 19
  • 46
Sunichie
  • 25
  • 4

2 Answers2

0

Here's a simple way with base to get you almost there:

Res = matrix(Exp$Res[match(Com, Exp$Locals)], nrow = nrow(Com))

subtract = Res
subtract[] = 5
subtract[1, ] = 2

Res = Res - subtract
output = data.frame(Locals = as.vector(Com), Dam = as.vector(Res))
output
#    Locals    Dam
# 1     113  2.248
# 2     112  2.865
# 3     116  2.865
# 4     118  0.666
# 5     117 -0.752
# 6     117 -0.752
# 7     112  5.865
# 8     114  0.666
# 9     118  0.666
# 10    118  0.666
# 11    117 -0.752
# 12    117 -0.752
# 13    113  2.248
# 14    119 -2.556
# 15    119 -2.556
# 16    119 -2.556
# 17    119 -2.556
# 18    119 -2.556

At this point, we just need to sum by group. There are many ways to do this, I'd encourage you to pick your favorite from the FAQ on How to sum a variable by group? To avoid external dependencies, here's a way using the built-in function aggregate:

aggregate(Dam ~ Locals, data = output, FUN = sum)
#   Locals     Dam
# 1    112   8.730
# 2    113   4.496
# 3    114   0.666
# 4    116   2.865
# 5    117  -3.008
# 6    118   1.998
# 7    119 -12.780

At this point, if you really need the 0 entry for 115, you could join to data.frame(Local = unique(Exp$Locals)) or use tidyr::complete to fill in the holes. I'll leave that as an exercise to the reader.

If this is done on a list of data then what is the best way

If you want to do this to a list of data frames, turn it into a function. If you have trouble with that, ask a new question providing a suitable sample inputs in a list - I'm not sure at this point whether you would want to use a list of Exp, Com, or both, so I won't make a guess here.

Using this data as input:

Exp = read.table(text = "  Locals   Res   Ind
1    112 7.865 4.248
2    113 4.248 5.666
3    114 5.666 2.444
4    115 2.444 7.865
5    116 7.865 4.248
6    117 4.248 6.983
7    118 5.666 3.867
8    119 2.444 2.987", header = T)

Com = as.matrix(read.table(text = "113 112 113
112 114 119
116 118 119
118 118 119
117 117 119
117 117 119"))
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
0

This exercise can be solved in many ways, I will present one using some basic R functions that may come in hand.

  • First the Res matrix, we can create it with a for loop and the which function in the following way.

    Resm <- matrix(0, nrow = nrow(com), ncol = ncol(com)) for (i in 1:nrow(com)) { for (j in 1:ncol(com)) { Resm[i,j] <-Exp$Res[which(Exp$Locals==com[i,j])] } } Resm

  • The subtraction is just simple matrix subtraction

    subM <- matrix(c(2, 2, 2, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5), ncol = 3,byrow=T) Resm2 <- Resm - subM

  • The final answer is a function applied to the Res matrix based on the indices in the com, so:

    coms <- as.integer(names(table(com))) Dam <- sapply(coms, function(x){sum(Resm2[which(com==x)])}) data.frame(Locals = coms, Dam)

Tuco
  • 116
  • 9