Calculate difference between column values in R

Question

How do I generate a variable that can be called "PV" which will be the difference between the DX values and the DR column values.

For example:

PV of DR0 will be 25-19=6 for 03/01/2021 for Room Super

PV of DR1 will be 25-19=6 for for 03/01/2021 for Room Super

PV of DR2 will be 25-23=2 for for 03/01/2021 for Room Super

And so on.

df <- structure(
      list(date = c("01-01-2021","01-01-2021","01-01-2021","01-01-2021","01-01-2021","02-01-2021","02-01-2021",
"02-01-2021","02-01-2021","02-01-2021","03-01-2021","03-01-2021","03-01-2021","03-01-2021","03-01-2021","04-01-2021",
"04-01-2021","04-01-2021","04-01-2021","04-01-2021"),Room = c("Standard","Master","Luxury","Super","Deluxe","Standard","Master",
"Luxury","Super","Deluxe","Standard","Master","Luxury","Super","Deluxe","Standard","Master","Luxury","Super","Deluxe"),
DX=c(5,8,13,5,3,4,6,18,14,5,9,9,25,25,10,10,9,24,25,12),DR0=c(5,8,13,6,4,4,6,19,14,7,9,7,25,19,12,10,10,30,27,12),
DR1=c(5,8,12,6,3,4,6,19,15,8,9,7,27,19,12,10,8,29,23,12),DR2=c(5,8,12,6,3,4,6,18,15,7,9,7,27,23,16,10,8,31,23,12),
DR3=c(5,8,12,6,3,4,6,18,16,7,9,7,24,19,11,10,8,31,29,14),DR4=c(5,8,12,4,3,4,6,18,16,7,9,7,24,18,11,10,8,28,25,10),
DR5=c(5,8,12,4,3,4,6,18,15,7,9,7,24,18,11,10,8,28,23,10)),class = "data.frame", row.names = c(NA, -20L))

> df
         date     Room DX DR0 DR1 DR2 DR3 DR4 DR5
1  01-01-2021 Standard  5   5   5   5   5   5   5
2  01-01-2021   Master  8   8   8   8   8   8   8
3  01-01-2021   Luxury 13  13  12  12  12  12  12
4  01-01-2021    Super  5   6   6   6   6   4   4
5  01-01-2021   Deluxe  3   4   3   3   3   3   3
6  02-01-2021 Standard  4   4   4   4   4   4   4
7  02-01-2021   Master  6   6   6   6   6   6   6
8  02-01-2021   Luxury 18  19  19  18  18  18  18
9  02-01-2021    Super 14  14  15  15  16  16  15
10 02-01-2021   Deluxe  5   7   8   7   7   7   7
11 03-01-2021 Standard  9   9   9   9   9   9   9
12 03-01-2021   Master  9   7   7   7   7   7   7
13 03-01-2021   Luxury 25  25  27  27  24  24  24
14 03-01-2021    Super 25  19  19  23  19  18  18
15 03-01-2021   Deluxe 10  12  12  16  11  11  11
16 04-01-2021 Standard 10  10  10  10  10  10  10
17 04-01-2021   Master  9  10   8   8   8   8   8
18 04-01-2021   Luxury 24  30  29  31  31  28  28
19 04-01-2021    Super 25  27  23  23  29  25  23
20 04-01-2021   Deluxe 12  12  12  12  14  10  10

score 1 · Accepted Answer · answered Aug 18 '21 at 18:42

base R

tmp <- subset(df, select = DR0:DR5)
cbind(df, setNames(df$DX - tmp, paste0(names(tmp), "_PV")))
#          date     Room DX DR0 DR1 DR2 DR3 DR4 DR5 DR0_PV DR1_PV DR2_PV DR3_PV DR4_PV DR5_PV
# 1  01-01-2021 Standard  5   5   5   5   5   5   5      0      0      0      0      0      0
# 2  01-01-2021   Master  8   8   8   8   8   8   8      0      0      0      0      0      0
# 3  01-01-2021   Luxury 13  13  12  12  12  12  12      0      1      1      1      1      1
# 4  01-01-2021    Super  5   6   6   6   6   4   4     -1     -1     -1     -1      1      1
# 5  01-01-2021   Deluxe  3   4   3   3   3   3   3     -1      0      0      0      0      0
# 6  02-01-2021 Standard  4   4   4   4   4   4   4      0      0      0      0      0      0
# 7  02-01-2021   Master  6   6   6   6   6   6   6      0      0      0      0      0      0
# 8  02-01-2021   Luxury 18  19  19  18  18  18  18     -1     -1      0      0      0      0
# 9  02-01-2021    Super 14  14  15  15  16  16  15      0     -1     -1     -2     -2     -1
# 10 02-01-2021   Deluxe  5   7   8   7   7   7   7     -2     -3     -2     -2     -2     -2
# 11 03-01-2021 Standard  9   9   9   9   9   9   9      0      0      0      0      0      0
# 12 03-01-2021   Master  9   7   7   7   7   7   7      2      2      2      2      2      2
# 13 03-01-2021   Luxury 25  25  27  27  24  24  24      0     -2     -2      1      1      1
# 14 03-01-2021    Super 25  19  19  23  19  18  18      6      6      2      6      7      7
# 15 03-01-2021   Deluxe 10  12  12  16  11  11  11     -2     -2     -6     -1     -1     -1
# 16 04-01-2021 Standard 10  10  10  10  10  10  10      0      0      0      0      0      0
# 17 04-01-2021   Master  9  10   8   8   8   8   8     -1      1      1      1      1      1
# 18 04-01-2021   Luxury 24  30  29  31  31  28  28     -6     -5     -7     -7     -4     -4
# 19 04-01-2021    Super 25  27  23  23  29  25  23     -2      2      2     -4      0      2
# 20 04-01-2021   Deluxe 12  12  12  12  14  10  10      0      0      0     -2      2      2

