How to add more data to a generated dataset

[JojoSouza]

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

[technocrat]

suppressPackageStartupMessages({
  library(dplyr)
  library(lubridate)
  library(stringr)
  library(tidyr)})

df1 <- data.frame(
  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)
)

df2 <- data.frame(
  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)
)

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)

# conform df2$date to All$date2

df2$date2 <- ymd(df2$date2)

# bring in All
df2 <- left_join(df2,All)
#> Joining, by = c("date2", "Category", "coef")

# replace NA values
pos <- which(is.na(df2$Result))
df2[pos,"Result"] = df2[pos,"coef"]

df2
#>        date2 Category coef Result
#> 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

[JojoSouza]

Thanks for reply @technocrat However, the Result values were NA and not the same coef value for the days 07-05 and 07-06, as I mentioned in the question.

[technocrat]

Edited answer to provide.

[williaml]

I haven't used this before, but this looks like it does what you want. There are other packages at the bottom too: Simulation for Factorial Designs • faux