Create a new column that is populated by the previous row of a different column in R

user_17083131 0
Viewed 30

My data are as follows:

site setup.date  revisit.date end.date
A    2019-07-15  2019-07-22   2019-07-22
A    2019-07-15  2019-07-29   2019-07-29
A    2019-07-15  2019-08-04   2019-08-01
A    2019-07-15  2019-08-18   2019-08-18
B    2019-07-22  2019-07-23   2019-07-23
B    2019-07-22  2019-07-30   2019-07-25
B    2019-07-22  2019-08-05   2019-08-05
B    2019-07-22  2019-08-19   2019-08-19

I would like to create a new column, "begin.date" that is populated by the previous row in "revisit.date" for each "site". If it is the first row in the "site", assign "begin.date" as "setup.date" for that "site". The first two rows of my desired output would be as follows:

site setup.date  revisit.date begin.date  end.date    
A    2019-07-15  2019-07-22   2019-07-15  2019-07-22   
A    2019-07-15  2019-07-29   2019-07-22  2019-07-29

Thank you in advance!

r
1 Answers

You may try

library(dplyr)
df %>%
  group_by(site) %>%
  mutate(begin.date = lag(revisit.date, default = first(setup.date)), .after = revisit.date)  #Thanks to @onyambu

  site  setup.date revisit.date begin.date end.date  
  <chr> <chr>      <chr>        <chr>      <chr>     
1 A     2019-07-15 2019-07-22   2019-07-15 2019-07-22
2 A     2019-07-15 2019-07-29   2019-07-22 2019-07-29
3 A     2019-07-15 2019-08-04   2019-07-29 2019-08-01
4 A     2019-07-15 2019-08-18   2019-08-04 2019-08-18
5 B     2019-07-22 2019-07-23   2019-07-22 2019-07-23
6 B     2019-07-22 2019-07-30   2019-07-23 2019-07-25
7 B     2019-07-22 2019-08-05   2019-07-30 2019-08-05
8 B     2019-07-22 2019-08-19   2019-08-05 2019-08-19
user_167291750
Related