How to create start and end column using days column?-CodePudding

I have a dataset as follows:

mydata <- read.table(header=TRUE, text="
  Id   DAYS   QS   Event 
  01    70    1      1
  01    78    2      1
  01    85    3      1
  02    92    4      1
  02    98    5      1
  02   105    6      1
  02   106    7      0
")

I would like to use the DAYS column to create a start and end column like this:

mydata <- read.table(header=TRUE, text="
  Id   start  end    QS   Event 
  01    0      70     1      1
  01    70     78     2      1
  01    78     85     3      1
  02    0      92     4      1
  02    92     98     5      1
  02    98     105    6      1
  02   105     106    7      0
")

How can I do that?

CodePudding user response：

You could use

library(dplyr)

mydata %>%
  group_by(Id) %>% 
  mutate(start = lag(DAYS, default = 0)) %>% 
  select(Id, start, end = DAYS, QS, Event) %>% 
  ungroup()

This returns

# A tibble: 7 x 5
     Id start   end    QS Event
  <int> <dbl> <int> <int> <int>
1     1     0    70     1     1
2     1    70    78     2     1
3     1    78    85     3     1
4     2     0    92     4     1
5     2    92    98     5     1
6     2    98   105     6     1
7     2   105   106     7     0

CodePudding user response：

Here is a base R way with ave.

mydata$start <- with(mydata, ave(DAYS, Id, FUN = \(x) c(0, head(x, -1))))
mydata <- mydata[c(1, 5, 2:4)]

Then, change the name of column DAYS.

names(mydata)[3] <- "end"
mydata
#  Id start end QS Event
#1  1     0  70  1     1
#2  1    70  78  2     1
#3  1    78  85  3     1
#4  2     0  92  4     1
#5  2    92  98  5     1
#6  2    98 105  6     1
#7  2   105 106  7     0