0

I scraped several tables from a website to be used for a lookup table. I ended up with something similar to this:

a<-tibble(Group=c("A","B","B","B","C","C"),Code=c("2,3","15,16,18,","11,12,13,19,","14,13,15,18,110","111,","178,152,111"))

Now I'd like to convert this into a long tibble with two columns with unique key-value pairs in Group and d. I managed to do it, but my solution seems rather verbose. Is there another way of combining separate and gather to automate this? 

a2<-a %>% separate(Code,sep = ',',into = paste0("n",c(1:5)),fill = "right",convert = T) %>% 
  gather(key=n,value = d,-Group) %>% 
  select(-n) %>% 
  filter(d!="" & !is.na(d)) %>%
  distinct() %>% 
  arrange(Group,d)
Misha
  • 3,114
  • 8
  • 39
  • 60
  • 3
    `a %>% separate_rows(Code) %>% filter(Code != "") %>% distinct()` could work maybe. Or `library(data.table) ; unique(setDT(a)[, unlist(strsplit(as.character(Code), ",")), by = Group], by = "V1")` – David Arenburg Sep 09 '16 at 10:36
  • We can try with `splitstackshape` i.e. `library(splitstackshape); cSplit(a, "Code", ",", "long")[ , unique(Code) , Group]` – akrun Sep 09 '16 at 10:42
  • @David - thx. Just what I was looking for. – Misha Sep 09 '16 at 18:31

0 Answers0