Using a custom function inside of dplyr mutate?

Question

I have a sql table set up via dplyr like this:

num                   gameday_link stand b_height     px    pz    type
 1   1 gid_2016_04_05_houmlb_nyamlb_1     R      5-6  0.194 3.225    B
 2   1 gid_2016_04_05_houmlb_nyamlb_1     R      5-6  0.510 1.965    S
 3   1 gid_2016_04_05_houmlb_nyamlb_1     R      5-6 -1.367 2.459    B

What I would like to do is add an additional column called correct, which holds the T or F results of a custom function called isCallCorrectV. The parameters of isCallCorrectV are columns in the table.

Here is an example of isCallCorrectV in action:

> isCallCorrectV(c(0.0, 2.5), c(2.5, 0), c("S", "B"), c("6-0", "5-0"), c("R", "L"))
[1]  TRUE FALSE

I tried using this command, but I get an error.

dplyr::mutate(noswings, correct = isCallCorrectV(px, pz, type, b_height, stand))
Error in sqliteSendQuery(conn, statement) : 
error in statement: no such function: ISCALLCORRECTV

Provide code for the function and do not that spelling in R is case-sensitive. — IRTFM
– IRTFM, Commented Jun 20, 2016 at 23:11
It sounds like you're you're using a SQLite backend? That's not going to work with a custom function written in R. If you look at dplyr's vignette on databses you can find a list of the R functions that dplyr knows how to convert to SQL (and isCallCorrect isn't on that list). — Gregor Thomas
– Gregor Thomas, Commented Jun 20, 2016 at 23:17
If isCallCorrect is made up of only those functions then you could potentially write it out long-form inside mutate() to get your desired result. That won't be very friendly, and since your function calls another custom function that calls ldply I'm going to say it's not an option here. — Gregor Thomas
– Gregor Thomas, Commented Jun 20, 2016 at 23:33
Your two options are (1) (as you say) read the data into R and operate on it there using your custom R functions, or (2) rewrite the custom functions in SQL and define them in your database. — Gregor Thomas
– Gregor Thomas, Commented Jun 20, 2016 at 23:34
When you're using a database back-end with dplyr, what dplyr is doing for you is translating R syntax to SQL syntax. When you start using custom functions dplyr just tries to call them. If you're working in R and calling R functions, everything's great. If you're working in SQL and calling SQL functions, everything's great. But dplyr can't translate arbitrary R code to SQL, so your custom functions need to match the back-end language (and be defined on the back-end). — Gregor Thomas
– Gregor Thomas, Commented Jun 20, 2016 at 23:40

zkello · Accepted Answer · 2016-06-24 18:40:40Z

1

Per @Gregor's comments, it isn't possible to use custom functions in dplyr on a sql database. Only the functions define here can be used.

So my options are:

(1) Read the data into R and operate on it there using your custom R functions, or (2) rewrite the custom functions in SQL and define them in your database

I can use the dplyr collect command to read the data into R.

answered Jun 24, 2016 at 18:40

zkello

2393 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ferroao Over a year ago

broken link , seems exists no more

Collectives™ on Stack Overflow

Using a custom function inside of dplyr mutate?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related