CODEWITHSUNDEEP
rdplyr
user3206440
user3206440

Reputation: 5069

dataframe - accessing the previous row

I have a dataframe df like below - which has the agg points gained / lost scored over several months.

name month       agg_points
A    2017-04-01   1
B    2017-04-01   3
C    2017-04-01   0
A    2017-05-01   2
B    2017-05-01   5
C    2017-05-01   2
A    2017-06-01   4
B    2017-06-01   5
C    2017-06-01   1

I need to find the net points gained / lost on a per month basis - this would mean subtracting the previous month's points from current month's point. How do I go about accessing the previous month's points in df ?

expected output

name month       net_points
A    2017-04-01   1
B    2017-04-01   3
C    2017-04-01   0
A    2017-05-01   1
B    2017-05-01   2
C    2017-05-01   2
A    2017-06-01   2
B    2017-06-01   0
C    2017-06-01   -1

Upvotes: 1

Views: 60

Answers (4)

denis
denis

Reputation: 5673

I have the data.table equivalent of markdly answer :

library(data.table)
DT <- setDT(df)
setkey(DT,month)
x <- DT[,  list(netpoint = diff(agg_points), month = .SD[-1,month]),by = name]

x is the data_table with the differences value. We then merge x and DT

DT <- x[DT, on = .(name,month)][,c("name","month","agg_points","netpoint")]

and add the first values of netpoint (equal to agg_points)

DT[,netpoint :={netpoint[1]<-agg_points[1]; netpoint},by=name]

Which gives

   name      month agg_points netpoint
1:    A 2017-04-01          1        1
2:    B 2017-04-01          3        3
3:    C 2017-04-01          0        0
4:    A 2017-05-01          2        1
5:    B 2017-05-01          5        2
6:    C 2017-05-01          2        2
7:    A 2017-06-01          4        2
8:    B 2017-06-01          5        0
9:    C 2017-06-01          1       -1

A closer way to markdly answer would be :

DT <- setDT(df)
setkey(DT,month)
DT[,netpoint := agg_points - c(NA, agg_points[-.N]), by = name]

But I still need to do 

DT[,netpoint :={netpoint[1]<-agg_points[1]; netpoint},by=name]

To fill the first rows, which desappoint me. Anyone have a better way ?

Upvotes: 0

Glaud
Glaud

Reputation: 733

You can create new temporary variable lag and use X3 - lag to get net_points.

library(readr)
df <- read_csv(
  "A,2017-04-01,1
  B,2017-04-01,3
  C,2017-04-01,0
  A,2017-05-01,2
  B,2017-05-01,5
  C,2017-05-01,2
  A,2017-06-01,4
  B,2017-06-01,5
  C,2017-06-01,1",
  col_names = F
)
str(df)
library(dplyr)
df %>% group_by(X1) %>% mutate(lag =  lag(X3), diff = ifelse(!is.na(lag), X3 - lag, X3)) %>%
  select(-lag)

gives

     X1         X2    X3  diff
  <chr>     <date> <int> <int>
1     A 2017-04-01     1     1
2     B 2017-04-01     3     3
3     C 2017-04-01     0     0
4     A 2017-05-01     2     1
5     B 2017-05-01     5     2
6     C 2017-05-01     2     2
7     A 2017-06-01     4     2
8     B 2017-06-01     5     0
9     C 2017-06-01     1    -1

Upvotes: 1

Ernest A
Ernest A

Reputation: 7839

One way:

with(df, {
    x <- xtabs(agg_points ~ month + name)
    x[-1, ] <- diff(x)
    as.data.frame(x, responseName = 'net_points')
})
#       month name net_points
#1 2017-04-01    A          1
#2 2017-05-01    A          1
#3 2017-06-01    A          2
#4 2017-04-01    B          3
#5 2017-05-01    B          2
#6 2017-06-01    B          0
#7 2017-04-01    C          0
#8 2017-05-01    C          2
#9 2017-06-01    C         -1

Upvotes: 1

markdly
markdly

Reputation: 4544

With dplyr, you could use the lag function after you group and arrange the rows appropriately: 

library(dplyr)
df %>%
  group_by(name) %>% 
  arrange(month, .by_group = TRUE) %>%
  mutate(net_points = agg_points - lag(agg_points, default = 0)) %>%
  arrange(month)

#> # A tibble: 9 x 4
#> # Groups:   name [3]
#>    name      month agg_points net_points
#>   <chr>      <chr>      <int>      <int>
#> 1     A 2017-04-01          1          1
#> 2     B 2017-04-01          3          3
#> 3     C 2017-04-01          0          0
#> 4     A 2017-05-01          2          1
#> 5     B 2017-05-01          5          2
#> 6     C 2017-05-01          2          2
#> 7     A 2017-06-01          4          2
#> 8     B 2017-06-01          5          0
#> 9     C 2017-06-01          1         -1

Data 

df <- read.table(text = "name month       agg_points
A    2017-04-01   1
B    2017-04-01   3
C    2017-04-01   0
A    2017-05-01   2
B    2017-05-01   5
C    2017-05-01   2
A    2017-06-01   4
B    2017-06-01   5
C    2017-06-01   1", header = TRUE, stringsAsFactors = FALSE)

Upvotes: 3

Related Questions