R: Calculate difference between values in rows with group reference

user_15152059 0
Viewed 694

This is my df:

group value
1     10
1     20
1     25
2     5
2     10
2     15

I now want to compute differences between each value of a group and a reference value, which is the first row of a group. More precisely:

group value diff
1     10    NA # because this is the reference for group 1
1     20    10 # value[2] - value[1]
1     25    15 # value[3] - value[1]
2     5     NA # because this is the reference for group 2
2     10    5  # value[5] - value[4]
2     15    10 # value[6] - value[4]

I found good answers for difference scores of the previous line (e.g., lag-function in dpylr, shift-function in data.table). However, I am looking for a fixed reference point and I couldn't make it work.

r
3 Answers

Try the code below

transform(
    df,
    Diff = ave(value, group, FUN = function(x) c(NA, diff(x)))
)

which gives

  group value Diff
1     1    10   NA
2     1    20   10
3     1    25    5
4     2     5   NA
5     2    10    5
6     2    15    5
user_121587570

I think you can also use this:

library(dplyr)
df %>%
  group_by(group) %>%
  mutate(diff = value - value[1], 
         diff = replace(diff, row_number() == 1, NA))

# A tibble: 6 x 3
# Groups:   group [2]
  group value  diff
  <int> <int> <int>
1     1    10    NA
2     1    20    10
3     1    25    15
4     2     5    NA
5     2    10     5
6     2    15    10
user_143145200 
df <-
  structure(list(
    group = c(1L, 1L, 1L, 2L, 2L, 2L),
    value = c(10L,
              20L, 25L, 5L, 10L, 15L)
  ),
  class = "data.frame",
  row.names = c(NA,
                -6L))

library(tidyverse)
df %>% 
  group_by(group) %>% 
  mutate(DIFF = ifelse(row_number() == 1, NA, value - first(value))) %>% 
  ungroup()
#> # A tibble: 6 x 3
#>   group value  DIFF
#>   <int> <int> <int>
#> 1     1    10    NA
#> 2     1    20    10
#> 3     1    25    15
#> 4     2     5    NA
#> 5     2    10     5
#> 6     2    15    10

Created on 2021-06-18 by the reprex package (v2.0.0)

user_120254830
Related