Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

insert rows between dates by group

Tags:

date

dataframe

r

I want to insert rows between two dates by group. My way of doing it is so complicated that I insert missing values by last observation carry forwards and then merge. I was wondering is there any easier way to achieve it.

# sample data 
user<-c("A","A","B","B","B") 
dummy<-c(1,1,1,1,1)
date<-as.Date(c("2017/1/3","2017/1/6","2016/5/1","2016/5/3","2016/5/5"))
dt<-data.frame(user,dummy,date) 

  user dummy       date
1    A     1 2017-01-03
2    A     1 2017-01-06
3    B     1 2016-05-01
4    B     1 2016-05-03
5    B     1 2016-05-05

Desired output

enter image description here

like image 204
News_is_Selection_Bias Avatar asked Sep 01 '17 01:09

News_is_Selection_Bias


2 Answers

By using dplyr and tidyr :)(one line solution )

library(dplyr)
library(tidyr)
dt %>% group_by(user) %>% complete(date=full_seq(date,1),fill=list(dummy=0))
# A tibble: 9 x 3
# Groups:   user [2]
    user       date dummy
  <fctr>     <date> <dbl>
1      A 2017-01-03     1
2      A 2017-01-04     0
3      A 2017-01-05     0
4      A 2017-01-06     1
5      B 2016-05-01     1
6      B 2016-05-02     0
7      B 2016-05-03     1
8      B 2016-05-04     0
9      B 2016-05-05     1
like image 95
BENY Avatar answered Oct 06 '22 00:10

BENY


you can try this

library(data.table)
setDT(dt)
tmp <- dt[, .(date = seq.Date(min(date), max(date), by = '1 day')), by = 
'user']
dt <- merge(tmp, dt, by = c('user', 'date'), all.x = TRUE)
dt[, dummy := ifelse(is.na(dummy), 0, dummy)]
like image 39
myincas Avatar answered Oct 05 '22 23:10

myincas