Applying a function to every row of a table using dplyr?

Question

When working with plyr I often found it useful to use adply for scalar functions that I have to apply to each and every row.

e.g.

data(iris)
library(plyr)
head(
     adply(iris, 1, transform , Max.Len= max(Sepal.Length,Petal.Length))
    )
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species Max.Len
1          5.1         3.5          1.4         0.2  setosa     5.1
2          4.9         3.0          1.4         0.2  setosa     4.9
3          4.7         3.2          1.3         0.2  setosa     4.7
4          4.6         3.1          1.5         0.2  setosa     4.6
5          5.0         3.6          1.4         0.2  setosa     5.0
6          5.4         3.9          1.7         0.4  setosa     5.4

Now I'm using dplyr more, I'm wondering if there is a tidy/natural way to do this? As this is NOT what I want:

library(dplyr)
head(
     mutate(iris, Max.Len= max(Sepal.Length,Petal.Length))
    )
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species Max.Len
1          5.1         3.5          1.4         0.2  setosa     7.9
2          4.9         3.0          1.4         0.2  setosa     7.9
3          4.7         3.2          1.3         0.2  setosa     7.9
4          4.6         3.1          1.5         0.2  setosa     7.9
5          5.0         3.6          1.4         0.2  setosa     7.9
6          5.4         3.9          1.7         0.4  setosa     7.9

Answer 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).

Answer 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.

Answer 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.

Answer 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.

Answer 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

Answer 6

In addition to the great answer provided by @alexwhan, please keep in mind that you need to use ungroup() to avoid side effects. This is because rowwise() is a grouping operation.

iris %>%
    rowwise() %>%
    mutate(Max.Len = max(Sepal.Length, Petal.Length))

will give you:

   Sepal.Length Sepal.Width Petal.Length Petal.Width Species Max.Len
          <dbl>       <dbl>        <dbl>       <dbl> <fct>     <dbl>
 1          5.1         3.5          1.4         0.2 setosa      5.1
 2          4.9         3            1.4         0.2 setosa      4.9
 3          4.7         3.2          1.3         0.2 setosa      4.7
 4          4.6         3.1          1.5         0.2 setosa      4.6
 5          5           3.6          1.4         0.2 setosa      5  
 6          5.4         3.9          1.7         0.4 setosa      5.4
 7          4.6         3.4          1.4         0.3 setosa      4.6
 8          5           3.4          1.5         0.2 setosa      5  
 9          4.4         2.9          1.4         0.2 setosa      4.4
10          4.9         3.1          1.5         0.1 setosa      4.9

Now let's assume that you need to continue with the dplyr pipe to add a lead to Max.Len:

iris %>%
    rowwise() %>%
    mutate(Max.Len = max(Sepal.Length, Petal.Length)) %>%
    mutate(Lead.Max.Len = lead(Max.Len))

This will produce:

   Sepal.Length Sepal.Width Petal.Length Petal.Width Species Max.Len Lead.Max.Len
          <dbl>       <dbl>        <dbl>       <dbl> <fct>     <dbl>        <dbl>
 1          5.1         3.5          1.4         0.2 setosa      5.1           NA
 2          4.9         3            1.4         0.2 setosa      4.9           NA
 3          4.7         3.2          1.3         0.2 setosa      4.7           NA
 4          4.6         3.1          1.5         0.2 setosa      4.6           NA
 5          5           3.6          1.4         0.2 setosa      5             NA
 6          5.4         3.9          1.7         0.4 setosa      5.4           NA
 7          4.6         3.4          1.4         0.3 setosa      4.6           NA
 8          5           3.4          1.5         0.2 setosa      5             NA
 9          4.4         2.9          1.4         0.2 setosa      4.4           NA
10          4.9         3.1          1.5         0.1 setosa      4.9           NA

NA's are produced as a side effect. This can be corrected with ungroup():

iris %>%
    rowwise() %>%
    mutate(Max.Len = max(Sepal.Length, Petal.Length)) %>%
    ungroup() %>%
    mutate(Lead.Max.Len = lead(Max.Len))

This will produce the desired output:

   Sepal.Length Sepal.Width Petal.Length Petal.Width Species Max.Len lead.max.len
          <dbl>       <dbl>        <dbl>       <dbl> <fct>     <dbl>        <dbl>
 1          5.1         3.5          1.4         0.2 setosa      5.1          4.9
 2          4.9         3            1.4         0.2 setosa      4.9          4.7
 3          4.7         3.2          1.3         0.2 setosa      4.7          4.6
 4          4.6         3.1          1.5         0.2 setosa      4.6          5  
 5          5           3.6          1.4         0.2 setosa      5            5.4
 6          5.4         3.9          1.7         0.4 setosa      5.4          4.6
 7          4.6         3.4          1.4         0.3 setosa      4.6          5  
 8          5           3.4          1.5         0.2 setosa      5            4.4
 9          4.4         2.9          1.4         0.2 setosa      4.4          4.9
10          4.9         3.1          1.5         0.1 setosa      4.9          5.4

Answer 7

Something like this?

iris$Max.Len <- pmax(iris$Sepal.Length, iris$Petal.Length)

Answer 8

Just for completeness I am going to change the code of this user from the forgotten answer (and maybe the best answer) of the question: Sum across multiple columns. And apply it to your problem:

iris %>%
  mutate(max = select(.,c('Sepal.Length','Petal.Length')) %>% 
  apply(1, max, na.rm=TRUE))

The Result is expected. Accepted answer said that rowwise is increasingly not recommended, and apply is base R. Uou don't need to import an extra package like purrr.

You can use apply() function with max, min, sum, median, mean. So it's very handy and simple.