Finding current active entry from a list of courses

If you don't want to use course_title column you may also do something like this.

• Used igraph to create an individual cluster for each parent-child.
• thereafter used that cluster-id values in our case, for group_by for actual data.
• to perform network analysis of parent-child I also had to remove None from parent and replaced it with child itself whenever a None is found.

Hope the other syntax is pretty clear. Else ask for an explnation

df <- read.table(header = T, text = 'Course_Code    Course_Title    Course_Status   Parent_Course
HLT31802    Title1a Superseded  HLT31807
HLT31807    Title1b Superseded  HLT31812
HLT31812    Title1c Superseded  HLT35015
HLT35015    Title1d Superseded  HLT35021
HLT35021    Title1e Current None
ABC12345    Title2a Superseded  ABC67890
ABC67890    Title2b Current None')

library(tidyverse)
library(igraph)

df %>%
  mutate(Parent_Course = ifelse(Parent_Course == 'None', Course_Code, Parent_Course)) %>%
  select(1,4) %>%
  graph.data.frame() %>%
  components() %>%
  pluck(membership) %>%
  stack() %>%
  right_join(df, by = c('ind' = 'Course_Code')) %>%
  group_by(values) %>%
  mutate(Parent_Course = ind[Course_Status == 'Current'], .keep = 'used') %>%
  rename(Course_Code = ind)

#> # A tibble: 7 x 4
#> # Groups:   values [2]
#>   values Course_Code Course_Status Parent_Course
#>    <dbl> <chr>       <chr>         <chr>        
#> 1      1 HLT31802    Superseded    HLT35021     
#> 2      1 HLT31807    Superseded    HLT35021     
#> 3      1 HLT31812    Superseded    HLT35021     
#> 4      1 HLT35015    Superseded    HLT35021     
#> 5      1 HLT35021    Current       HLT35021     
#> 6      2 ABC12345    Superseded    ABC67890     
#> 7      2 ABC67890    Current       ABC67890

^{Created on 2021-06-30 by the reprex package (v2.0.0)}

Another igraph option

df %>%
  mutate(Parent_Course = ifelse(Parent_Course == "None", Course_Code, Parent_Course)) %>%
  select(Course_Code, Parent_Course, everything()) %>%
  group_by(Grp = membership(components(graph_from_data_frame(.)))[Course_Code]) %>%
  mutate(Parent_Course = Parent_Course[Course_Status == "Current"]) %>%
  ungroup()

gives

  Course_Code Parent_Course Course_Title Course_Status   Grp
  <chr>       <chr>         <chr>        <chr>         <dbl>
1 HLT31802    HLT35021      Title1a      Superseded        1
2 HLT31807    HLT35021      Title1b      Superseded        1
3 HLT31812    HLT35021      Title1c      Superseded        1
4 HLT35015    HLT35021      Title1d      Superseded        1
5 HLT35021    HLT35021      Title1e      Current           1
6 ABC12345    ABC67890      Title2a      Superseded        2
7 ABC67890    ABC67890      Title2b      Current           2

By using the packages stringr and dplyr:

df <- data %>% 
  filter(Course_Status == "Current") %>% 
  mutate(Ety = str_sub(Course_Code, 1, 3)) %>% 
  select(-Course_Status) %>% 
  rename(Current_Course = Course_Code)


df2 <- data %>% 
  select(Course_Code) %>% 
  mutate(Ety = str_sub(Course_Code, 1, 3))


res <- left_join(df2, df, by = "Ety")
res <- res %>% 
  select(-Ety)

res