How to create a "relative time" variable from date?

Question

I have the following dataframe:

df <- structure(list(country = c("US", "US", "US", "UK", "UK", "UK", 
"UK"), date = c("2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04", 
"2020-01-05", "2020-01-06", "2020-01-07"), y = 1:7, treatment = c(0, 
1, 0, 0, 0, 1, 0)), class = "data.frame", row.names = c(NA, -7L
))

Here is the df:

  country date       y treatment
  US      2020-01-01 1    0
  US      2020-01-02 2    1
  US      2020-01-03 3    0
  UK      2020-01-01 4    0
  UK      2020-01-02 5    0
  UK      2020-01-03 6    1
  UK      2020-01-04 7    0

I need to create varible which will reflect relative time before and after treatment with zero for the date of treatment. So, in this case it should be equal to

relative_time = c(-1,0,1,-2,-1,0,1)

How can I create such a varible for each group of country?

The code for df is not the same as the data frame displayed. Please fix the question. — G. Grothendieck
– G. Grothendieck, Commented Jul 2, 2020 at 15:07
Exactly. If the country is missing, this is not possible. You need the country group. — Georgery
– Georgery, Commented Jul 2, 2020 at 15:14
Sorry, took the freedom to edit the post and make the reproducible example right. — Georgery
– Georgery, Commented Jul 2, 2020 at 15:20
One addition. This example implicitly assumes that there is only one treatment per country. Readers might wanna know that when adapting from this example. — Georgery
– Georgery, Commented Jul 2, 2020 at 15:29

Georgery · Accepted Answer · 2020-07-02 15:24:30Z

0

Also dplyr, bit shorter

library(lubridate)

df %>%
    mutate(date = ymd(date)) %>%
    group_by(country) %>%
    mutate(time_to_treatment = date - date[treatment == 1])

outputs

  country date           y treatment time_to_treatment
  <chr>   <date>     <int>     <dbl> <drtn>           
1 US      2020-01-01     1         0 -1 days          
2 US      2020-01-02     2         1  0 days          
3 US      2020-01-03     3         0  1 days          
4 UK      2020-01-04     4         0 -2 days          
5 UK      2020-01-05     5         0 -1 days          
6 UK      2020-01-06     6         1  0 days          
7 UK      2020-01-07     7         0  1 days

edited Jul 2, 2020 at 15:24

answered Jul 2, 2020 at 15:17

Georgery

8,1871 gold badge22 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mouad_Seridi Over a year ago

lubridate needs to be loaded in order to use the function ymd

Georgery Over a year ago

true. Will add it.

Mouad_Seridi · Accepted Answer · 2020-07-02 15:15:54Z

-1

The provided data is different from the displayed data, so the displayed data was used.

require(tidyverse)

df = read.table( text = 'country date       y treatment
                US      2020-01-01 1    0
                US      2020-01-02 2    1
                US      2020-01-03 3    0
                UK      2020-01-01 4    0
                UK      2020-01-02 5    0
                UK      2020-01-03 6    1
                UK      2020-01-04 7    0', header  = T) 

df <- 
df %>% group_by(country) %>% 
  mutate(rank_row = row_number()) %>% 
  ungroup %>% 
  mutate(treatment_row_number = if_else(treatment == 1, rank_row, 0L)) %>% 
  group_by(country) %>% 
  mutate(treatment_row_number = max(treatment_row_number)) %>% 
  ungroup %>% 
  mutate(relative_time = rank_row - treatment_row_number) %>% 
  select(-rank_row, -treatment_row_number) 
 
df


# A tibble: 7 x 5
  country date           y treatment relative_time
  <fct>   <fct>      <int>     <int>         <int>
1 US      2020-01-01     1         0            -1
2 US      2020-01-02     2         1             0
3 US      2020-01-03     3         0             1
4 UK      2020-01-01     4         0            -2
5 UK      2020-01-02     5         0            -1
6 UK      2020-01-03     6         1             0
7 UK      2020-01-04     7         0             1

answered Jul 2, 2020 at 15:15

Mouad_Seridi

2,71617 silver badges29 bronze badges

1 Comment

Georgery Over a year ago

Um, this is not reliable the dataframe must be sorted correctly in advance, otherwise you will get something wrong. And if you don't convert the date column into a date you cannot even sort by it. You might wann fix this in your answer.

Collectives™ on Stack Overflow

How to create a "relative time" variable from date?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related