根据R中的特定变量选择不同的条目

Question

我希望根据两个特定的变量为我的数据集选择不同的条目。事实上，我可能喜欢创建一个子集并使用每个子集进行分析。

数据集如下所示

id <- c(3,3,6,6,4,4,3,3)
date <- c("2017-1-1", "2017-3-3", "2017-4-3", "2017-4-7", "2017-10-1", "2017-11-1", "2018-3-1", "2018-4-3")
date_cat <- c(1,1,1,1,2,2,3,3)
measurement <- c(10, 13, 14,13, 12, 11, 14, 17)
myData <- data.frame(id, date, date_cat, measurement)
myData

myData$date1 <- as.Date(myData$date)
myData

  id      date date_cat measurement      date1
1  3  2017-1-1        1          10 2017-01-01
2  3  2017-3-3        1          13 2017-03-03
3  6  2017-4-3        1          14 2017-04-03
4  6  2017-4-7        1          13 2017-04-07
5  4 2017-10-1        2          12 2017-10-01
6  4 2017-11-1        2          11 2017-11-01
7  3  2018-3-1        3          14 2018-03-01
8  3  2018-4-3        3          17 2018-04-03

#select the last date for the ID in each date category.
Here date_cat is the date category and date1 is date formatted as date. How can I get the last date for each ID in each date_category?

I want my data to show up as

  id      date date_cat measurement      date1
1  3  2017-3-3        1          13 2017-03-03
2  6  2017-4-7        1          13 2017-04-07
3  4 2017-11-1        2          11 2017-11-01
4  3  2018-4-3        3          17 2018-04-03

Thanks!

Answer 1

我不确定你是否想要下面这样的东西

subset(myData,ave(date1,id,date_cat,FUN = function(x) tail(sort(x),1))==date1)

这给

> subset(myData,ave(date1,id,date_cat,FUN = function(x) tail(sort(x),1))==date1)
  id      date date_cat measurement      date1
2  3  2017-3-3        1          13 2017-03-03
4  6  2017-4-7        1          13 2017-04-07
6  4 2017-11-1        2          11 2017-11-01
8  3  2018-4-3        3          17 2018-04-03

Answer 2

我们可以在'id‘列上创建一个带有rleid的组，slice最后一行，删除临时分组列

library(dplyr)
library(data.table)
myData %>%
    group_by(grp = rleid(id)) %>% 
    slice(n()) %>%
    ungroup %>%
    select(-grp)
# A tibble: 4 x 5
#     id date      date_cat measurement date1     
#  <dbl> <chr>        <dbl>       <dbl> <date>    
#1     3 2017-3-3         1          13 2017-03-03
#2     6 2017-4-7         1          13 2017-04-07
#3     4 2017-11-1        2          11 2017-11-01
#4     3 2018-4-3         3          17 2018-04-03

或者，可以在不创建临时列的情况下动态执行此操作。

myData %>% 
    filter(!duplicated(rleid(id), fromLast = TRUE))

或将base R与subset和rle结合使用

subset(myData, !duplicated(with(rle(id), 
    rep(seq_along(values), lengths)), fromLast = TRUE))
# id      date date_cat measurement      date1
#2  3  2017-3-3        1          13 2017-03-03
#4  6  2017-4-7        1          13 2017-04-07
#6  4 2017-11-1        2          11 2017-11-01
#8  3  2018-4-3        3          17 2018-04-03

Answer 3

使用data.table：

library(data.table)
myData_DT <- as.data.table(myData)
myData_DT[, .SD[.N] , by = .(date_cat, id)]