Sorting a data with characters and numbers into numbers in R

user_7706651 0
Viewed 165

I have a list of data with results that include numbers and also include text.

Example data:

df$col_1 
Neg 
Negative 
32 
16 
64 
8 
128 
4 
not done 
Pos 
Missing 
?Pos 
~2 
? 240

What I have done is create a new column and try to re-code the data. 

 df$col <- NA df$col [ which (df$col_1=="Positive" )] <- 1 
 df$col [ which (df$col_1=="2" )] <- 1 
 df$col [ which (df$col_1=="Negative" )] <- 1

Rather than coding every possible combination, as above, What I would like to do is be able to create a list of negatives, positives and NA values.

I tried this

list <- c ("2","4","8","16","32")
df$col [ which (df$col_1=="list" )] <- 1

But this did not work.

Every number should be considered positive, unless there is a question mark. So I wondered whether I could convert all numbers to numeric?

For all the miscellaneous text, other than positive and negative, I want to put NA. 

df$col_1        df$col
Neg             0
Negative        0
32              1 
16              1
64              1
8               1
128             1
4               1
not done        NA
Pos             1
Missing         NA
?Pos            NA
~2              1
? 240           NA
r
3 Answers

You potentially have a fairly complex set of conditions, so you might be better of using regular expressions with ifelse and sapply. For instance, below I use grepl in nested ifelses:

df$col <- sapply(df$col_1,
       function(x) ifelse(grepl("^((~)?\\d+)$|^([pP]os(itive)?)$", x),
                          1,
                          ifelse(grepl("^[nN]eg(ative)?$", x), 0, NA)
                          )
       )

#### OUTPUT ####

      col_1 col
1       Neg   0
2  Negative   0
3        32   1
4        16   1
5        64   1
6         8   1
7       128   1
8         4   1
9  not done  NA
10      Pos   1
11  Missing  NA
12     ?Pos  NA
13       ~2   1
14        ?  NA
15      240   1

Explanation: It the string contains only digits, with or without preceding tilda ~, or only "Pos" or "Positive", return 1. Otherwise return the output of the second ifelse, which returns 0 if the string contains only "Neg" or "Negative", otherwise NA.

Data:

df <- structure(list(col_1 = c("Neg", "Negative", "32", "16", "64", 
"8", "128", "4", "not done", "Pos", "Missing", "?Pos", "~2", 
"?", "240")), class = "data.frame", row.names = c(NA, -15L))
Admin1

You can list down all your conditions in a case_when statement. Note that in case_when conditions are executed sequentially so if one of the condition is satisfied it doesn't check for other conditions so start with the most specific condition and then add the general ones.

library(dplyr)

df %>%
                         #Has a question mark then NA
  mutate(col = case_when(grepl('\\?', col_1) ~ NA_integer_, 
                         #has "pos" then 1
                         grepl('pos', col_1, ignore.case = TRUE) ~ 1L, 
                         #has "neg" then 1
                         grepl('neg', col_1, ignore.case = TRUE) ~0L, 
                         #Has a number then 1
                         grepl('\\d+', col_1) ~ 1L))

#      col_1 col
#1       Neg   0
#2  Negative   0
#3        32   1
#4        16   1
#5        64   1
#6         8   1
#7       128   1
#8         4   1
#9  not done  NA
#10      Pos   1
#11  Missing  NA
#12     ?Pos  NA
#13       ~2   1
#14    ? 240  NA

Note that it is possible to combine multiple conditions into one to shorten the code but I have kept each condition separate for clarity.

data

df <- structure(list(col_1 = structure(c(11L, 12L, 6L, 5L, 8L, 9L, 
4L, 7L, 13L, 14L, 10L, 2L, 3L, 1L), .Label = c("? 240", "?Pos", 
"~2", "128", "16", "32", "4", "64", "8", "Missing", "Neg", "Negative", 
"not done", "Pos"), class = "factor")), row.names = c(NA, -14L), 
class = "data.frame")
user_39629140

Here's a full tidyverse solution that also builds on the other two answers to make them more general. Also, this solution addresses the comment regarding stratifying the numeric values (ie >= 5 is Positive and < 5 is Negative).

The warning messages are just from calling as.numeric on the character vector. Annoying, but I'm not sure how to avoid.

Note: I just chose Other as the explicit NA level but it can obviously be whatever you want. Also the order of the levels can be put in whatever order needed.

library(tidyverse)
#> Warning: package 'tidyverse' was built under R version 3.5.3
#> Warning: package 'tibble' was built under R version 3.5.3
#> Warning: package 'tidyr' was built under R version 3.5.3
#> Warning: package 'readr' was built under R version 3.5.2
#> Warning: package 'purrr' was built under R version 3.5.3
#> Warning: package 'dplyr' was built under R version 3.5.3
#> Warning: package 'stringr' was built under R version 3.5.2
#> Warning: package 'forcats' was built under R version 3.5.3


df <- structure(list(col_1 = c("Neg", "Negative", "32", "16", "64", 
                               "8", "128", "4", "not done", "Pos", "Missing", "?Pos", "~2", 
                               "?", "240")), class = "data.frame", row.names = c(NA, -15L))


df %>%
  mutate(
    col = case_when(
      str_detect(col_1, "\\?") ~ NA_character_, # if question mark, return NA
      str_detect(col_1, "^[0-9]*$") ~ col_1, # starts and ends with a number, return value
      str_detect(str_to_lower(col_1), "^pos.*") ~ "Positive", # starts with 'pos' then Positive
      str_detect(str_to_lower(col_1), "^neg.*") ~ "Negative", # starts with 'neg' then Negative
      TRUE ~ NA_character_ # else NA
    ),
    col = factor(
      case_when(
        as.numeric(col) >= 5 ~ "Positive", # >= 5 then Positive per comment
        as.numeric(col) < 5  ~ "Negative", # < 5 then Negative per comment
        TRUE                 ~ col
      ),
      c("Positive", "Negative")
    ) %>% fct_explicit_na("Other") # make a factor and explicitly define NA level
  )
#> Warning in eval_tidy(pair$lhs, env = default_env): NAs introduced by coercion
#> Warning in eval_tidy(pair$lhs, env = default_env): NAs introduced by coercion
#>       col_1      col
#> 1       Neg Negative
#> 2  Negative Negative
#> 3        32 Positive
#> 4        16 Positive
#> 5        64 Positive
#> 6         8 Positive
#> 7       128 Positive
#> 8         4 Negative
#> 9  not done    Other
#> 10      Pos Positive
#> 11  Missing    Other
#> 12     ?Pos    Other
#> 13       ~2    Other
#> 14        ?    Other
#> 15      240 Positive

Created on 2020-09-18 by the reprex package (v0.3.0)

user_115164970
Related