Assigning new values from another column in R

Viewed 128

I want to make a new column in R that contain values from another column but I need to reassign them. This is an example: The data is look like this:

ID column1  column2 
1  C*0421   C*0232
2  C*0531   C*0332
3  C*0502   C*0544
4  C*0432   C*0132
5  C*0632   C*0703

So what I want is to create a new column that should assign anything starts with :

C*04 should be C1
C*05 should be C2
C*07 should be C1
C*02 should be C2 
NA   should be NA

and the final results should look like this:

ID column1  column2   results_from_column1  results_from_column2     Combining_the_results
1  C*0421   C*0232     C1                        C2                       C1/C2
2  C*0531   C*0332     C2                        C1                       C2/C1
3  C*0502   C*0544     C2                        C2                       C2/C2  
4  C*0432   C*0132     C1                        C1                       C1/C1
5  C*0632   C*0703     C2                        C1                       C2/C1

I tried to use this code but it gave me wrong assigning for some of the values and i did not know why

df2<- 
  df1%>% 
  mutate(
    C.groups = case_when(
      str_detect(column1, "C*01")~ "C1",
      str_detect(column2, "C*03") ~ "C1", 
        is.na(HLA.C) ~ "NA",
         TRUE ~ "C2"
    )
  )

Please, can you help me?

4 Answers

You could also use the following solution:

library(dplyr)

df %>%
  mutate(across(!ID, ~ case_when(
    grepl("C*04", .) ~ "C1",
    grepl("C*05", .) ~ "C2",
    grepl("C*07", .) ~ "C2",
    grepl("C*02", .) ~ "C2",
    NA ~ NA_character_,
    TRUE ~ "C2"), .names = "results_from_{.col}")) %>%
  rowwise() %>%
  mutate(Combining_the_results = paste(results_from_column1, results_from_column2, 
                                        sep = "/"))

# A tibble: 5 x 6
# Rowwise: 
     ID column1 column2 results_from_column1 results_from_column2 Combining_the_results
  <int> <chr>   <chr>   <chr>                <chr>                <chr>                
1     1 C*0421  C*0232  C1                   C2                   C1/C2                
2     2 C*0531  C*0332  C2                   C2                   C2/C2                
3     3 C*0502  C*0544  C2                   C2                   C2/C2                
4     4 C*0432  C*0132  C1                   C2                   C1/C2                
5     5 C*0632  C*0703  C2                   C2                   C2/C2 

The asterisk (*) in the string pattern is treated as a wild card character in str_detect (or grepl or other functions that uses regular expressions, too). You need to add two backslashes (\\) to before the asterisk to escape the special behaviour (i.e., replace * with \\* in the string).

library(tidyverse)

# example data (I added one new row to illustrate the code that you have)
df1 <- data.frame(
    index = 1:6, 
    column1 = c("C*0421", "C*0531", "C*0502", "C*0432", "C*0332", "C*0123"), 
    column2 = c("C*0232", "C*0332", "C*0544", "C*0132", "C*0703", "C*0987")) 


df1%>% # based on the code you're using
    mutate(
        C.groups = case_when(
            str_detect(column1, "C\\*01") ~ "C1",
            str_detect(column2, "C\\*03") ~ "C1"
        )
    )

#  index column1 column2 C.groups
#1     1  C*0421  C*0232     <NA>
#2     2  C*0531  C*0332       C1
#3     3  C*0502  C*0544     <NA>
#4     4  C*0432  C*0132     <NA>
#5     5  C*0332  C*0703     <NA>
#6     6  C*0123  C*0987       C1

See here for details about regular expressions: https://cran.r-project.org/web/packages/stringr/vignettes/regular-expressions.html

If I'm following your conditions, as described in the OP, and creating the new desired column variables, then it would be like this:

df1%>% 
    mutate(
        results_from_column1 = case_when(
            str_detect(column1, "^C\\*04") ~ "C1", # anything starts with C*04 should be C1
            str_detect(column1, "^C\\*05") ~ "C2", # anything starts with C*05 should be C2
            str_detect(column1, "^C\\*07") ~ "C1", # anything starts with C*07 should be C1
            str_detect(column1, "^C\\*02") ~ "C2"  # anything starts with C*02 should be C2 
        ),
        results_from_column2 = case_when(
            str_detect(column2, "^C\\*04") ~ "C1", # anything starts with C*04 should be C1
            str_detect(column2, "^C\\*05") ~ "C2", # anything starts with C*05 should be C2
            str_detect(column2, "^C\\*07") ~ "C1", # anything starts with C*07 should be C1
            str_detect(column2, "^C\\*02") ~ "C2"  # anything starts with C*02 should be C2 
        ), 
        Combining_the_results = paste0(results_from_column1, "/", results_from_column2)
    )

