Create categorical variable/data subset with case matching

user_13659834 0
Viewed 33

I have a dataset like this:

structure(list(year = c(2019, 2019, 2019, 2019, 2019, 2019), 
    venue = c("Z", "Z", "Z", "Z", "O", "D"), HO = c("X", "Y", 
    "X", "Y", "W", "J"), AW = c("Y", "X", "W", "T", "T", "X")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

I need to create a categorical variable that returns "yes" or "no".

structure(list(year = c(2019, 2019, 2019, 2019, 2019, 2019), 
    venue = c("Z", "Z", "Z", "Z", "O", "D"), HO = c("X", "Y", 
    "X", "Y", "W", "J"), AW = c("Y", "X", "W", "T", "T", "X"), 
    Cat = c("yes", "yes", "no", "no", "no", "no")), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

As note, the first two rows are cases for "yes". These are the same in the attributes "year" and "venue", regardless of the order in "HO" and "AW".

Thanks.

data-wrangling dplyr subset match r
1 Answers

You can first group your dataframe by row using rowwise(), then paste the HO and AW columns together. This step is to see which rows have the same HO and AW regardless of order. Then group_by the unique combination to categorise them.

library(dplyr)
library(stringr)

df %>% 
  rowwise() %>% 
  mutate(paste_col = paste0(sort(str_split(paste0(HO, AW), "", simplify = T)), collapse = ",")) %>% 
  group_by(year, venue, paste_col) %>% 
  mutate(Cat = ifelse(n() > 1, "Yes", "No")) %>% 
  ungroup() %>% 
  select(-paste_col)

# A tibble: 6 × 5
   year venue HO    AW    Cat  
  <dbl> <chr> <chr> <chr> <chr>
1  2019 Z     X     Y     Yes  
2  2019 Z     Y     X     Yes  
3  2019 Z     X     W     No   
4  2019 Z     Y     T     No   
5  2019 O     W     T     No   
6  2019 D     J     X     No
user_166474960
Related