Applying a function to every row of a table using dplyr?
Solution 1:
As of dplyr 0.2 (I think) rowwise() is implemented, so the answer to this problem becomes:
iris %>%
rowwise() %>%
mutate(Max.Len= max(Sepal.Length,Petal.Length))
Non rowwise alternative
Five years (!) later this answer still gets a lot of traffic. Since it was given, rowwise is increasingly not recommended, although lots of people seem to find it intuitive. Do yourself a favour and go through Jenny Bryan's Row-oriented workflows in R with the tidyverse material to get a good handle on this topic.
The most straightforward way I have found is based on one of Hadley's examples using pmap:
iris %>%
mutate(Max.Len= purrr::pmap_dbl(list(Sepal.Length, Petal.Length), max))
Using this approach, you can give an arbitrary number of arguments to the function (.f) inside pmap.
pmap is a good conceptual approach because it reflects the fact that when you're doing row wise operations you're actually working with tuples from a list of vectors (the columns in a dataframe).
Solution 2:
The idiomatic approach will be to create an appropriately vectorised function.
R provide pmax which is suitable here, however it also provides Vectorize as a wrapper for mapply to allow you to create a vectorised arbitrary version of an arbitrary function.
library(dplyr)
# use base R pmax (vectorized in C)
iris %>% mutate(max.len = pmax(Sepal.Length, Petal.Length))
# use vectorize to create your own function
# for example, a horribly inefficient get first non-Na value function
# a version that is not vectorized
coalesce <- function(a,b) {r <- c(a[1],b[1]); r[!is.na(r)][1]}
# a vectorized version
Coalesce <- Vectorize(coalesce, vectorize.args = c('a','b'))
# some example data
df <- data.frame(a = c(1:5,NA,7:10), b = c(1:3,NA,NA,6,NA,10:8))
df %>% mutate(ab =Coalesce(a,b))
Note that implementing the vectorization in C / C++ will be faster, but there isn't a magicPony package that will write the function for you.
Solution 3:
You need to group by row:
iris %>% group_by(1:n()) %>% mutate(Max.Len= max(Sepal.Length,Petal.Length))
This is what the 1 did in adply.
Solution 4:
Update 2017-08-03
After writing this, Hadley changed some stuff again. The functions that used to be in purrr are now in a new mixed package called purrrlyr, described as:
purrrlyr contains some functions that lie at the intersection of purrr and dplyr. They have been removed from purrr in order to make the package lighter and because they have been replaced by other solutions in the tidyverse.
So, you will need to install + load that package to make the code below work.
Original post
Hadley frequently changes his mind about what we should use, but I think we are supposed to switch to the functions in purrr to get the by row functionality. At least, they offer the same functionality and have almost the same interface as adply from plyr.
There are two related functions, by_row and invoke_rows. My understanding is that you use by_row when you want to loop over rows and add the results to the data.frame. invoke_rows is used when you loop over rows of a data.frame and pass each col as an argument to a function. We will only use the first.
Examples
library(tidyverse)
iris %>%
by_row(..f = function(this_row) {
browser()
})
This lets us see the internals (so we can see what we are doing), which is the same as doing it with adply.
Called from: ..f(.d[[i]], ...)
Browse[1]> this_row
# A tibble: 1 × 5
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <fctr>
1 5.1 3.5 1.4 0.2 setosa
Browse[1]> Q
By default, by_row adds a list column based on the output:
iris %>%
by_row(..f = function(this_row) {
this_row[1:4] %>% unlist %>% mean
})
gives:
# A tibble: 150 × 6
Sepal.Length Sepal.Width Petal.Length Petal.Width Species .out
<dbl> <dbl> <dbl> <dbl> <fctr> <list>
1 5.1 3.5 1.4 0.2 setosa <dbl [1]>
2 4.9 3.0 1.4 0.2 setosa <dbl [1]>
3 4.7 3.2 1.3 0.2 setosa <dbl [1]>
4 4.6 3.1 1.5 0.2 setosa <dbl [1]>
5 5.0 3.6 1.4 0.2 setosa <dbl [1]>
6 5.4 3.9 1.7 0.4 setosa <dbl [1]>
7 4.6 3.4 1.4 0.3 setosa <dbl [1]>
8 5.0 3.4 1.5 0.2 setosa <dbl [1]>
9 4.4 2.9 1.4 0.2 setosa <dbl [1]>
10 4.9 3.1 1.5 0.1 setosa <dbl [1]>
# ... with 140 more rows
if instead we return a data.frame, we get a list with data.frames:
iris %>%
by_row( ..f = function(this_row) {
data.frame(
new_col_mean = this_row[1:4] %>% unlist %>% mean,
new_col_median = this_row[1:4] %>% unlist %>% median
)
})
gives:
# A tibble: 150 × 6
Sepal.Length Sepal.Width Petal.Length Petal.Width Species .out
<dbl> <dbl> <dbl> <dbl> <fctr> <list>
1 5.1 3.5 1.4 0.2 setosa <data.frame [1 × 2]>
2 4.9 3.0 1.4 0.2 setosa <data.frame [1 × 2]>
3 4.7 3.2 1.3 0.2 setosa <data.frame [1 × 2]>
4 4.6 3.1 1.5 0.2 setosa <data.frame [1 × 2]>
5 5.0 3.6 1.4 0.2 setosa <data.frame [1 × 2]>
6 5.4 3.9 1.7 0.4 setosa <data.frame [1 × 2]>
7 4.6 3.4 1.4 0.3 setosa <data.frame [1 × 2]>
8 5.0 3.4 1.5 0.2 setosa <data.frame [1 × 2]>
9 4.4 2.9 1.4 0.2 setosa <data.frame [1 × 2]>
10 4.9 3.1 1.5 0.1 setosa <data.frame [1 × 2]>
# ... with 140 more rows
How we add the output of the function is controlled by the .collate param. There's three options: list, rows, cols. When our output has length 1, it doesn't matter whether we use rows or cols.
iris %>%
by_row(.collate = "cols", ..f = function(this_row) {
this_row[1:4] %>% unlist %>% mean
})
iris %>%
by_row(.collate = "rows", ..f = function(this_row) {
this_row[1:4] %>% unlist %>% mean
})
both produce:
# A tibble: 150 × 6
Sepal.Length Sepal.Width Petal.Length Petal.Width Species .out
<dbl> <dbl> <dbl> <dbl> <fctr> <dbl>
1 5.1 3.5 1.4 0.2 setosa 2.550
2 4.9 3.0 1.4 0.2 setosa 2.375
3 4.7 3.2 1.3 0.2 setosa 2.350
4 4.6 3.1 1.5 0.2 setosa 2.350
5 5.0 3.6 1.4 0.2 setosa 2.550
6 5.4 3.9 1.7 0.4 setosa 2.850
7 4.6 3.4 1.4 0.3 setosa 2.425
8 5.0 3.4 1.5 0.2 setosa 2.525
9 4.4 2.9 1.4 0.2 setosa 2.225
10 4.9 3.1 1.5 0.1 setosa 2.400
# ... with 140 more rows
If we output a data.frame with 1 row, it matters only slightly which we use:
iris %>%
by_row(.collate = "cols", ..f = function(this_row) {
data.frame(
new_col_mean = this_row[1:4] %>% unlist %>% mean,
new_col_median = this_row[1:4] %>% unlist %>% median
)
})
iris %>%
by_row(.collate = "rows", ..f = function(this_row) {
data.frame(
new_col_mean = this_row[1:4] %>% unlist %>% mean,
new_col_median = this_row[1:4] %>% unlist %>% median
)
})
both give:
# A tibble: 150 × 8
Sepal.Length Sepal.Width Petal.Length Petal.Width Species .row new_col_mean new_col_median
<dbl> <dbl> <dbl> <dbl> <fctr> <int> <dbl> <dbl>
1 5.1 3.5 1.4 0.2 setosa 1 2.550 2.45
2 4.9 3.0 1.4 0.2 setosa 2 2.375 2.20
3 4.7 3.2 1.3 0.2 setosa 3 2.350 2.25
4 4.6 3.1 1.5 0.2 setosa 4 2.350 2.30
5 5.0 3.6 1.4 0.2 setosa 5 2.550 2.50
6 5.4 3.9 1.7 0.4 setosa 6 2.850 2.80
7 4.6 3.4 1.4 0.3 setosa 7 2.425 2.40
8 5.0 3.4 1.5 0.2 setosa 8 2.525 2.45
9 4.4 2.9 1.4 0.2 setosa 9 2.225 2.15
10 4.9 3.1 1.5 0.1 setosa 10 2.400 2.30
# ... with 140 more rows
except that the second has the column called .row and the first does not.
Finally, if our output is longer than length 1 either as a vector or as a data.frame with rows, then it matters whether we use rows or cols for .collate:
mtcars[1:2] %>% by_row(function(x) 1:5)
mtcars[1:2] %>% by_row(function(x) 1:5, .collate = "rows")
mtcars[1:2] %>% by_row(function(x) 1:5, .collate = "cols")
produces, respectively:
# A tibble: 32 × 3
mpg cyl .out
<dbl> <dbl> <list>
1 21.0 6 <int [5]>
2 21.0 6 <int [5]>
3 22.8 4 <int [5]>
4 21.4 6 <int [5]>
5 18.7 8 <int [5]>
6 18.1 6 <int [5]>
7 14.3 8 <int [5]>
8 24.4 4 <int [5]>
9 22.8 4 <int [5]>
10 19.2 6 <int [5]>
# ... with 22 more rows
# A tibble: 160 × 4
mpg cyl .row .out
<dbl> <dbl> <int> <int>
1 21 6 1 1
2 21 6 1 2
3 21 6 1 3
4 21 6 1 4
5 21 6 1 5
6 21 6 2 1
7 21 6 2 2
8 21 6 2 3
9 21 6 2 4
10 21 6 2 5
# ... with 150 more rows
# A tibble: 32 × 7
mpg cyl .out1 .out2 .out3 .out4 .out5
<dbl> <dbl> <int> <int> <int> <int> <int>
1 21.0 6 1 2 3 4 5
2 21.0 6 1 2 3 4 5
3 22.8 4 1 2 3 4 5
4 21.4 6 1 2 3 4 5
5 18.7 8 1 2 3 4 5
6 18.1 6 1 2 3 4 5
7 14.3 8 1 2 3 4 5
8 24.4 4 1 2 3 4 5
9 22.8 4 1 2 3 4 5
10 19.2 6 1 2 3 4 5
# ... with 22 more rows
So, bottom line. If you want the adply(.margins = 1, ...) functionality, you can use by_row.
Solution 5:
Extending BrodieG's answer,
If the function returns more than one row, then instead of mutate(), do() must be used. Then to combine it back together, use rbind_all() from the dplyr package.
In dplyr version dplyr_0.1.2, using 1:n() in the group_by() clause doesn't work for me. Hopefully Hadley will implement rowwise() soon.
iris %>%
group_by(1:nrow(iris)) %>%
do(do_fn) %>%
rbind_all()
Testing the performance,
library(plyr) # plyr_1.8.4.9000
library(dplyr) # dplyr_0.8.0.9000
library(purrr) # purrr_0.2.99.9000
library(microbenchmark)
d1_count <- 1000
d2_count <- 10
d1 <- data.frame(a=runif(d1_count))
do_fn <- function(row){data.frame(a=row$a, b=runif(d2_count))}
do_fn2 <- function(a){data.frame(a=a, b=runif(d2_count))}
op <- microbenchmark(
plyr_version = plyr::adply(d1, 1, do_fn),
dplyr_version = d1 %>%
dplyr::group_by(1:nrow(d1)) %>%
dplyr::do(do_fn(.)) %>%
dplyr::bind_rows(),
purrr_version = d1 %>% purrr::pmap_dfr(do_fn2),
times=50)
it has the following results:
Unit: milliseconds
expr min lq mean median uq max neval
plyr_version 1227.2589 1275.1363 1317.3431 1293.5759 1314.4266 1616.5449 50
dplyr_version 977.3025 1012.6340 1035.9436 1025.6267 1040.5882 1449.0978 50
purrr_version 609.5790 629.7565 643.8498 644.2505 656.1959 686.8128 50
This shows that the new purrr version is the fastest