1

I am looking to perform a row subtraction, where I have a group of individuals and I want to subtract the more recent row from the row above it (like a rolling row subtraction). Does anyone know a simple way to do this?

The data would look something like this:

    Name Day variable.1
1   Bob   1       43.4
2   Bob   2       32.0
3   Bob   3       18.1
4   Bob   4       41.2
5   Bob   5       85.2
6  Jeff   1       17.4
7  Jeff   2       55.6
8  Jeff   3       58.7
9  Jeff   4       40.6
10 Jeff   5       77.3
11 Carl   1       52.9
12 Carl   2       71.7
13 Carl   3       84.3
14 Carl   4       54.8
15 Carl   5       69.7

For example, for Bob, I would like it to come out as:

    Name Day variable.1
1   Bob   1       NA
2   Bob   2       -11.4
3   Bob   3       -13.9
4   Bob   4       23.1
5   Bob   5       44

And then it would go to the next name and perform the same task.

Alex A.
  • 5,466
  • 4
  • 26
  • 56
user3585829
  • 945
  • 11
  • 24

3 Answers3

5

You could try

library(data.table)#v1.9.5+
setDT(df1)[,variable.1:=c(NA,diff(variable.1)) , Name]

Or using shift from the devel version of data.table (as suggested by @Jan Gorecki). Instructions to install are here

setDT(df1)[, variable.1 := variable.1- shift(variable.1), Name]
akrun
  • 874,273
  • 37
  • 540
  • 662
3

You can use the base ave() function. For example, if your data is in a data.frame named dd, 

dd$newcol <-ave(dd$variable.1, dd$Name, FUN=function(x) c(NA,diff(x)))
MrFlick
  • 195,160
  • 17
  • 277
  • 295
0

You can also try:

library(dplyr)
df %>% group_by(Name) %>% mutate(diff = variable.1-lag(variable.1))
Source: local data frame [15 x 4]
Groups: Name

   Name Day variable.1  diff
1   Bob   1       43.4    NA
2   Bob   2       32.0 -11.4
3   Bob   3       18.1 -13.9
4   Bob   4       41.2  23.1
5   Bob   5       85.2  44.0
6  Jeff   1       17.4    NA
7  Jeff   2       55.6  38.2
8  Jeff   3       58.7   3.1
9  Jeff   4       40.6 -18.1
10 Jeff   5       77.3  36.7
11 Carl   1       52.9    NA
12 Carl   2       71.7  18.8
13 Carl   3       84.3  12.6
14 Carl   4       54.8 -29.5
15 Carl   5       69.7  14.9
DatamineR
  • 10,428
  • 3
  • 25
  • 45