I have a dataframe having country_name, date and several columns: column_1, column_2and column_3. I am trying to extract the latest record based on date across several columns.
The dataframe looks like this:
| country_name | date | column_1| column_2| column_3|
| US | 2016-11-02 | 7.5 | NA | NA |
| US | 2017-09-12 | NA | NA | 9 |
| US | 2017-09-19 | NA | 8 | 10 |
| US | 2020-02-10 | 10 | NA | NA |
| US | 2021-03-10 | NA | NA | 7.3 |
| US | 2021-05-02 | NA | 3 | NA |
| UK | 2016-11-02 | NA | 2 | NA |
| UK | 2017-09-12 | 0.5 | 3 | NA |
.
.
For the US the desired output is:
| country_name | column_1| column_2| column_3|
| US | 10 | 3 | 7.3 |
For column_1, the value with the latest date is 10 (date: 2020-02-10),
for column_2 is 3 (date: 2021-05-02), and for column_3 is 7.3 (date: 2021-03-10). My goal is to apply this logic across several countries. How do I achieve this?
library(dplyr)
library(tidyr)
df1 %>%
mutate(date = as.Date(date)) %>%
group_by(country_name) %>%
arrange(date) %>%
select(-date) %>%
fill(everything()) %>%
slice(n())
#> # A tibble: 2 x 4
#> # Groups: country_name [2]
#> country_name column_1 column_2 column_3
#> <chr> <dbl> <int> <dbl>
#> 1 UK 0.5 3 NA
#> 2 US 10 3 7.3
read.table(text = "country_name date column_1 column_2 column_3
US 2016-11-02 7.5 NA NA
US 2017-09-12 NA NA 9
US 2017-09-19 NA 8 10
US 2020-02-10 10 NA NA
US 2021-03-10 NA NA 7.3
US 2021-05-02 NA 3 NA
UK 2016-11-02 NA 2 NA
UK 2017-09-12 0.5 3 NA",
header = T, stringsAsFactors = F) -> df1