How to create a column based on other columns in a vectorized/fast fashion?

user_7507590 0
Viewed 62

I need to create one column which is base on other two columns. The thing is I don't want to use rowwise because it makes it too slow when used on large data, when I don't use rowwise it does not work.

library(tidyverse)

df <- tibble(
 param1 = c(NA, "---", "< 2,8", "5", "6", "7", "-"),
 param2 = c("---", "4", "5.5", "6", NA, "< 10,8", "6")
)

# A tibble: 7 x 2
  param1 param2
  <chr>  <chr> 
1 NA     ---   
2 ---    4     
3 < 2,8  5.5   
4 5      6     
5 6      NA    
6 7      < 10,8
7 -      6

desired_output:

# A tibble: 7 x 3
# Rowwise: 
  param1 param2 param3
  <chr>  <chr>  <chr> 
1 NA     ---    NA    
2 ---    4      NA    
3 < 2,8  5.5    6.9   
4 5      6      11    
5 6      NA     NA    
6 7      < 10,8 12.4  
7 -      6      NA

This is what I tried so far, which produces the correct output, but as I said, it is slow because of rowwise, is there another fast vectorized option?. I'm open to other approaches. Thanks.

somefunction <- function(value) {
  
  parse_number(str_remove(str_replace(value, ",", "."), "<")) * (as.numeric(!str_detect(value, "<")) + 1)/2
  
}

df %>% 
  rowwise() %>% 
  mutate(
    param3 = case_when(
      is.na(param1) || is.na(param2) || str_detect(param1, "^-") || str_detect(param2, "^-") ~ NA_character_,
      TRUE ~ as.character(somefunction(param1) + somefunction(param2))
    )
  )
rowwise dplyr tidyverse case-when r
5 Answers

i'm sure there could be many other approaches but this could be of help:

output <- data.frame(param1,param2,param3 = somefunction(df$param1) + somefunction(df$param2))
user_154821510

Here is tidyverse solution alone:

Explanation

  • In essence it is a sum of two columns mutate(param3 = param1 + param2)
  • First ad id to handle the change of wide to long format and back
  • Make long format pivot_longer tidyr
  • Do some manipulation: commented out in the code
  • Back to wide format pivot_wider
  • sum param1 and param2
library(tidyverse)
df %>% 
  mutate(id = row_number()) %>% 
  pivot_longer(
    cols = starts_with("param"),
    names_to = "param"
  ) %>% 
  mutate(value = gsub("\\,", ".", value)) %>% # , to .
  mutate(first = str_extract(value, "^[^\\s]+"),
         rest = str_extract(value, "\\s.+")) %>%  # isolte <
  mutate(value = as.numeric(gsub("[^[:digit:].]", "",  value))) %>%  # now column is numeric
  mutate(value = ifelse(first =="<", value/2, value)) %>% # solving this < then divide by two problem
  select(-first, -rest) %>% 
  pivot_wider(names_from = "param", values_from = "value") %>%  # back to wide format
  mutate(param3 = param1 + param2) %>% 
  select(-id)

Output:

# A tibble: 7 x 3
  param1 param2 param3
   <dbl>  <dbl>  <dbl>
1   NA     NA     NA  
2   NA      4     NA  
3    1.4    5.5    6.9
4    5      6     11  
5    6     NA     NA  
6    7      5.4   12.4
7   NA      6     NA
user_133216470

I think this is hard to debug because you are trying to do to many things at one time. You're trying to handle NA's, text to numeric conversions, and back to character. You can use the profile package to see what part of the code is actually slow, but otherwise it's hard to tell.

I would recommend defining the NA's in a new column how you want them, then you can use ifelse(), which is vectorized, to generate the numeric transformation. Once you have the numbers, then convert them to character, which is vectorized.

user_59638270

It works with this, It is similar to user15482151 solution, thanks.

df %>% 
  mutate(
    param3 = as.character(somefunction(param1) + somefunction(param2))
    )
user_75075900

Try:

otherfunction <- function(x){
    a <- parse_number(str_extract(x,"\\d+,{0,1}\\d*"), locale = locale(decimal_mark = ",")) *if_else(grepl("<",x),1/2,1)
    if_else(grepl("^-",x) | is.na(x),NA_real_,a)
    }

df %>% mutate(param3 = as.character(otherfunction(param1)+otherfunction(param2)))
user_146695450
Related