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.
We could use the
tidyverse
optionsConsidering that the SQL constraint prevents use of more simple and elegant solutions such as
rowSums
andreduce
, I offer a more hack-y answer that brings us back to the more basicnew_col = a + b + c + ... + n