1

I have this data

     COL
    AABC1
   AAAABD2
  AAAAAABF3

I would like to make a certain column like this:

     COL         NEW_COL
    AABC1          T1
   AAAABD2         T2
  AAAAAABF3        T3

If COL contains 'BC', NEW_COL will be T1

contains 'BD', it will be T2

contains 'BF', it will be T3.

I would like to use mutate and grepl function but I have 80 conditions (like BC>T1) so that the code does not work in the R.

With the table like:

    CLASS       NEW_COL
    BC          T1
    BD          T2
    BF          T3

Could I use mutate(create) new column with above standard table ??

lmo
  • 37,904
  • 9
  • 56
  • 69
jhyeon
  • 456
  • 4
  • 14
  • You can use mutate to create a new column, but it sounds like you will need to write a function to get the values you need. – Kevin Jul 22 '17 at 02:44

2 Answers2

2

Here's your data:

DF <- data.frame(COL = c("AABC1",
                         "AAAABD2",
                         "AAAAABF3"), 
                 stringsAsFactors = FALSE)

lookup_tbl <- data.frame(CLASS = c("BC", "BD", "BF"),
                         NEW_COL = c("T1", "T2", "T3"), 
                         stringsAsFactors = FALSE)

Your problem is solved by a join, after some initial preparation.

To prepare DF, you need to add a column that extracts any instance of CLASS in the lookup table from COL in DF. Then you can join normally. In R:

library(dplyr)
DF %>%
  mutate(CLASS = gsub(paste0("^.*(", 
                            paste0(lookup_tbl[["CLASS"]], collapse = "|"),
                            ").*$"), 
                      "\\1",
                      lookup_tbl[["CLASS"]])) %>%
  # or inner_join as required
  left_join(lookup_tbl, by = "CLASS")

How the solution should behave COL matches zero or more than one instance in CLASS will need to be specified. The above handles both cases, but maybe not how you'd like.

Hugh
  • 15,521
  • 12
  • 57
  • 100
0

You can create a lookup table with your 80 conditions and write a little function to match against it. Here's an example (normally, you'd read in lookup_table from file, I'm guessing):

library(tidyverse)

lookup_table <- data.frame(
                    row.names = c('BC', 'BD', 'BF'), 
                    new_col = c('T1', 'T2', 'T3'), 
                    stringsAsFactors = FALSE)

lookup <- function(x, table) {
    for (class in rownames(table)) {
        if (grepl(class, x)) {
            return(table[class, 'new_col'])
        }
    }
}

data_frame(col = c('AABC1', 'AAAABD2', 'AAAAAABF3')) %>% 
    rowwise %>% mutate(new_col = lookup(col, lookup_table))

Note that this will take the first match it finds, so be sure your lookup table is ordered properly with respect to the priority you want to give the matching rules.

rmuraglia
  • 11
  • 2