Search code examples
rsumaggregatedivide

Adding row values then dividing between data frames

The first data frame I have looks like this:

ID 2016 2017
1  5    6
2  15   20
3  10   10

The second data frame is identical, but with different values:

ID 2016 2017
1  20   30
2  50   40
3  10   15

I'd like to add ID numbers 1 and 3 in each table, then divide the first data frame by the second in a new data frame. I'd also like to divide ID 2 in the first table by ID 2 in the second. Finally, I'd like the new data frame to have row names with the results of these calculations, so:

Type 2016 2017
A    0.5  0.36
B    0.3  0.5

Row A would be the result of ID 1 and 3, while Row B is the result of ID 2.

Solution

  • For this, we can also use base R. Get the colSums of subset of rows of both datasets, divide, rbind with the division of 2nd rows of each dataset

    cbind(Type = c('A', 'B'), rbind.data.frame(colSums(df1[-2, 
     -1])/colSums(df2[-2, -1]), df1[2, -1]/df2[2, -1]))
#   Type 2016      2017
#1    A  0.5 0.3555556
#2    B  0.3 0.5000000

    Here, the subsetting is done for rows and columns with index

    df2[-2, -1]

    implies, we remove the 2nd rows and the first column. The indexing is row,column. If it is positive, then we are keeping that rows/columns. Here, those rows/columns are removed.

    data

    df1 <- structure(list(ID = 1:3, `2016` = c(5L, 15L, 10L), `2017` = c(6L, 
20L, 10L)), class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(ID = 1:3, X2016 = c(20L, 50L, 10L), X2017 = c(30L, 
40L, 15L)), class = "data.frame", row.names = c(NA, -3L))