Calculate difference between rows in R based on a specifc row for each group

user_4060158 0
Viewed 227

Hi everyone, I have a dataframe with where each ID has multiple visits from 1-5. I am trying to calculate the difference of a score between each visit to visit 1. eg. (Score(Visit 5-score(Visit1) and so on). How do I achieve that in R ? Below is a sample dataset and result dataset

structure(list(ID = c("A", "A", "A", "A", "A", "B", "B", "B"), 
    Visit = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L), Score = c(16, 
        15, 13, 12, 12, 20, 19, 18)), class = "data.frame", row.names = c(NA, 
    -8L))

#>   ID Visit Score
#> 1  A     1    16
#> 2  A     2    15
#> 3  A     3    13
#> 4  A     4    12
#> 5  A     5    12
#> 6  B     1    20
#> 7  B     2    19
#> 8  B     3    18

Created on 2021-05-20 by the reprex package (v2.0.0)

Here is the expected output

enter image description here

difference dplyr data-manipulation dataframe r
2 Answers

Here's a solution using dplyr

library(dplyr)

df %>% 
  group_by(ID) %>% 
  mutate(Difference = ifelse(Visit == 1, NA, Score[Visit == 1] - Score))

# A tibble: 8 x 4
# Groups:   ID [2]
  ID    Visit Score Difference
  <chr> <int> <dbl>      <dbl>
1 A         1    16         NA
2 A         2    15          1
3 A         3    13          3
4 A         4    12          4
5 A         5    12          4
6 B         1    20         NA
7 B         2    19          1
8 B         3    18          2

Sample data

df <- data.frame(
  ID = c('A', 'A', 'A', 'A', 'A', 'B', 'B', 'B'),
  Visit = c(1:5, 1:3),
  Score = c(16,15,13,12,12,20,19,18)
)

Sidenote: next time I suggest you to post not images but a sample data using the dput() function on your dataframe

user_74654620

Solution with dplyr using first

data <- data.frame(
  ID = c(rep("A", 5), rep("B", 3)),
  Visit = c(1:5, 1:3),
  Score = c(16, 15, 13, 12, 12, 20, 19, 18))

library(dplyr)
data %>%
  group_by(ID) %>%
  arrange(Visit) %>%
  mutate(Difference = first(Score) - Score)
#> # A tibble: 8 x 4
#> # Groups:   ID [2]
#>   ID    Visit Score Difference
#>   <chr> <int> <dbl>      <dbl>
#> 1 A         1    16          0
#> 2 A         2    15          1
#> 3 A         3    13          3
#> 4 A         4    12          4
#> 5 A         5    12          4
#> 6 B         1    20          0
#> 7 B         2    19          1
#> 8 B         3    18          2

Created on 2021-05-20 by the reprex package (v2.0.0)

user_42096570
Related