All geek questions in one place

Adding missing date values ​​in a data frame with multiple watch periods

Thanks in advance.

I am trying to add missing date values ​​that were not included in the observation period for three different individuals.

My data is as follows:

 IndID       Date Event Number Percent
1   P01 2011-03-04     1      2   0.390
2   P01 2011-03-11     1      2   0.975
3   P01 2011-03-13     0      9   0.795
4   P01 2011-03-14     0     10   0.516
5   P01 2011-03-15     0      1   0.117
6   P01 2011-03-17     0      7   0.093

IndID- a unique identifier ( P01, P03, P06). Date- This is obviously a date. Event- A binary variable indicating whether an event has occurred ( 0= no and 1= yes).
Columns Numberand Percenthave no direct value, but they need to save and thus to include here.

My sample data frame ( PostData) is given below using dput.

IndID Date - , . - 0 Event. (Number Percent) .

, - .

min(PostData$Date) max(PostData$Date). , merge for. , .

.

PostData <-structure(list(IndID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
  3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 
  5L, 5L), .Label = c("P01", "P02", "P03", "P05", "P06", "P07", 
  "P08", "P09", "P10", "P11", "P12", "P13"), class = "factor"), 
  Date = structure(c(1299196800, 1299801600, 1299974400, 1300060800, 
  1300147200, 1300320000, 1300406400, 1310083200, 1310169600, 
  1310515200, 1310774400, 1310947200, 1311033600, 1311292800, 
  1311552000, 1323129600, 1323388800, 1323648000, 1323993600, 
  1324080000, 1324166400, 1324339200, 1327622400, 1327795200, 
  1327881600), class = c("POSIXct", "POSIXt"), tzone = "GMT"), 
  Event = c(1L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 0L, 1L, 
  0L, 0L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 0L, 0L), Number = c(2L, 
  2L, 9L, 10L, 1L, 7L, 5L, 9L, 1L, 4L, 5L, 2L, 0L, 1L, 10L, 
  5L, 0L, 6L, 5L, 10L, 9L, 4L, 4L, 8L, 1L), Percent = c(0.39, 
  0.975, 0.795, 0.516, 0.117, 0.093, 0.528, 0.659, 0.308, 0.055, 
  0.185, 0.761, 0.132, 0.676, 0.368, 0.383, 0.272, 0.113, 0.974, 
  0.696, 0.941, 0.751, 0.758, 0.29, 0.15)), .Names = c("IndID", 
  "Date", "Event", "Number", "Percent"), row.names = c(NA, 25L), 
  class = "data.frame")
+4
4

R:

do.call(rbind,
  by(
    PostData,
    PostData$IndID,
    function(x) {
      out <- merge(
        data.frame(
          IndID=x$IndID[1],
          Date=seq.POSIXt(min(x$Date),max(x$Date),by="1 day")
        ),
        x,
        all.x=TRUE
      )
      out$Event[is.na(out$Event)] <- 0
      out
    }  
  )
)

:

       IndID       Date Event Number Percent
P01.1    P01 2011-03-04     1      2   0.390
P01.2    P01 2011-03-05     0     NA      NA
P01.3    P01 2011-03-06     0     NA      NA
P01.4    P01 2011-03-07     0     NA      NA
P01.5    P01 2011-03-08     0     NA      NA
P01.6    P01 2011-03-09     0     NA      NA
P01.7    P01 2011-03-10     0     NA      NA
P01.8    P01 2011-03-11     1      2   0.975
<<etc>>
+5

. 0

library(data.table)
library(plyr)
dtPostData = data.table(PostData)
minmaxTab = dtPostData[,list(minDate=min(Date),maxDate=max(Date)),by=IndID]

df = lapply(1:nrow(minmaxTab),function(x) {
  temp = seq(minmaxTab$minDate[x],minmaxTab$maxDate[x],by=24*60*60) 
  temp = temp[!(temp %in% dtPostData[IndID == minmaxTab$IndID[x],]$Date)]
  data.table(IndID = minmaxTab$IndID[x], Date = temp, Event = 0, Number = 0, Percent = 0)
})

df <- ldply(x, data.frame)
df

#Results
   IndID       Date Event Number Percent
1    P01 2011-03-05     0      0       0
2    P01 2011-03-06     0      0       0
3    P01 2011-03-07     0      0       0
4    P01 2011-03-08     0      0       0
5    P01 2011-03-09     0      0       0
6    P01 2011-03-10     0      0       0
7    P01 2011-03-12     0      0       0
8    P01 2011-03-16     0      0       0
9    P03 2011-07-10     0      0       0
+3

dplyr. , , data.frame 89 , , .

require(dplyr)

PostData %>%
  mutate(Date = as.Date(as.character(Date))) %>%
  group_by(IndID) %>%
  do(left_join(data.frame(IndID = .$IndID[1], Date = seq(min(.$Date), max(.$Date), 1)), ., 
                       by=c("IndID", "Date"))) %>%
  mutate(Event = ifelse(is.na(Event), 0, Event))

#   IndID       Date Event Number Percent
#1    P01 2011-03-04     1      2   0.390
#2    P01 2011-03-05     0     NA      NA
#3    P01 2011-03-06     0     NA      NA
#4    P01 2011-03-07     0     NA      NA
#5    P01 2011-03-08     0     NA      NA
#6    P01 2011-03-09     0     NA      NA 
#7    P01 2011-03-10     0     NA      NA
#8    P01 2011-03-11     1      2   0.975
#...
#84   P06 2012-01-25     0     NA      NA
#85   P06 2012-01-26     0     NA      NA
#86   P06 2012-01-27     1      4   0.758
#87   P06 2012-01-28     0     NA      NA
#88   P06 2012-01-29     0      8   0.290
#89   P06 2012-01-30     0      1   0.150
+2

( ):

min_time = as.integer(min(PostData$Date))
max_time = as.integer(max(PostData$Date))

:

list_of_dates = seq(min_time,max_time, 86400) #since there are 86400 seconds in a day
list_of_dates = as.Date(as.POSIXct( list_of_dates ), origin = '1970-01-01 00:00.00 UTC') 
#convert back to a date

INDID Date

temp = merge(unique(PostData$IndID),list_of_dates)
names(temp) = c("IndID","Date")
data_missing_indID_date = temp[!which(temp$IndID %in% PostData$IndID & temp$Date %in% PostData$Date ),]

:

data_missing_indID_date$Event = 0 
data_missing_indID_date$Number = NA
data_missing_indID_date$Percent = NA

rbind :

final_data = rbind(PostData, data_missing_indID_date)
0

Source: https://habr.com/ru/post/1544092/

More articles:


All Articles