CODEWITHSUNDEEP
rdataframe
EricaO
EricaO

Reputation: 471

Subtract values in one dataframe from another

I have two data frames: (these are shortened versions of them)

A

    Link    VU  U   P
1   DVH1    7   1   37
2   DVH2    7   0   38
3   DVH3    10  1   35

B

    Link    VU  U   P
1   DVH1    2   0   15
2   DVH2    4   0   14
3   DVH3    0   0   5

I want to substract the values in data frame B from those in A based on their location. So for example: For DVH1, VU would be 7-2 (or 5), and the resulting data frame would look like:

    Link    VU  U   P
1   DVH1    5   1   22
2   DVH2    3   0   24
3   DVH3    10  1   30

Upvotes: 18

Views: 21793

Answers (3)

petzi
petzi

Reputation: 1636

If the values of the two data frames are in the same position, and the columns have the same names, then merge() is not necessary anymore:

## provide the sample data
df1 <- tibble::tibble(
    Link = c("DVH1", "DVH2", "DVH3"),
    VU = c(7,7,10),
    U = c(1,0,1),
    P = c(37, 38, 35)
)

df2 <- tibble::tibble(
    Link = c("DVH1", "DVH2", "DVH3"),
    VU = c(2,4,0),
    U = c(0, 0, 0),
    P = c(15, 14, 5)
)

df1
#> # A tibble: 3 × 4
#>   Link     VU     U     P
#>   <chr> <dbl> <dbl> <dbl>
#> 1 DVH1      7     1    37
#> 2 DVH2      7     0    38
#> 3 DVH3     10     1    35

df2
#> # A tibble: 3 × 4
#>   Link     VU     U     P
#>   <chr> <dbl> <dbl> <dbl>
#> 1 DVH1      2     0    15
#> 2 DVH2      4     0    14
#> 3 DVH3      0     0     5

## subtract df2 from df1
df3 <- df1[, 2:4] - df2[, 2:4]
dplyr::bind_cols(df1[, 1], df3)
#> # A tibble: 3 × 4
#>   Link     VU     U     P
#>   <chr> <dbl> <dbl> <dbl>
#> 1 DVH1      5     1    22
#> 2 DVH2      3     0    24
#> 3 DVH3     10     1    30

Created on 2024-02-22 with reprex v2.1.0

Upvotes: 0

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

A faster way than merge (most likely) is to just make sure the second data.frame is in the same row and column order as the first and subtract them from each other:

z <- names(A)[-1]
cbind(A[1], A[z] - B[match(A$Link, B$Link), z])
#   Link VU U  P
# 1 DVH1  5 1 22
# 2 DVH2  3 0 24
# 3 DVH3 10 1 30

Here's some sample data:

A <- structure(list(Link = c("DVH1", "DVH2", "DVH3"), VU = c(7L, 7L, 
10L), U = c(1L, 0L, 1L), P = c(37L, 38L, 35L)), .Names = c("Link", 
"VU", "U", "P"), class = "data.frame", row.names = c("1", "2", "3"))

B <- structure(list(Link = c("DVH1", "DVH3", "DVH2"), P = c(15L, 5L, 
14L), U = c(0L, 0L, 0L), VU = c(2L, 0L, 4L)), .Names = c("Link", 
"P", "U", "VU"), class = "data.frame", row.names = c("1", "3", "2"))

Upvotes: 11

Ferdinand.kraft
Ferdinand.kraft

Reputation: 12819

Use this:

within(merge(A,B,by="Link"), {
    VU <- VU.x - VU.y
    U <- U.x - U.y
    P <- P.x - P.y
})[,c("Link","VU","U","P")]

EDIT: Bonus: if there are too many paired columns (not just VU, U and P) you can use this:

M <- merge(A,B,by="Link")

S <- M[,grepl("*\\.x$",names(M))] - M[,grepl("*\\.y$",names(M))]

cbind(M[,1,drop=FALSE],S)

#  Link VU.x U.x P.x
#1 DVH1    5   1  22
#2 DVH2    3   0  24
#3 DVH3   10   1  30

Upvotes: 14

Related Questions