每个公司每年每个月的最后一次观察

Question

我有以下数据

id <- c(A,A,B,B)
date <- as.Date(c("21/10/2011","22/10/2011","23/10/2011","24/10/2011"), format = "%d/%m/%Y"
price <- c(1,2,3,4)
df <- as.data.frame(id,date,price)

我想要每个ID每年每个月的价格最后一次记录观察。

所以在这种情况下，我希望它看起来如下

ID  date        price
A   22/10/2011  2
B   24/10/2011  4

注意，我想要每年每个月的最后一次观察：(

有人能帮我吗？

Answer 1

library(data.table)
setDT(df)
df[, head(.SD[order(-date)], 1), by = .(id, year(date), month(date))]
#    id year month price
# 1:  A 2011    10     2
# 2:  B 2011    10     4
#or
df[df[order(date), .I[.N], by = .(id, year(date), month(date))]$V1]
#    id       date price
# 1:  A 2011-10-22     2
# 2:  B 2011-10-24     4

Answer 2

使用tidyverse方法：

library(tidyverse)
library(lubridate)
id <- c("A","A","B","B")
date <- c("21/10/2011","22/10/2011","23/10/2011","24/10/2011")
price <- c(1,2,3,4)
df <- data.frame(id,date,price)
df %>% 
mutate(month = month(dmy(date)), year = year(dmy(date))) %>% 
group_by(id,month, year) %>% 
summarise(date=last(date), last_price = last(price), .groups = "drop") %>%
select(-month, -year)
#> # A tibble: 2 × 3
#>   id    date       last_price
#>   <chr> <chr>           <dbl>
#> 1 A     22/10/2011          2
#> 2 B     24/10/2011          4