Complete sequence column and fill in rows

user_9249427 0
Viewed 1329

I'm starting with a dataset that has a numeric column (time in my case, but in numeric format), a certain event for the timestamp, and an ID. I'm hoping to run some code that expands the data frame to fill in the sequence of numbers while duplicating the event column (and any other associated columns). I'd like to run this by ID so I'm not also filling in the gaps between IDs.

Here's a simplified example of the data I'm starting with. I'd like to fill the sequence of the "value" column for each ID separately, completing each new row with the data from the first row for each "event".

 a<-c("162", "164", "169", "171", "174", "188", "191", "198","200")
b<-c("start1","end1","start2", "event", "end2", "start1", "end1", "start2", "end2")
c<-c("A","A","A", "A", "A", "B", "B", "B", "B")

original<-data.table(value = a, event = b, ID = c)

And this is a reconstruction of my end goal:

agoal<-c(seq(from = 162, to = 174), seq(from = 188, to = 200))
bgoal<-c("start1","start1","end1","end1", "end1", "end1", "end1",
     "start2", "start2",  "event", "end2","end2", "end2", 
     "start1", "start1", "start1", "end1", "end1", "end1", "end1", "end1", "end1", "end1",
     "start2", "start2","end2")
cgoal<-c(rep("A",13), rep("B",13))

goal<-data.table(value = agoal, event = bgoal, ID = cgoal)

Sorry if this isn't very clear!

dplyr dataframe sequence r time
3 Answers

We could use complete and fill from tidyr package: First use tpye.convert(as.is=TRUE) to assign numeric to value

library(dplyr)
library(tidyr)

original %>% 
  type.convert(as.is=TRUE) %>% 
  group_by(ID) %>% 
  complete(value = first(value):max(value)) %>% 
  fill(event)

Output:

    value  event ID
 1:   162 start1  A
 2:   163 start1  A
 3:   164   end1  A
 4:   165   end1  A
 5:   166   end1  A
 6:   167   end1  A
 7:   168   end1  A
 8:   169 start2  A
 9:   170 start2  A
10:   171  event  A
11:   172   end2  A
12:   173   end2  A
13:   174   end2  A
14:   188 start1  B
15:   189 start1  B
16:   190 start1  B
17:   191   end1  B
18:   192   end1  B
19:   193   end1  B
20:   194   end1  B
21:   195   end1  B
22:   196   end1  B
23:   197   end1  B
24:   198 start2  B
25:   199 start2  B
26:   200   end2  B
user_133216470

There's two parts to deal with: a) how to add rows for the missing numbers in the value column and b) how to fill event with whatever came before it. (b) is easy (just use fill). (a) can be done by making a new dataframe with all the values you want and joining with the original:

library(tidyverse)

original %>%
  group_by(ID) %>%
  summarize(a = as.integer(min(value)),
            b = as.integer(max(value))) %>%
  transpose() %>%
  map(~ data.frame(ID = .x$ID, value = .x$a:.x$b)) %>%
  reduce(bind_rows) %>%
  left_join(original, by = c("value", "ID")) %>%
  group_by(ID) %>%
  fill(event, .direction = "down")

Step by step:

  1. get the min and max for the value column within each group
  2. split the dataframe into a list using transpose (this will give a nested list where the first level is indexed by row number and second level is indexed by column name)
  3. use map to make a list of dataframes for each ID with a value column having all values between the min and max of the original
  4. mash all the dataframes together using reduce and bind_rows
  5. join with original using value and ID; note that left_join will ensure all new value and ID combinations are present, even if they don't have any corresponding combination in the original (this will result in any missing event values having NA, which is what you want for the next step)
  6. fill all the NAs downward with whatever the previous event in that group was

Result:

   ID value  event
1   A   162 start1
2   A   163 start1
3   A   164   end1
4   A   165   end1
5   A   166   end1
6   A   167   end1
7   A   168   end1
8   A   169 start2
9   A   170 start2
10  A   171  event
11  A   172  event
12  A   173  event
13  A   174   end2
14  B   188 start1
15  B   189 start1
16  B   190 start1
17  B   191   end1
18  B   192   end1
19  B   193   end1
20  B   194   end1
21  B   195   end1
22  B   196   end1
23  B   197   end1
24  B   198 start2
25  B   199 start2
26  B   200   end2

Note that you don't actually need the two as.integer calls if you have the numeric values as actual numbers in original. Also, I think in the OP you meant to have event three times in a row instead of one (otherwise the pattern is broken).

user_147756280

Another option using dplyr and tidyr:

library(dplyr)
library(tidyr)

original %>% 
  split(.$ID) %>% 
  lapply(function(x) data.frame(value = as.character(seq(min(x$value), max(x$value)))) %>% 
           left_join(x, by="value") %>% 
           fill(c("event", "ID"))) %>% 
  do.call(rbind.data.frame, .)

returns

     value  event ID
A.1    162 start1  A
A.2    163 start1  A
A.3    164   end1  A
A.4    165   end1  A
A.5    166   end1  A
A.6    167   end1  A
A.7    168   end1  A
A.8    169 start2  A
A.9    170 start2  A
A.10   171  event  A
A.11   172  event  A
A.12   173  event  A
A.13   174   end2  A
B.1    188 start1  B
B.2    189 start1  B
B.3    190 start1  B
B.4    191   end1  B
B.5    192   end1  B
B.6    193   end1  B
B.7    194   end1  B
B.8    195   end1  B
B.9    196   end1  B
B.10   197   end1  B
B.11   198 start2  B
B.12   199 start2  B
B.13   200   end2  B

More or less the same but without the split and do.call-part:

original %>% 
  group_by(ID) %>% 
  group_map(function(x, ...) data.frame(value = as.character(seq(min(x$value), max(x$value)))) %>% 
           left_join(original, by="value") %>% 
           fill(c("event", "ID"))) %>% 
  bind_rows()
user_125052510
Related