#  index column1 column2 results_from_column1 results_from_column2 Combining_the_results
#1     1  C*0421  C*0232                   C1                   C2                 C1/C2
#2     2  C*0531  C*0332                   C2                 <NA>                 C2/NA
#3     3  C*0502  C*0544                   C2                   C2                 C2/C2
#4     4  C*0432  C*0132                   C1                 <NA>                 C1/NA
#5     5  C*0332  C*0703                 <NA>                   C1                 NA/C1

Note 1 – Undefined cases for case_when become NA's in the new column; thus, defining "NA should be NA" would be unnecessary when using case_when.
Note 2 – There are some NA's because they were not defined in your conditions in the original post. (Hence, the output based on your assignment conditions is different from your expected final results.)
Note 3 – The caret (^) at the beginning of the string pattern (e.g., "^C\\*04") is a special/regex character that anchors the matched string pattern to the beginning of the text. Although it's unnecessary with this example data here, I added it to be elaborate and meet the condition, "anything starts with ..."

We could mutate across over columns column1 to column2 Use case_when for our condition as you mentioned in your question and mutate the Combining_the_results column.

library(dplyr)
library(stringr)
df %>% 
    mutate(across(starts_with("column"), ~case_when(
        str_detect(., "C*01|C*03|C*04|C*07") ~ "C1",
        str_detect(., "C*02|C*05") ~ "C2",
        is.na(.) ~ NA_character_,
        TRUE ~ "C2"), .names = "results_from_{.col}")) %>% 
    mutate(Combining_the_results = paste(results_from_column1, results_from_column2, sep="/"))

output:

 ID column1 column2 results_from_column1 results_from_column2 Combining_the_results
1  1  C*0421  C*0232                   C1                   C2                 C1/C2
2  2  C*0531  C*0332                   C2                   C1                 C2/C1
3  3  C*0502  C*0544                   C2                   C2                 C2/C2
4  4  C*0432  C*0132                   C1                   C1                 C1/C1
5  5  C*0632  C*0703                   C2                   C1                 C2/C1
df <- read.table(header = T, text = 'ID column1  column2 
1  C*0421   C*0232
2  C*0531   C*0332
3  C*0502   C*0544
4  C*0432   C*0132
5  C*0632   C*0703')

library(tidyverse)

df %>%
  mutate(across(starts_with('column'), ~ c('C2', 'C1')[1 +(substr(., 3, 4) %in% c('04', '07'))],
                .names = 'results_from_{.col}')) %>%
  mutate(combining_the_results = pmap(select(cur_data(), starts_with('results')), ~ paste(c(...), collapse = '/')))

  ID column1 column2 results_from_column1 results_from_column2 combining_the_results
1  1  C*0421  C*0232                   C1                   C2                 C1/C2
2  2  C*0531  C*0332                   C2                   C2                 C2/C2
3  3  C*0502  C*0544                   C2                   C2                 C2/C2
4  4  C*0432  C*0132                   C1                   C2                 C1/C2
5  5  C*0632  C*0703                   C2                   C1                 C2/C1

If you want to use C1 for "C*01|C*03|C*04|C*07" as shown in example, please modify the code as

df %>%
  mutate(across(starts_with('column'), ~ c('C2', 'C1')[1 +(substr(., 3, 4) %in% c('04', '07', '01', '03'))],
                .names = 'results_from_{.col}')) %>%
  mutate(combining_the_results = pmap(select(cur_data(), starts_with('results')), ~ paste(c(...), collapse = '/')))

  ID column1 column2 results_from_column1 results_from_column2 combining_the_results
1  1  C*0421  C*0232                   C1                   C2                 C1/C2
2  2  C*0531  C*0332                   C2                   C1                 C2/C1
3  3  C*0502  C*0544                   C2                   C2                 C2/C2
4  4  C*0432  C*0132                   C1                   C1                 C1/C1
5  5  C*0632  C*0703                   C2                   C1                 C2/C1
Related