CODEWITHSUNDEEP
rdate
syhwyqp
syhwyqp

Reputation: 77

Imputing dates to empty cells for large dataset

I have a dataset that looks like below:

PPID      join_date      week      date         visit
A         2017-10-01     1         NA           0
A         2017-10-01     2         2017-10-08   2
A         2017-10-01     3         2017-10-15   1
A         2017-10-01     4         NA           0
B         2017-05-23     1         2017-05-21   4
B         2017-05-23     2         2017-05-28   2
B         2017-05-23     3         NA           0

week indicates the difference between the Sunday of the week of join_date and date in weeks (e.g. for participant B, the Sunday of the week of 2017-05-23 is 2017-05-21; thus participant B's week1 starts on 2017-05-21, and week2 starts on 2017-05-28).

My goal is to fill in date where it is currently NA, such that the output looks like below: 

PPID      join_date      week      date         visit
A         2017-10-01     1         2017-10-01   0
A         2017-10-01     2         2017-10-08   2
A         2017-10-01     3         2017-10-15   1
A         2017-10-01     4         2017-10-22   0
B         2017-05-23     1         2017-05-21   4
B         2017-05-23     2         2017-05-28   2
B         2017-05-23     3         2017-06-04   0

The code I currently have is:

library(dplyr)
library(lubridate)
df2 <- df %>% 
 group_by(PPID) %>% 
 mutate(date = seq(unique(floor_date(as.Date(join_date), "weeks")), 
           unique(floor_date(as.Date(join_date), "weeks") + 7*(max(week)-1)), 
           by="week"))

The problem with this approach is that I'm working with large dataset (~8 mil observation) and it takes forever to run! I read some posts that all those date conversion/calculation (e.g. floor_date or as.Date) is what takes so long, and was wondering if there's ways to make my code more efficient.

Thanks!

Upvotes: 0

Views: 31

Answers (1)

dww
dww

Reputation: 31452

How about simply 

df2$date = floor_date(df2$join_date, 'week') + 7*(df2$week-1)
#   PPID  join_date week       date visit
# 1    A 2017-10-01    1 2017-10-01     0
# 2    A 2017-10-01    2 2017-10-08     2
# 3    A 2017-10-01    3 2017-10-15     1
# 4    A 2017-10-01    4 2017-10-22     0
# 5    B 2017-05-23    1 2017-05-21     4
# 6    B 2017-05-23    2 2017-05-28     2
# 7    B 2017-05-23    3 2017-06-04     0

Although this calculates floor_date for every row, it is vectorised rather looping (as you did implicitly using by), so should be fast enough for most purposes. If you need even more speed-up, you could subset on is.na(df2$data) to only calculate the rows you need to impute.

Data:

df2 = structure(list(PPID = c("A", "A", "A", "A", "B", "B", "B"), join_date = structure(c(17440, 
  17440, 17440, 17440, 17309, 17309, 17309), class = "Date"), week = c(1L, 
    2L, 3L, 4L, 1L, 2L, 3L), date = structure(c(NA, 17447, 17454, 
      NA, 17307, 17314, NA), class = "Date"), visit = c(0L, 2L, 1L, 
        0L, 4L, 2L, 0L)), row.names = c(NA, -7L), class = "data.frame")

Upvotes: 1

Related Questions