How to add more data to a generated dataset-CodePudding

I would like to make one more addition to the code below. Note that the All dataset does not include only 05/07 and 06/07. However, I would like to add somehow to the All dataset. In this case both coef and Result would have the same value, which is the coef value, so the final output table would look like this:

date2      Category  coef   Result
  <date>     <chr>    <dbl>  <dbl>
1 2021-06-30 FDE          4      0
2 2021-06-30 ABC          1     -1
3 2021-07-04 FDE          6      3
4 2021-07-05 ABC          3      3
5 2021-07-06 ABC          6      6

Executable code below:

library(dplyr)
library(tidyverse)
library(lubridate)

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

df2 <- structure(list(date2 = c("2021-06-30", "2021-06-30", "2021-07-04", "2021-07-05","2021-07-06"), 
                      Category = c("FDE", "ABC", "FDE", "ABC","ABC"), coef = c(4, 1, 6, 3,6)), 
                 class = "data.frame", row.names = c(NA, -5L))

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

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

All<-All %>% 
  mutate(across(date1:date2, as.Date)) %>% 
  pivot_longer(starts_with('coef-'), values_to = 'Result') %>% 
  filter(date2 - date1 == as.numeric(str_sub(name, -2))) %>% 
  select(-date1,-name)
> All
# A tibble: 3 x 4
  date2      Category  coef Result
  <date>     <chr>    <dbl>  <dbl>
1 2021-06-30 FDE          4      0
2 2021-06-30 ABC          1     -1
3 2021-07-04 FDE          6      3

New database to test with 6 dates

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

df2<- structure(list(date2 = c("2021-06-30", "2021-06-30", "2021-07-04", "2021-07-05","2021-07-10","2021-07-11"), 
                      Category = c("FDE", "ABC", "FDE", "ABC","ABC","ABC"), coef = c(4, 1, 6, 3, 6,1)), 
                 class = "data.frame", row.names = c(NA, -6L))

New database to test with 8 dates

df1 <- structure(list(date1 = c("2021-06-28", "2021-06-28", "2021-06-28", "2021-06-28","2021-06-28","2021-06-28","2021-06-28","2021-06-28"), 
                      date2 = c("2021-06-30", "2021-06-30", "2021-07-04", "2021-07-05","2021-07-10","2021-07-13","2021-07-20","2021-07-22"), 
                      Category = c("FDE", "ABC", "FDE", "ABC","ABC","ABC","ABC","FDE"), 
                      Week = c("Wednesday", "Wednesday", "Friday", "Sunday","Thursday","Wednesday","Saturday","Sunday"), 
                      DR1 = c(4, 1, 6, 3, 3, 1,2,4), DR01 = c(4, 1, 4, 3, 2, 4,1,4), DR02 = c(4, 2, 6, 2, 2, 1,2,2), 
                      DR03 = c(9, 5, 4, 7,3, 3,3,2), DR04 = c(5, 4, 3, 2, 3, 8,2,2), DR05 = c(5, 4, 5, 4, 3, 5,4,4), 
                      DR06 = c(2, 4, 3, 2, 2, 5,4,2),DR07 = c(1, 4, 3, 2, 2, 5,4,5)), class = "data.frame", row.names = c(NA, -8L))

df2<- structure(list(date2 = c("2021-06-30", "2021-06-30", "2021-07-04", "2021-07-05","2021-07-10","2021-07-13","2021-07-20","2021-07-22"), 
                      Category = c("FDE", "ABC", "FDE", "ABC","ABC","ABC","ABC","FDE"), coef = c(4, 1, 6, 3, 6, 5, 11,14)), 
                 class = "data.frame", row.names = c(NA, -8L))
# A tibble: 7 x 4
  date2      Category  coef Result
  <date>     <chr>    <dbl>  <dbl>
1 2021-06-30 FDE          4      0
2 2021-06-30 ABC          1     -1
3 2021-07-04 FDE          6      3
4 2021-07-05 ABC          3      3
5 2021-07-05 ABC          3      1
6 2021-07-10 ABC          6      6
7 2021-07-13 ABC          5      5

CodePudding user response：

Update III: Now it should work!!! (Removed prior code)

library(tidyverse)
library(lubridate)

df1 %>% 
  left_join(df2, by = c("date2", "Category")) %>%
  select(-c(Week, DR1)) %>% 
  mutate(across(starts_with("DR"), ~ coef - .),
         across(contains("date"), ymd),
         datedif = parse_number(as.character(date2-date1)),
         across(starts_with("DR"), .names = "{str_replace(.col, 'DR0', 'DR_')}")
         ) %>% 
  rowwise %>%
  mutate(Result = if (str_c('DR_', datedif) %in% names(.)) get(str_c('DR_', datedif)) else coef) %>%
  ungroup() %>% 
  select(date2, Category, coef, Result)

date2      Category  coef Result
  <date>     <chr>    <dbl>  <dbl>
1 2021-06-30 FDE          4      0
2 2021-06-30 ABC          1     -1
3 2021-07-04 FDE          6      3
4 2021-07-05 ABC          3      1
5 2021-07-10 ABC          6      6
6 2021-07-13 ABC          5      5
7 2021-07-20 ABC         11     11
8 2021-07-22 FDE         14     14