Emulate the window function of SQL in R

Question

I have a table as following:

id   name  amount  year
001  A     10      2010
001  A     10      2011
001  A     12      2012
-----------------------
002  A     3       2012
002  A     4       2013
-----------------------
003  B     20      2011
003  B     20      2012

(Note two entities have the same name A but they are different, id is the unique identifier.)

I want to compute the increment in amount over the previous year, i.e. the result should look like:

id   name   increment   year
001  A      0           2010
001  A      0           2011
001  A      2           2012
----------------------------
002  A      0           2012
002  A      1           2013
----------------------------
003  B      0           2011
003  B      0           2012

Note that the increment of the first year is considered "0".

---

In MSSQL, it can implemented by:

SELECT id,
       name,
       amount - LAG(amount, 1, amount) OVER (PARTITION BY id ORDER BY YEAR) as increment,
       year
FROM table

---

I am trying to accomplish the task in R with data.table. I found an succinct example here:
DT[, increment := amount - shift(amount, 1), by=id]. But error was prompted: could not find function "shift".

The versions are:

• R: 3.2.0_1
• data.table: 1.9.4

The questions are:

1. I found the shift function implemented on data.table's Github, why I failed to invoke the function?
2. I think that by in data.table is equivalent to PARTITION BY in SQL, then what is the counterpart of ORDER BY in R? Do I have to set the key of data.table before carrying out any aggregation so the data.table is ordered?

Answer 1

This case falls under a general structure of doing an operation on a column by a separate grouping column.

fun <- function(v) c(0, diff(v)) #to take the difference and account for the starting value

#function tapply()
df1 <- df
df1$amount <- unlist(with(df, by(amount, id, fun)))
df1
   id name amount year
1 001    A      0 2010
2 001    A      0 2011
3 001    A      2 2012
4 002    A      0 2012
5 002    A      1 2013
6 003    B      0 2011
7 003    B      0 2012

#using data.table
df2 <- df
setDT(df2)[, list(name, Increment = fun(amount), year), by = id]
    id name Increment year
1: 001    A         0 2010
2: 001    A         0 2011
3: 001    A         2 2012
4: 002    A         0 2012
5: 002    A         1 2013
6: 003    B         0 2011
7: 003    B         0 2012

#function: by()
df3 <- df
df3$amount <- unlist(with(df3, by(amount, id, fun)))
df3
   id name amount year
1 001    A      0 2010
2 001    A      0 2011
3 001    A      2 2012
4 002    A      0 2012
5 002    A      1 2013
6 003    B      0 2011
7 003    B      0 2012

#using dplyr with data.table
DT %>%
  group_by(id) %>%
  summarise(name, increment = fun(amount), year)
Source: local data table [7 x 4]

   id name increment year
1 001    A         0 2010
2 001    A         0 2011
3 001    A         2 2012
4 002    A         0 2012
5 002    A         1 2013
6 003    B         0 2011
7 003    B         0 2012

#using aggregate
df5$amount <- unlist(aggregate(amount ~ id, data=df5, FUN=fun)$amount)
df5
   id name amount year
1 001    A      0 2010
2 001    A      0 2011
3 001    A      2 2012
4 002    A      0 2012
5 002    A      1 2013
6 003    B      0 2011
7 003    B      0 2012

#function: ave
df6 <- df
df6$amount <- with(df, ave(amount, id, FUN-fun))
df6
   id name amount year
1 001    A      0 2010
2 001    A      0 2011
3 001    A      2 2012
4 002    A      0 2012
5 002    A      1 2013
6 003    B      0 2011
7 003    B      0 2012

#dplyr (non-data.table)
df7 <- df
df %>%
  group_by(id) %>%
  mutate(increment = fun(amount))
   id name amount year increment
1 001    A     10 2010         0
2 001    A     10 2011         0
3 001    A     12 2012         2
4 002    A      3 2012         0
5 002    A      4 2013         1
6 003    B     20 2011         0
7 003    B     20 2012         0

#dplyr (with extra command 'select' to give the desired output of the OP)
df %>%
   group_by(id) %>%
     mutate(increment = fun(amount)) %>%
       select(id, name, increment, year)
Source: local data frame [7 x 4]
Groups: id

   id name increment year
1 001    A         0 2010
2 001    A         0 2011
3 001    A         2 2012
4 002    A         0 2012
5 002    A         1 2013
6 003    B         0 2011
7 003    B         0 2012

Data

df <- data.frame(id=factor(c('001', '001', '001', '002', '002', '003', '003')), 
                 name=c(rep('A', 5), rep('B', 2)),
                 amount=c(10,10,12,3,4,20,20),
                 year=c(2010, 2011, 2012, 2012, 2013, 2011, 2012)
)