Add column to data frame to show the most recent description

user_11987725 0
Viewed 69

I have spent hours today to find a solution to this, there are similar threads out there but not quite what I need.

Dataset:

Year          <- c(2019, 2020, 2021, 2019, 2020, 2020, 2021, 2021)
Term          <- c("2019_T1", "2020_T1", "2021_T1", "2019_T1", "2020_T1", "2020_T2", "2021_T1", "2021_T2")
Code          <- c(1,1,1,2,2,2,2,2)
Description   <- c("Desc1","Desc1","Desc1", "Desc2", "Desc2", "Desc2", "Desc2_NotRecent","Desc2_Recent")

This produces a table as follows:

  Year    Term Code     Description
1 2019 2019_T1    1           Desc1
2 2020 2020_T1    1           Desc1
3 2021 2021_T1    1           Desc1
4 2019 2019_T1    2           Desc2
5 2020 2020_T1    2           Desc2
6 2020 2020_T2    2           Desc2
7 2021 2021_T1    2 Desc2_NotRecent
8 2021 2021_T2    2    Desc2_Recent

Question: How to add a column to show the most recent Description for each Code.

I will need to find the most recent based on the Term. Perhaps this can be accomplished by a simple sort first, apologies I have not figured this out.

Its important its the most recent Term value. Here, the most recent Term is 2021_T2. If the first value is selected, it could be an old description and confuse stakeholders.

Outcome I need:

  Year    Term Code     Description  Most_Recent
1 2019 2019_T1    1           Desc1        Desc1
2 2020 2020_T1    1           Desc1        Desc1
3 2021 2021_T1    1           Desc1        Desc1
4 2019 2019_T1    2           Desc2 Desc2_Recent
5 2020 2020_T1    2           Desc2 Desc2_Recent
6 2020 2020_T2    2           Desc2 Desc2_Recent
7 2021 2021_T1    2 Desc2_NotRecent Desc2_Recent
8 2021 2021_T2    2    Desc2_Recent Desc2_Recent

Really grateful for all of the help. Edited to include simple solution from Robin Gertenbach.

df %>%
  group_by(Code) %>%
  dplyr:: mutate(Most_Recent = dplyr::last(Description, Term))
dplyr r
4 Answers

A shorter solution, no joins or conditionals

df %>%
  group_by(Code) %>%
  mutate(Most_Recent = last(Description, Term))
user_54269090

Update after clarification: See comments:

library(tidyverse)

df %>% 
  group_by(Code) %>% 
  separate(Term, c("Year", "T"), sep = "_", remove = FALSE) %>% 
  mutate(T = parse_number(T),
         Most_recent = ifelse(Year == max(Year) & T == max(T), Description, NA)
         ) %>% 
  fill(Most_recent, .direction = "up")

output:

  Term    Code  Year      T Description     Most_recent 
  <chr>   <chr> <chr> <dbl> <chr>           <chr>       
1 2019_T1 1     2019      1 Desc1           Desc1       
2 2020_T1 1     2020      1 Desc1           Desc1       
3 2021_T1 1     2021      1 Desc1           Desc1       
4 2019_T1 2     2019      1 Desc2           Desc2_Recent
5 2020_T1 2     2020      1 Desc2           Desc2_Recent
6 2020_T2 2     2020      2 Desc2           Desc2_Recent
7 2021_T1 2     2021      1 Desc2_NotRecent Desc2_Recent
8 2021_T2 2     2021      2 Desc2_Recent    Desc2_Recent

First answer: Here is tidyverse way how you could get your result:

library(dplyr)
library(tidyr)
df %>% 
  group_by(Code) %>% 
  mutate(Year = as.numeric(Year),
         Most_recent = ifelse(Year == max(Year), Description, NA)
         ) %>% 
  fill(Most_recent, .direction = "up")

output:

   Year Term    Code  Description  Most_recent 
  <dbl> <chr>   <chr> <chr>        <chr>       
1  2019 2019_T1 1     Desc1        Desc1       
2  2020 2020_T1 1     Desc1        Desc1       
3  2021 2021_T1 1     Desc1        Desc1       
4  2019 2019_T1 2     Desc2        Desc2_Recent
5  2020 2020_T1 2     Desc2        Desc2_Recent
6  2020 2020_T2 2     Desc2        Desc2_Recent
7  2021 2021_T2 2     Desc2_Recent Desc2_Recent
user_133216470 
library(tidyverse)

Year <- c(2019, 2020, 2021, 2019, 2020, 2020, 2021)
Term <- c("2019_T1", "2020_T1", "2021_T1", "2019_T1", "2020_T1", "2020_T2", "2021_T2")
Code <- c(1, 1, 1, 2, 2, 2, 2)
Description <- c("Desc1", "Desc1", "Desc1", "Desc2", "Desc2", "Desc2", "Desc2_Recent")

df <- data.frame(cbind(Year, Term, Code, Description))
df <- df %>%
  mutate(Year = Year %>% as.integer())

most_recent_descriptions <-
  df %>%
  group_by(Code) %>%
  arrange(-Year) %>%
  slice(1) %>%
  transmute(
    Code,
    Most_Recent = Description
  )

df %>%
  left_join(most_recent_descriptions)
#> Joining, by = "Code"
#>   Year    Term Code  Description  Most_Recent
#> 1 2019 2019_T1    1        Desc1        Desc1
#> 2 2020 2020_T1    1        Desc1        Desc1
#> 3 2021 2021_T1    1        Desc1        Desc1
#> 4 2019 2019_T1    2        Desc2 Desc2_Recent
#> 5 2020 2020_T1    2        Desc2 Desc2_Recent
#> 6 2020 2020_T2    2        Desc2 Desc2_Recent
#> 7 2021 2021_T2    2 Desc2_Recent Desc2_Recent

Created on 2021-09-13 by the reprex package (v2.0.1)

user_168531140

Split the Term value in two columns as 'Year' and 'Term', arrange the data by Year and Term and for each Code get the last value.

library(dplyr)
library(tidyr)

df %>%
  separate(Term, c('Year', 'Term'), sep = '_', convert = TRUE) %>%
  arrange(Code, Year, order(gtools::mixedorder(Term))) %>%
  group_by(Code) %>%
  mutate(Most_Recent = last(Description)) %>%
  ungroup

#   Code  Year Term  Description     Most_Recent 
#  <dbl> <int> <chr> <chr>           <chr>       
#1     1  2019 T1    Desc1           Desc1       
#2     1  2020 T1    Desc1           Desc1       
#3     1  2021 T1    Desc1           Desc1       
#4     2  2019 T1    Desc2           Desc2_Recent
#5     2  2020 T1    Desc2           Desc2_Recent
#6     2  2020 T2    Desc2           Desc2_Recent
#7     2  2021 T1    Desc2_NotRecent Desc2_Recent
#8     2  2021 T2    Desc2_Recent    Desc2_Recent

If you want the Year and Term values in one column again you may add unite to do the above chain.

...ungroup %>% unite(Term, Year, Term)
user_39629140
Related