Make a column based on the difference of 2 objects in a seperate column in R

Question

I have the Data Frame:

Team_num <- c("One","Two","Three", "One", "Two", "One", "Two", "Three")
Date <- c("08/09/2019","08/09/2019","08/09/2019","08/10/2019","08/10/2019","08/11/2019","08/11/2019","08/11/2019")
Score <- c(12,15,68,59,63,47,45,89)

df <- data.frame(Team_num, Date, Score)

Which produces:

 Team_num       Date Score
1      One 08/09/2019    12
2      Two 08/09/2019    15
3    Three 08/09/2019    68
4      One 08/10/2019    59
5      Two 08/10/2019    63
6      One 08/11/2019    47
7      Two 08/11/2019    45
8    Three 08/11/2019    89

I would like to create a new column based on the difference in each team's score, based on their previous recorded score.

Not every team has a score for each date, so it is important to base it off the previous recorded score.

the resulting df should look like this:

  Team_num       Date Score Difference
1      One 08/09/2019    12        N/A
2      Two 08/09/2019    15        N/A
3    Three 08/09/2019    68        N/A
4      One 08/10/2019    59         47
5      Two 08/10/2019    63         48
6      One 08/11/2019    47        -12
7      Two 08/11/2019    45        -18
8    Three 08/11/2019    89         21

score 1 · Answer 1 · answered Feb 09 '20 at 21:48

A base R solution using ave

df <- within(df,Difference <- ave(Score,Team_num,FUN = function(v) c(NA,diff(v))))

such that

> df
  Team_num       Date Score Difference
1      One 08/09/2019    12         NA
2      Two 08/09/2019    15         NA
3    Three 08/09/2019    68         NA
4      One 08/10/2019    59         47
5      Two 08/10/2019    63         48
6      One 08/11/2019    47        -12
7      Two 08/11/2019    45        -18
8    Three 08/11/2019    89         21

score 0 · Accepted Answer · answered Feb 09 '20 at 21:44

After grouping by 'Team_num', we can take the difference between the current 'Score' with the lag of 'Score'

library(dplyr)
df %>%
    group_by(Team_num) %>% 
    mutate(Difference = Score - lag(Score)) 
    # or use diff
    # mutate(Difference = c(NA, diff(Score)))
# A tibble: 8 x 4
# Groups:   Team_num [3]
# Team_num Date       Score Difference
#  <fct>    <fct>      <dbl>      <dbl>
#1 One      08/09/2019    12         NA
#2 Two      08/09/2019    15         NA
#3 Three    08/09/2019    68         NA
#4 One      08/10/2019    59         47
#5 Two      08/10/2019    63         48
#6 One      08/11/2019    47        -12
#7 Two      08/11/2019    45        -18
#8 Three    08/11/2019    89         21

Make a column based on the difference of 2 objects in a seperate column in R

2 Answers2