Last observation for every month for every year, for each firm-CodePudding

I have the following data

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)

I want last recorded observation of price for every month for every year for each ID.

so in this case I want it to look as follows

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

Note that i want the last observation for every month for every year:)

Can someone help me?

CodePudding user response：

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

CodePudding user response：

Using a tidyverse approach:

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