What is the equivalent of dplyr mutate and summarise in data table?

Question

In package dplyr, we have operations:

mtcars %>%
    group_by(cyl) %>%
    summarise(max_mpg = max(mpg)) # output one result for each unique group,
                                  # result has nGroups number of rows.

giving

    cyl max_mpg
  <dbl>   <dbl>
1     4    33.9
2     6    21.4
3     8    19.2

and

mtcars %>%
    group_by(cyl) %>%
    mutate(max_mpg = max(mpg)) # output the same result for every row in the                       
                               # same group, result has same number of rows
                               # as input

giving

Source: local data frame [32 x 12]
Groups: cyl [3]

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

What is the equivalent of these operations in data.table?

I think mutate is given by

data.table(mtcars) %>% 
    .[, max := max(mpg), by = cyl]

but I don't know how to get the equivalent of summarise. I can add that for whatever reason if you do not have := it does a summarise, e.g.:

data.table(mtcars) %>% .[, max(mpg), by = cyl]

gives

   cyl   V1
1:   6 21.4
2:   4 33.9
3:   8 19.2

but it is not obvious how to assign a name to the V1 column created.

The obvious question is why do `mtcars %>% data.table() %>%....` - why not just use `data.table` syntax alone instead of mixing various packages? — thelatemail, Jul 07 '16 at 01:55
I think about function operations with pipes these days, and I would like to use the `data.table` functionality with piping so it makes sense for me to lay it out like this,. — Alex, Jul 07 '16 at 02:15
These operations and more are covered in detail in [this answer](http://stackoverflow.com/a/27718317/2761575). I'd be inclined to put this as a duplicate. — dww, Jul 07 '16 at 02:17
I have had multiple looks at the linked question but I argue that it is very hard to find the answer to my question due to the sheer amount of information present in that post. @Sumedh's answer makes the distinction very clear. — Alex, Jul 07 '16 at 02:20
Note that there is a recent package [dtplyr](https://cloud.r-project.org/web/packages/dtplyr/index.html) which aims to offer just that: a `data.table` backend for `dplyr`. Me, I prefer the `data.table` syntax... — Dirk Eddelbuettel, Jul 07 '16 at 02:24
The mutate analogue `:=` does not really map. Data.table adds a column to the original table by reference for efficiency and to make it easier to keep track of what is where. Dplyr has the opposite philosophy -- whatever starts your chain should be immutable, with each step in the chain verb-ing a new object (where verb = group_by, mutate, etc.). Anyway, d(t)plyr takes advantage of data.table functionality already, as Dirk said... — Frank, Jul 07 '16 at 04:48
Read the [vignettes](https://github.com/Rdatatable/data.table/wiki/Getting-started). — Arun, Jul 07 '16 at 06:39

Sumedh · Answer 1 · 2016-07-07T02:55:40.993

12

library(data.table)
MT <- data.table(mtcars)

# summarise
MT[, .(max_mpg = max(mpg)), by = cyl]

   cyl max_mpg
1:   6    21.4
2:   4    33.9
3:   8    19.2

# mutate
MT[, max_mpg := max(mpg), by = cyl]

max_mpg is added to MT, but the data is not displayed with this command

To display the data:

MT[, max_mpg := max(mpg), by = cyl][]

Since the data has 32 rows, displaying just the head:

MT[, max_mpg := max(mpg), by = cyl][,head(.SD, 6)]

     mpg cyl disp  hp drat    wt  qsec vs am gear carb max_mpg
 1: 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4    21.4
 2: 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4    21.4
 3: 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1    33.9
 4: 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1    21.4
 5: 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2    19.2
 6: 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1    21.4

If you wish to sort by cyl: (Code suggested by @thelatemail)

MT[, .(max_mpg = max(mpg)), keyby=cyl]

   cyl max_mpg
1:   4    33.9
2:   6    21.4
3:   8    19.2

Edit

Adding this in response to @Alex's comment

data("mtcars")
setDT(mtcars)[, .(max_mpg = max(mpg)), by = cyl]

edited Jul 07 '16 at 02:55

answered Jul 07 '16 at 01:57

Sumedh

4,835
2
17
32

2

`MT[, .(max_mpg = max(mpg)), keyby=cyl]` is the preferred method for sorting by the `by=` value in the output I believe. – thelatemail Jul 07 '16 at 02:11
Oh, thanks. I wasn't aware of that. Just started reading about `data.table` ! – Sumedh Jul 07 '16 at 02:12
@thelatemail, updated the answer. Hope that's ok. – Sumedh Jul 07 '16 at 02:16
thanks, this is very interesting, so the only difference is the addition of `.()`. – Alex Jul 07 '16 at 02:17
yes! Also it's preferred to use `data.table`'s syntax as suggested by @thelatemail – Sumedh Jul 07 '16 at 02:19
why is it preferred? You need more lines of code in assigning the original table `MT`. – Alex Jul 07 '16 at 02:21
2

I am not sure if posting this is frowned upon, but I think this cheat sheet is very helpful: https://s3.amazonaws.com/assets.datacamp.com/img/blog/data+table+cheat+sheet.pdf – Sumedh Jul 07 '16 at 02:23
The cheat sheet has too much info to extract the relevant method for this question. – Alex Jul 07 '16 at 02:24

What is the equivalent of dplyr mutate and summarise in data table?

1 Answers1