Filtering by year equal or smaller than threshold

user_13499413 0
Viewed 46

I have country data for multiple years and want to filter for the last observation of each country where the year is equal or smaller than year X. Thus, I want to end up with unique row for each country:

library(tidyverse)

df <- tibble("country" = c(rep("AFG", 3),  rep("BEN", 3), rep("CHE", 3)),
             "year" = c(2001, 2005, 2009, 2001, 2004, 2009, 2000, 2003, 2008), 
             "value" = rnorm(9, 50))

df %>% 
  filter(year <= 2008)
#> # A tibble: 7 x 3
#>   country  year value
#>   <chr>   <dbl> <dbl>
#> 1 AFG      2001  51.0
#> 2 AFG      2005  49.9
#> 3 BEN      2001  50.2
#> 4 BEN      2004  49.2
#> 5 CHE      2000  50.8
#> 6 CHE      2003  49.0
#> 7 CHE      2008  48.3

Created on 2021-02-10 by the reprex package (v0.3.0)

Whereas my desired outcome would be:

#>   country year  value
#>   <chr>   <chr> <dbl>
#> 1 AFG     2005   50.8
#> 2 BEN     2004   49.5
#> 3 CHE     2008   49.9

how would one achieve this (preferably using a vectorized solution instead of a for-loop)?

dplyr r
2 Answers

Keep only the rows for the year 2008 or less, arrange the data by year and for each country select the last row.

library(dplyr)

df %>%
  filter(year <= 2008) %>%
  arrange(country, year) %>%
  group_by(country) %>%
  slice(n())

#  country  year value
#  <chr>   <dbl> <dbl>
#1 AFG      2005  48.5
#2 BEN      2004  49.1
#3 CHE      2008  49.3
user_39629140

We can use

 library(dplyr)
 df %>%
        filter(year <= 2008) %>%
        arrange(country, year) %>%
        group_by(country) %>%
        filter(row_number() == n())
user_37322710
Related