I would like some help on a question, but I'm going to give an example because I believe it's easier to explain.
The general idea is to make a different table from a date I choose. In the table will add data from the PV variable with the DR data from my database df. I'll explain it better below:
As you can see I have date1 and date2 in my database. Date1 will always be constant, in this case it's 28/06. In date2 there are days that are days after 28/06, for example, in this database we have 01/07 (Thursday), 02/07 (Saturday) and 03/07 (Monday).
My idea is to make a function or something like that, if I, for example, chose 01/07, it would do the following calculation: 01/07 – 28/06 = 3 days. This value I would subtract from the amount of DR I have from my df database, which in this case is 7, that is, 7 – 3 = 4. This 4 refers to the amount of DR that I will consider for my table. These DR will always be backwards, that is, it starts with DR06 and not DR01. Examples for each date would be these values:
So the table for 01/07 would be:
If it is for the day 02/07, it would be 02/07 – 28/06 = 4 days. This value is subtracted from the amount of DR that I have in my database, that is, 7 – 4 = 3. So, 3 will be the amount of DR that I will consider for my table.
If it is for the day 03/07, it would be 03/07 – 28/06 = 5 days. This value is subtracted from the amount of DR, that is, 7 – 5 = 2. So 2 will be the amount of DR I'm going to consider for my table
Thanks you so much!
Code below:
library(dplyr)
df <- structure(
list(Id=c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
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","2021-06-28","2021-06-28","2021-06-28",
"2021-06-28","2021-06-28","2021-06-28","2021-06-28","2021-06-28","2021-06-28",
"2021-06-28","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-07-01","2021-07-01","2021-07-01","2021-07-01","2021-04-02",
"2021-04-02","2021-04-02","2021-04-02","2021-04-02","2021-04-02","2021-04-03",
"2021-04-03","2021-04-03","2021-04-03","2021-04-03","2021-04-08","2021-04-08",
"2021-04-09","2021-04-09","2021-04-10","2021-04-10","2021-07-02","2021-07-02",
"2021-07-02","2021-07-03","2021-07-03"),
Week= c("Thursday","Thursday","Thursday","Thursday","Friday","Friday","Friday","Friday",
"Friday","Friday","Saturday","Saturday","Saturday","Saturday","Saturday","Thursday",
"Thursday","Friday","Friday","Friday","Friday","Friday","Friday","Friday","Monday",
"Monday"),
DTPE = c("Ho","Ho","Ho","Ho","","","","","","","","","","","","","","","","Ho","Ho","","","","",""),
D1 = c(8,1,9, 3,5,4,7,6,3,8,2,3,4,6,7,8,4,2,6,2,3,4,3,2,4,8), DR01 = c(4,1,4,3,3,4,3,6,3,7,2,3,4,6,7,8,4,2,6,7,3,3,4,5,6,4),
DR02= c(4,2,6,7,3,2,7,4,2,1,2,3,4,6,7,8,4,2,6,4,3,4,3,2,4,3),DR03 = c(9,5,4,3,3,2,1,5,3,7,2,3,4,7,7,8,4,2,6,4,3,4,3,4,5,4),
DR04 = c(5,4,3,3,6,2,1,9,3,7,2,3,4,7,7,8,4,2,6,4,3,3,4,5,3,4),DR05 = c(5,4,5,3,6,2,1,9,3,7,5,3,4,3,4,5,6,2,6,4,3,3,4,5,3,4),
DR06 = c(2,4,3,3,5,6,7,8,3,7,2,3,4,7,7,8,4,2,6,4,3,3,4,5,3,4),DR07 = c(2,5,4,4,9,4,7,8,3,7,2,3,4,7,7,8,4,2,6,4,3,3,4,5,3,4)),
class = "data.frame", row.names = c(NA, -26L))
df<-df %>%
group_by(date2, Week) %>%
select(D1:DR07) %>%
summarise_all(sum)
df<-data.frame(df)
df
date2 Week D1 DR01 DR02 DR03 DR04 DR05 DR06 DR07
1 2021-04-02 Friday 33 26 19 21 28 28 36 38
2 2021-04-03 Saturday 22 22 22 23 23 19 23 23
3 2021-04-08 Thursday 12 12 12 12 12 11 12 12
4 2021-04-09 Friday 8 8 8 8 8 8 8 8
5 2021-04-10 Friday 5 10 7 7 7 7 7 7
6 2021-07-01 Thursday 21 12 19 21 15 17 12 15
7 2021-07-02 Friday 9 12 9 11 12 12 12 12
8 2021-07-03 Monday 12 10 7 9 7 7 7 7
x<-subset(df, select = DR01:DR07)
x<-cbind(df, setNames(df$D1 - x, paste0(names(x), "_PV")))
PV<-select(x, date2,Week, D1, ends_with("PV"))
PV<-PV %>%
group_by(Week) %>%
summarize(across(ends_with("PV"), median))
PV
# A tibble: 4 x 8
Week DR01_PV DR02_PV DR03_PV DR04_PV DR05_PV DR06_PV DR07_PV
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Friday -1.5 0 -1 -1 -1 -2.5 -2.5
2 Monday 2 5 3 5 5 5 5
3 Saturday 0 0 -1 -1 3 -1 -1
4 Thursday 4.5 1 0 3 2.5 4.5 3