# Calculation relating two variables in R

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
``````

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.