How to replace with only the part before the ":" in every row of a column in R

user_12207261 0
Viewed 83

so in a dataset, I have a column named "Interventions", and each row looks like this:

row1: "Drug: Rituximab|Drug: Utomilumab|Drug: Avelumab|Drug: PF04518600"

row2: "Biological: alemtuzumab|Biological: donor lymphocytes|Drug: carmustine|Drug: cytarabine|Drug: etoposide|Drug: melphalan|Procedure: allogeneic bone marroow"

I want to only extract the Intervention type such as "Drug", "Biological", "Procedure" to remain in the column. And even better, if can only have the unique Intervention type instead of "Drug" 4 times like the first row.

The expected output would look like this:

row1: "Drug"

row2: "Biological, Drug, Procedure"

I am just getting started with r, I have tidyverse installed and kinda used to playing with the %>%. If anyone can help me with this, much appreciated !

dplyr tidyverse r
2 Answers

If we want to extract only the prefix part before the :

library(dplyr)
library(stringr)
library(tidyr)
library(purrr)
df1 %>%
   mutate(Interventions = map_chr(str_extract_all(Interventions, 
          "\\w+(?=:)"), ~ toString(sort(unique(.x)))))
#               Interventions
#1                        Drug
#2 Biological, Drug, Procedure

Or another option is to separate the rows based on the delimiters, slice the alternate rows and paste together the sorted unique values in 'Interventions'

df1 %>%
   mutate(rn = row_number()) %>% 
   separate_rows(Interventions, sep="[:|]") %>% 
   group_by(rn) %>% 
   slice(seq(1, n(), by = 2)) %>%
   distinct() %>%
   summarise(Interventions = toString(sort(unique(Interventions)))) %>%
   ungroup %>% 
   select(-rn)
# A tibble: 2 x 1
#    Interventions              
#  <chr>                      
#1 Drug                       
#2 Biological, Drug, Procedure

data

df1 <- structure(list(Interventions = c("Drug: Rituximab|Drug: Utomilumab|Drug: Avelumab|Drug: PF04518600", 
"Biological: alemtuzumab|Biological: donor lymphocytes|Drug: carmustine|Drug: cytarabine|Drug: etoposide|Drug: melphalan|Procedure: allogeneic bone marroow"
)), class = "data.frame", row.names = c(NA, -2L))
user_37322710

Not as concise and the same logic as Akruns but in Base R: 

# Create df: 

df1 <- structure(list(Interventions = c("Drug: Rituximab|Drug: Utomilumab|Drug: Avelumab|Drug: PF04518600", 
                                        "Biological: alemtuzumab|Biological: donor lymphocytes|Drug: carmustine|Drug: cytarabine|Drug: etoposide|Drug: melphalan|Procedure: allogeneic bone marroow"
)), class = "data.frame", row.names = c(NA, -2L))

# Assign a row id vec: 

df1$row_num <- 1:nrow(df1)

# Split string on | delim: 

split_up <- strsplit(df1$Interventions, split = "[|]")

# Roll down the dataframe - keep uniques: 

rolled_out <- unique(data.frame(row_num = rep(df1$row_num, sapply(split_up, length)),

                             Interventions = gsub("[:].*","", unlist(split_up))))

# Stack the dataframe: 

df2 <- aggregate(Interventions~row_num, rolled_out, paste0, collapse = ", ")

# Drop id vec: 

df2 <- within(df2, rm("row_num"))
user_92031580
Related