dplyr - sum of multiple columns using regular expr

问题:

For the dataset mtcars2

mtcars2 = mtcars
mtcars2 = mtcars2 %>% mutate(cyl9=cyl, disp9=disp, gear2=gear)

I want to get a new column which is the sum of multiple columns, by using regular expressions to capture the pattern.

This is a solution, however this is done by hard-coding

select(mtcars2, cyl9) + select(mtcars2, disp9) + select(mtcars2, gear2)

I tried something like this but it gives me a number instead of a vector

mtcars2 %>% select(matches("[0-9]")) %>% sum

Please dplyr solutions only, since i need to apply these functions to a sql table later on.

Thanks!

Update.. I need the solution to work on sql tables, data setup as follow..

mydb <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(mydb, "mt", mtcars)
mt.sql=tbl(mydb, "mt")
mt.sql = mt.sql %>% mutate(cyl9=cyl, disp9=disp, gear2=gear)

reduce(), rowSums(), rowwise() does not work on sql tables, ive tried those and they give me errors.

I've tried,

mt.sql %>% rowwise()

Error: is.data.frame(data) is not TRUE

mt.sql %>% select(matches("[0-9]")) %>% mutate(sum=rowSums(.))

Error in UseMethod("escape") : no applicable method for 'escape' applied to an object of class "c('tbl_dbi', 'tbl_sql', 'tbl_lazy', 'tbl')"

mt.sql %>% select(matches("[0-9]")) %>% reduce(`+`)

Error in .x + .y : non-numeric argument to binary operator

If i switch mt.sql to mtcars2, they all work, so i guess this is a sql table issue.

回答1:

Considering that the SQL constraint prevents use of more simple and elegant solutions such as rowSums and reduce, I offer a more hack-y answer that brings us back to the more basic new_col = a + b + c + ... + n

library(dplyr)
library(stringr)

# get the variable names and form a text equation
col_eqn <- paste0(str_subset(colnames(mtcars), "[a-z]", collapse = " + ")

# run a normal  mutate function parsing and evaluating the equation
mtcars %>% mutate(new_col = eval(parse(text = col_eqn)))

# mpg cyl  disp  hp drat    wt  qsec vs am gear carb new_col
# 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4 328.980
# 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4 329.795
# 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1 259.580
# 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1 426.135
# 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 590.310
# 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1 385.540
# 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 656.920
# 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2 270.980
# 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2 299.570
# 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4 350.460

回答2:

We could use the tidyverse options

library(tidyverse)
mtcars2 %>%
      select(matches("[0-9]")) %>%
      reduce(`+`) #%>%
      #if needed to create a new column   
      #mutate(mtcars2, newcol = .)

#[1] 170.0 170.0 116.0 267.0 371.0 234.0 371.0 154.7 148.8 177.6 177.6 286.8
#[13] 286.8 286.8 483.0 471.0 451.0  86.7  83.7  79.1 127.1 329.0 315.0 361.0
#[25] 411.0  87.0 129.3 104.1 364.0 156.0 314.0 129.0