按组在日期之间插入行

Question

我希望能够按组在两个日期之间插入行。我的方法非常复杂，需要通过最后观察值向前推导来插入缺失值，然后再合并。我想知道是否有更简单的方法来实现。

# 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

期望的输出结果

- MLE

Answer 1

通过使用 dplyr 和 tidyr (一行解决方案)。

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

- BENY

Answer 2

你可以尝试这个

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)]

- myincas

---

只是一个建议，OP正在处理 data.frame :) - BENY

Answer 3

我们可以使用tidyverse来完成这个任务。

library(tidyverse)

dt2 <- dt %>%
  group_by(user) %>%
  do(date = seq(from = min(.$date), to = max(.$date), by = 1)) %>%
  unnest() %>%
  left_join(dt, by = c("user", "date")) %>%
  replace_na(list(dummy = 0)) %>%
  select(colnames(dt))

dt2
# A tibble: 9 x 3
    user dummy       date
  <fctr> <dbl>     <date>
1      A     1 2017-01-03
2      A     0 2017-01-04
3      A     0 2017-01-05
4      A     1 2017-01-06
5      B     1 2016-05-01
6      B     0 2016-05-02
7      B     1 2016-05-03
8      B     0 2016-05-04
9      B     1 2016-05-05

- www

Answer 4

我发现用padr库是最简单的方法。

library(padr)
dt_padded <- pad(dt, group = "user", by = "date") %>%
  replace_na(list(dummy=0))

- roarkz

---

fillna to 0 :) - BENY

Answer 5

一个基本的 R（不太优雅）解决方案：

# 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"))
df1 <-data.frame(user,dummy,date)

# Solution
do.call(rbind, lapply(split(df1, df1$user), function(df) {
  dff <- data.frame(user=df$user[1], dummy=0, date=seq.Date(min(df$date), max(df$date), 'day'))
  dff[dff$date %in% df$date, "dummy"] <- df$dummy[1]
  dff
}))


# user dummy date      
# A    1     2017-01-03
# A    0     2017-01-04
# A    0     2017-01-05
# A    1     2017-01-06
# B    1     2016-05-01
# B    0     2016-05-02
# B    1     2016-05-03
# B    0     2016-05-04
# B    1     2016-05-05

- Abdou

Answer 6

假设你的数据叫做df1，而你想在两个日期之间添加日期，请尝试以下操作：

library(dplyr)
df2 <- seq.Date(as.Date("2015-01-03"), as.Date("2015-01-06"), by ="day")
left_join(df2, df1)

如果您只是想添加一条新记录，我建议使用rbind。

rbind()

- Joel Alcedo