dplyr

library(dplyr)
df %>%
  mutate(across(DR0:DR5, list(PV = ~ DX - .)))
#          date     Room DX DR0 DR1 DR2 DR3 DR4 DR5 DR0_PV DR1_PV DR2_PV DR3_PV DR4_PV DR5_PV
# 1  01-01-2021 Standard  5   5   5   5   5   5   5      0      0      0      0      0      0
# 2  01-01-2021   Master  8   8   8   8   8   8   8      0      0      0      0      0      0
# 3  01-01-2021   Luxury 13  13  12  12  12  12  12      0      1      1      1      1      1
# 4  01-01-2021    Super  5   6   6   6   6   4   4     -1     -1     -1     -1      1      1
# 5  01-01-2021   Deluxe  3   4   3   3   3   3   3     -1      0      0      0      0      0
# 6  02-01-2021 Standard  4   4   4   4   4   4   4      0      0      0      0      0      0
# 7  02-01-2021   Master  6   6   6   6   6   6   6      0      0      0      0      0      0
# 8  02-01-2021   Luxury 18  19  19  18  18  18  18     -1     -1      0      0      0      0
# 9  02-01-2021    Super 14  14  15  15  16  16  15      0     -1     -1     -2     -2     -1
# 10 02-01-2021   Deluxe  5   7   8   7   7   7   7     -2     -3     -2     -2     -2     -2
# 11 03-01-2021 Standard  9   9   9   9   9   9   9      0      0      0      0      0      0
# 12 03-01-2021   Master  9   7   7   7   7   7   7      2      2      2      2      2      2
# 13 03-01-2021   Luxury 25  25  27  27  24  24  24      0     -2     -2      1      1      1
# 14 03-01-2021    Super 25  19  19  23  19  18  18      6      6      2      6      7      7
# 15 03-01-2021   Deluxe 10  12  12  16  11  11  11     -2     -2     -6     -1     -1     -1
# 16 04-01-2021 Standard 10  10  10  10  10  10  10      0      0      0      0      0      0
# 17 04-01-2021   Master  9  10   8   8   8   8   8     -1      1      1      1      1      1
# 18 04-01-2021   Luxury 24  30  29  31  31  28  28     -6     -5     -7     -7     -4     -4
# 19 04-01-2021    Super 25  27  23  23  29  25  23     -2      2      2     -4      0      2
# 20 04-01-2021   Deluxe 12  12  12  12  14  10  10      0      0      0     -2      2      2

akrun · Answer 2 · 2021-08-18T19:07:14.417

We can use `tidyverse

library(dplyr)
library(stringr)
df %>%
    mutate(across(c(DR0:DR5), ~ DX - .,
         .names = '{str_replace(.col, "DR", "PV")}'))

-output

          date     Room DX DR0 DR1 DR2 DR3 DR4 DR5 PV0 PV1 PV2 PV3 PV4 PV5
1  01-01-2021 Standard  5   5   5   5   5   5   5   0   0   0   0   0   0
2  01-01-2021   Master  8   8   8   8   8   8   8   0   0   0   0   0   0
3  01-01-2021   Luxury 13  13  12  12  12  12  12   0   1   1   1   1   1
4  01-01-2021    Super  5   6   6   6   6   4   4  -1  -1  -1  -1   1   1
5  01-01-2021   Deluxe  3   4   3   3   3   3   3  -1   0   0   0   0   0
6  02-01-2021 Standard  4   4   4   4   4   4   4   0   0   0   0   0   0
7  02-01-2021   Master  6   6   6   6   6   6   6   0   0   0   0   0   0
8  02-01-2021   Luxury 18  19  19  18  18  18  18  -1  -1   0   0   0   0
9  02-01-2021    Super 14  14  15  15  16  16  15   0  -1  -1  -2  -2  -1
10 02-01-2021   Deluxe  5   7   8   7   7   7   7  -2  -3  -2  -2  -2  -2
11 03-01-2021 Standard  9   9   9   9   9   9   9   0   0   0   0   0   0
12 03-01-2021   Master  9   7   7   7   7   7   7   2   2   2   2   2   2
13 03-01-2021   Luxury 25  25  27  27  24  24  24   0  -2  -2   1   1   1
14 03-01-2021    Super 25  19  19  23  19  18  18   6   6   2   6   7   7
15 03-01-2021   Deluxe 10  12  12  16  11  11  11  -2  -2  -6  -1  -1  -1
16 04-01-2021 Standard 10  10  10  10  10  10  10   0   0   0   0   0   0
17 04-01-2021   Master  9  10   8   8   8   8   8  -1   1   1   1   1   1
18 04-01-2021   Luxury 24  30  29  31  31  28  28  -6  -5  -7  -7  -4  -4
19 04-01-2021    Super 25  27  23  23  29  25  23  -2   2   2  -4   0   2
20 04-01-2021   Deluxe 12  12  12  12  14  10  10   0   0   0  -2   2   2

Or in base R

nm1 <- paste0("DR", 0:5)
df[paste0("PR", 0:5)] <- df$DX[col(df[nm1])] - df[nm1]

Calculate difference between column values in R

2 Answers2

base R

dplyr