r - 获取最新记录，但如果最新记录为空，则获取最后一条最新记录

Question

我在尝试将行转换为列然后仅获取具有条目的最新记录时遇到问题(使用时间戳)。这是我的数据集:

df <- data.frame(id = c("123||wa", "123||wa", "123||wa", "223||sa", "223||sa", "223||sa", "123||wa"),
             questions = c("dish", "car", "house", "dish", "house", "car", "dish"),
             answers = c(" ", "bmw", "yes", "pizza", "yes", "audi","ravioli" ), 
             timestamp = c("24JUN2018:12:24:16", "07JAN2015:15:22:54", "24MAR2018:12:24:16", "24MAR2018:12:24:16",
                           "04AUG2014:12:40:30", "03JUL2014:15:38:11", "03JUL2014:15:38:11"))

所需的输出是:



到目前为止，我有以下代码可以完成大部分工作。

library(lubridate)
library(dplyr)

df %>%
  mutate(timestamp = ymd_hms(strptime(timestamp, "%d%b%Y:%H:%M:%S"))) %>%
  group_by(id, questions) %>%
  arrange(timestamp) %>%
  summarise(last = last(answers)) %>%
  spread(questions, last)

唯一缺少的是它总是拉出最新的记录，即使它是空的。如何将其更改为仅包含最新的条目记录而不是拉空白。谢谢

上述代码产生的输出为:

Answer 1

另一种使用 slice 的方法.作为奖励，您可以结合 mutate和 arrange脚步:

library(lubridate)
library(dplyr)
library(tidyr)

df %>%
  group_by(id, questions) %>%
  arrange(timestamp = ymd_hms(strptime(timestamp, "%d%b%Y:%H:%M:%S"))) %>%
  slice(which.max(!is.na(answers))) %>%
  select(-timestamp) %>%
  spread(questions, answers)

输出:

# A tibble: 2 x 4
# Groups:   id [2]
  id      car   dish    house
  <fct>   <fct> <fct>   <fct>
1 123||wa bmw   ravioli yes  
2 223||sa audi  pizza   yes