Make adjustments to the generated output table-CodePudding

I would like to make two adjustments to the code below: 1) As you can see I use the left_join, where the coef value of the All dataset is subtracted from the DR0.. columns. Therefore, I would like to change the column names as per the output table I inserted into the image. 2) In the output table, I wouldn't want it to have date1, Week and DR1 as well. So I would like it to look exactly like my image output table.

library(dplyr)

df1 <- structure(list(date1 = c("2021-06-28", "2021-06-28", "2021-06-28", "2021-06-28"), date2 = c("2021-06-30", "2021-06-30", "2021-07-01", "2021-07-01"), Category = c("FDE", "ABC", "FDE", "ABC"), Week = c("Wednesday", "Wednesday", "Friday", "Friday"), DR1 = c(4, 1, 6, 3), DR01 = c(4, 1, 4, 3), DR02 = c(4, 2, 6, 2), DR03 = c(9, 5, 4, 7), DR04 = c(5, 4, 3, 2), DR05 = c(5, 4, 5, 4), DR06 = c(2, 4, 3, 2)), class = "data.frame", row.names = c(NA, -4L))
       date1      date2 Category      Week DR1 DR01 DR02 DR03 DR04 DR05 DR06
1 2021-06-28 2021-06-30      FDE Wednesday   4    4    4    9    5    5    2
2 2021-06-28 2021-06-30      ABC Wednesday   1    1    2    5    4    4    4
3 2021-06-28 2021-07-01      FDE    Friday   6    4    6    4    3    5    3
4 2021-06-28 2021-07-01      ABC    Friday   3    3    2    7    2    4    2

All <- structure(list(date2 = c("2021-06-30", "2021-06-30", "2021-07-01", "2021-07-01"), Category = c("FDE", "ABC", "FDE", "ABC"), coef = c(4L, 1L, 6L, 3L)), class = "data.frame", row.names = c("1", "2", "3", "4"))
           date2 Category coef
1 2021-06-30      FDE    4
2 2021-06-30      ABC    1
3 2021-07-01      FDE    6
4 2021-07-01      ABC    3

 left_join(All, df1, by = c("date2", "Category")) %>%
  mutate(across(starts_with("DR0"), ~ coef - .))

       date2 Category coef      date1      Week DR1 DR01 DR02 DR03 DR04 DR05 DR06
1 2021-06-30      FDE    4 2021-06-28 Wednesday   4    0    0   -5   -1   -1    2
2 2021-06-30      ABC    1 2021-06-28 Wednesday   1    0   -1   -4   -3   -3   -3
3 2021-07-01      FDE    6 2021-06-28    Friday   6    2    0    2    3    1    3
4 2021-07-01      ABC    3 2021-06-28    Friday   3    0    1   -4    1   -1    1

CodePudding user response：

You may try

library(dplyr)

v1<- df1 %>%
  select(starts_with("DR0")) %>% names %>%
  paste0("coef-",.)

left_join(All, df1, by = c("date2", "Category")) %>%
  mutate(across(starts_with("DR0"), ~ coef - .)) %>%
  select(-date1, -Week, -DR1) %>%
  rename_at(-c(1:3), ~v1)

       date2 Category coef coef-DR01 coef-DR02 coef-DR03 coef-DR04 coef-DR05 coef-DR06
1 2021-06-30      FDE    4         0         0        -5        -1        -1         2
2 2021-06-30      ABC    1         0        -1        -4        -3        -3        -3
3 2021-07-01      FDE    6         2         0         2         3         1         3
4 2021-07-01      ABC    3         0         1        -4         1        -1         1

left_join(All, df1, by = c("date2", "Category")) %>%
  mutate(across(starts_with("DR0"), ~ coef - .)) %>%
  select(-date1, -Week, -DR1) %>%
  rename_at(vars(starts_with("DR{0-9}")), ~v1)