Repeated records in each year etc...

[Slavek]

Hi,
I have this task. My real file contains thousands of DF.URNs. I need to add a variable flagging that the same DF.URN appears in two IntYears with following conditions:
If only in 2018 so (2018>0 and 2019=0) Retention="Old",
If only in 2019 so (2019>0 and 2018=0) Retention="New",
If in 2018 and 2019 so (2018>0 and 2019>0) Retention="Same",
If neither in 2018 and 2019 so (2018=0 and 2019=0) Retention="No info".

I have this dummy file with a few records only:

data.source <- data.frame(stringsAsFactors=FALSE,
                          DF.URN = c("aaa", "aaa", "ccc", "ddd",
                                     "ccc", "eee"),
                          Rec_Score = c(90, 100, 90, 90, 100, 80),
                          IntYear = c(2018, 2019, 2019, 2018, 2019, 2017))

data.source

table(data.source$DF.URN,data.source$IntYear)

As a result I should get
"aaa" is "Same", "ccc" is "New", "ddd" is "Old" and "eee" is "No Info"

Is it easy to do?

[tbradley]

You could do it like this:

library(dplyr)
library(tidyr)

dat <- data.frame(stringsAsFactors=FALSE,
                          DF.URN = c("aaa", "aaa", "ccc", "ddd",
                                     "ccc", "eee"),
                          Rec_Score = c(90, 100, 90, 90, 100, 80),
                          IntYear = c(2018, 2019, 2019, 2018, 2019, 2017))

dat %>% 
  count(IntYear, DF.URN) %>% 
  pivot_wider(names_from = IntYear, values_from = n, values_fill = list(n = 0)) %>%  
  mutate(
    urn_info = case_when(
      `2018` > 0 & `2019` == 0 ~ "Old",
      `2018` == 0 & `2019` > 0 ~ "New",
      `2018` > 0 & `2019` > 0 ~ "Same",
      `2018` == 0 & `2019` == 0 ~ "No Info"
    )
  )
#> # A tibble: 4 x 5
#>   DF.URN `2017` `2018` `2019` urn_info
#>   <chr>   <int>  <int>  <int> <chr>   
#> 1 eee         1      0      0 No Info 
#> 2 aaa         0      1      1 Same    
#> 3 ddd         0      1      0 Old     
#> 4 ccc         0      0      2 New

^{Created on 2020-01-16 by the reprex package (v0.3.0)}

[Slavek]

Absolutely brilliant! Thank you