Mutate if sum of new created variables starting from specific characters is 0

Slavek · May 21, 2020, 5:37pm

Hi, I have following problem.
I have this simple data frame

library(tidyverse)

sample_data <- data.frame(stringsAsFactors=FALSE,
                          InterviewID = c(94, 59, 100, 86, 60, 101, 61, 7, 23, 8),
                          all_comment = c("None", "geen speciale dvdv", "xxxxx.", "None of products",
                                      "geen speciale", "none", "God!!!!", "aa", "special", "perfect"),
                          ModelLong = c("A", "A", "A", "B", "B", "B", "c", "c", "A", "B"))

Now I am creating some new categories based on comments (thank you siddharthprabhu):

blank_statements <- c("none", "geen\\sspeciale", "commentaar", "neen")
exact_match <- regex(str_c("^", blank_statements, "$", collapse = "|"), ignore_case = TRUE)
partial_match <- regex(str_c(blank_statements, collapse = "|"), ignore_case = TRUE)

sample_data %>%
  mutate(TMC.Blank = case_when(is.na(all_comment) ~ 1,
                               str_length(all_comment) < 4 ~ 1,
                               str_detect(all_comment, exact_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, partial_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, "([a-zA-Z])\\1{2,}") ~ 1),
         TMC.Special =  if_else(str_detect(all_comment, regex("special", ignore_case = TRUE, multiline = TRUE)),  1, 0),
         TMC.Perfect =  if_else(str_detect(all_comment, regex("perfect", ignore_case = TRUE, multiline = TRUE)),  1, 0)
  ) %>% 
  mutate(TMC.Other = ifelse(test = (rowSums(x = .[-(1:3)]) == 0),
                            yes = 1,
                            no = 0)) %>% 
  mutate_at(vars(-c(1:3)), ~if_else(is.na(.), 0, .))

My problem is that TMC.Other is incorrect as values in rows 4 and 7 should be 1. I have 0s instead.
What am I doing wrong?
Also. The sample file is only an example. In my real data, number of variables varies. Is it possible to change this part of the code:

(rowSums(x = .[-(1:3)]) == 0)

by something reverse (so summing variables from the right - in this case I created 3 new variables so it should sum up just them.
Or even better (more universal): Sum values from all variables starting from TMC to create TMC.Other

Can you help?

siddharthprabhu · May 21, 2020, 6:09pm

You're overthinking the problem. Since you have a pattern of column names, why not just pass the selection of columns into rowSums()?

library(tidyverse)

sample_data <- data.frame(stringsAsFactors=FALSE,
                          InterviewID = c(94, 59, 100, 86, 60, 101, 61, 7, 23, 8),
                          all_comment = c("None", "geen speciale dvdv", "xxxxx.", "None of products",
                                          "geen speciale", "none", "God!!!!", "aa", "special", "perfect"),
                          ModelLong = c("A", "A", "A", "B", "B", "B", "c", "c", "A", "B"))

blank_statements <- c("none", "geen\\sspeciale", "commentaar", "neen")
exact_match <- regex(str_c("^", blank_statements, "$", collapse = "|"), ignore_case = TRUE)
partial_match <- regex(str_c(blank_statements, collapse = "|"), ignore_case = TRUE)

sample_data %>%
  mutate(TMC.Blank = case_when(is.na(all_comment) ~ 1,
                               str_length(all_comment) < 4 ~ 1,
                               str_detect(all_comment, exact_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, partial_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, "([a-zA-Z])\\1{2,}") ~ 1),
         TMC.Special =  if_else(str_detect(all_comment, regex("special", ignore_case = TRUE, multiline = TRUE)),  1, 0),
         TMC.Perfect =  if_else(str_detect(all_comment, regex("perfect", ignore_case = TRUE, multiline = TRUE)),  1, 0)
  ) %>% 
  mutate(TMC.Other = rowSums(select(., contains("TMC")), na.rm = TRUE))
#>    InterviewID        all_comment ModelLong TMC.Blank TMC.Special TMC.Perfect
#> 1           94               None         A         1           0           0
#> 2           59 geen speciale dvdv         A        NA           1           0
#> 3          100             xxxxx.         A         1           0           0
#> 4           86   None of products         B        NA           0           0
#> 5           60      geen speciale         B         1           1           0
#> 6          101               none         B         1           0           0
#> 7           61            God!!!!         c        NA           0           0
#> 8            7                 aa         c         1           0           0
#> 9           23            special         A        NA           1           0
#> 10           8            perfect         B        NA           0           1
#>    TMC.Other
#> 1          1
#> 2          1
#> 3          1
#> 4          0
#> 5          2
#> 6          1
#> 7          0
#> 8          1
#> 9          1
#> 10         1

^{Created on 2020-05-21 by the reprex package (v0.3.0)}

Slavek · May 21, 2020, 6:13pm

Excellent!
Can I do the same with this code:

 mutate_at(vars(-c(1:3)), ~if_else(is.na(.), 0, .))

I want to recode all variables containing TMC

Slavek · May 21, 2020, 6:18pm

Also your solution should generate 1 if sum of other TMC categories is 0 or 0 if sum of other TMC categories is not 0. I don't want to recode it later on if possible. The reason behind is the fact that categories can overlap. By the way, this overlap in raw 5 is incorrect as TMC.Special should pick up comments with a word "special" but not "geen speciale". Overlap should take place if we had a comment like "special and perfect".

siddharthprabhu · May 22, 2020, 7:43am

You could but then you're relying on your target columns being in those exact positions. Using contains("TMC")) is more robust I feel.

To replace NA values, consider using tidyr::replace_na() instead.

Slavek · May 22, 2020, 10:23am

I did that:

result_data <- sample_data %>%
  mutate(TMC.Blank = case_when(is.na(all_comment) ~ 1,
                               str_length(all_comment) < 4 ~ 1,
                               str_detect(all_comment, exact_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, partial_match) ~ 1,
                               str_length(all_comment) < 10 & str_detect(all_comment, "([a-zA-Z])\\1{2,}") ~ 1),
         TMC.Special =  if_else(str_detect(all_comment, regex("special", ignore_case = TRUE, multiline = TRUE)),  1, 0),
         TMC.Perfect =  if_else(str_detect(all_comment, regex("perfect", ignore_case = TRUE, multiline = TRUE)),  1, 0)
  ) %>% 
  mutate(TMC.Other = rowSums(select(., contains("TMC")), na.rm = TRUE)) %>% 
  mutate_at(vars(matches("TMC")), ~if_else(is.na(.), 0, .))

result_data$TMC.Other <-ifelse(result_data$TMC.Other>0,1,0)
result_data

Not nice but working. It is a shame I can only do a recode as a separate procedure...

system · June 12, 2020, 10:23am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.