R Paste string into a new column if there is a match across multiple columns by a specified string value

k_grace 0
Viewed 46

I am trying to create a new column in my dataset that extracts a specified string value if it's present across any multiple columns and paste that value into a new column.

To explain further my dataset has multiple ICD codes across columns. See below. I want to extract any column values that have a string containing "V0" and paste that entire string into the new column called "match"

structure(list(ID = c(2L, 7L, 8L, 13L, 15L, 16L, 18L, 25L, 29L, 
35L, 36L, 42L, 44L, 46L, 48L, 49L, 61L, 62L, 64L, 65L), code1 = c("S020XXB", 
"S2243XA", "S32052A", "S069X1A", "S0210XA", "S72331A", "S0264XA", 
"S065X0A", "S066X9A", "S06352A", "S0219XA", "S066X9A", "S8255XA", 
"S02119A", "S36113A", "S065X9A", "S022XXA", "S82102A", "S060X1A", 
"S72451B"), code2 = c("S066X9A", "J9600", "S066X0A", "S42292A", 
"J9601", "S060X9A", "J690", "B1920", "J9601", "J9600", "S066X0A", 
"J9601", "S32591A", "S129XXA", "S270XXA", "I10", "J15211", "J9600", 
"S2242XA", "S065X0A"), code3 = c("S065X9A", "S270XXA", "S37032A", 
"S42425A", "S270XXA", "S42001A", "G92", "V00211A", "J690", "S22069A", 
"F17210", "S82142A", "K760", "S270XXA", "J90", "F17210", "F10121", 
"G9340", "S42032A", "S32461A"), code4 = c("S064X9A", "S069X9A", 
"S52572A", "V4959XA", "S066X5A", "K5900", "Z6843", "Y92838", 
"R6510", "R40243", "R911", "S27329A", "F419", "S27322A", "S060X9A", 
"M542", "M6282", "F10231", "S2231XA", "J9690"), code5 = c("S0102XA", 
"S25391A", "I252", "Y92488", "G931", "S8012XA", "E871", "", "E222", 
"F1010", "S61310A", "S4291XA", "I10", "S22029A", "J9811", "R40241", 
"S02600B", "E440", "V4351XA", "R578"), code6 = c("S82841A", "S3210XA", 
"E039", "", "R1312", "S8011XA", "D62", "", "I480", "H532", "F17290", 
"S2243XA", "S060X0A", "S2232XA", "S0181XA", "Z9114", "E441", 
"N179", "Y92410", "G92"), match = c("NULL", "NULL", "NULL", "NULL", 
"NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", 
"NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL"
)), row.names = c(9L, 286L, 363L, 760L, 905L, 909L, 1027L, 1610L, 
1884L, 2072L, 2201L, 3167L, 3367L, 3459L, 3809L, 4052L, 4780L, 
4804L, 4982L, 5009L), class = "data.frame")

I want to paste a the entire string value that contains the character "V" into the "match" column IF a string with "V" is found in any of the code columns.


to_match<-c("V")

from this 
   ID   code1   code2   code3   code4   code5   code6 match
9    2 S020XXB S066X9A S065X9A S064X9A S0102XA S82841A  NULL
286  7 S2243XA   J9600 S270XXA S069X9A S25391A S3210XA  NULL
363  8 S32052A S066X0A S37032A S52572A    I252    E039  NULL
760 13 S069X1A S42292A S42425A V4959XA  Y92488          NULL
905 15 S0210XA   J9601 S270XXA S066X5A    G931   R1312  NULL
909 16 S72331A S060X9A S42001A   K5900 S8012XA S8011XA  NULL


to this
   ID   code1   code2   code3   code4   code5   code6 match
9    2 S020XXB S066X9A S065X9A S064X9A S0102XA S82841A  NULL
286  7 S2243XA   J9600 S270XXA S069X9A S25391A S3210XA  NULL
363  8 S32052A S066X0A S37032A S52572A    I252    E039  NULL
760 13 S069X1A S42292A S42425A V4959XA  Y92488          V4959XA  
905 15 S0210XA   J9601 S270XXA S066X5A    G931   R1312  NULL
909 16 S72331A S060X9A V42001A   K5900 S8012XA S8011XA  V42001A
grepl multiple-columns r
1 Answers

Here is one option with tidyverse, where we search across columns that start with code, then extract any value that contains a V.

library(tidyverse)

df %>% 
  rowwise() %>%
  mutate(match = str_subset(c_across(starts_with("code")), "V")[1]) %>% 
  ungroup

Output

      ID code1   code2   code3   code4   code5     code6     match  
   <int> <chr>   <chr>   <chr>   <chr>   <chr>     <chr>     <chr>  
 1     2 S020XXB S066X9A S065X9A S064X9A "S0102XA" "S82841A" NA     
 2     7 S2243XA J9600   S270XXA S069X9A "S25391A" "S3210XA" NA     
 3     8 S32052A S066X0A S37032A S52572A "I252"    "E039"    NA     
 4    13 S069X1A S42292A S42425A V4959XA "Y92488"  ""        V4959XA
 5    15 S0210XA J9601   S270XXA S066X5A "G931"    "R1312"   NA     
 6    16 S72331A S060X9A S42001A K5900   "S8012XA" "S8011XA" NA     
 7    18 S0264XA J690    G92     Z6843   "E871"    "D62"     NA     
 8    25 S065X0A B1920   V00211A Y92838  ""        ""        V00211A
 9    29 S066X9A J9601   J690    R6510   "E222"    "I480"    NA     
10    35 S06352A J9600   S22069A R40243  "F1010"   "H532"    NA     
11    36 S0219XA S066X0A F17210  R911    "S61310A" "F17290"  NA     
12    42 S066X9A J9601   S82142A S27329A "S4291XA" "S2243XA" NA     
13    44 S8255XA S32591A K760    F419    "I10"     "S060X0A" NA     
14    46 S02119A S129XXA S270XXA S27322A "S22029A" "S2232XA" NA     
15    48 S36113A S270XXA J90     S060X9A "J9811"   "S0181XA" NA     
16    49 S065X9A I10     F17210  M542    "R40241"  "Z9114"   NA     
17    61 S022XXA J15211  F10121  M6282   "S02600B" "E441"    NA     
18    62 S82102A J9600   G9340   F10231  "E440"    "N179"    NA     
19    64 S060X1A S2242XA S42032A S2231XA "V4351XA" "Y92410"  V4351XA
20    65 S72451B S065X0A S32461A J9690   "R578"    "G92"     NA

Another option is to pivot into a long format, then we can just filter to the values that have a V, then rejoin to the dataframe.

df %>% 
  pivot_longer(starts_with("code")) %>% 
  filter(str_detect(value, "V")) %>% 
  select(ID, match = value) %>% 
  left_join(df, ., by = "ID")
AndrewGB0
Related