Cumulative sum based on Subject ID in R

Assume we have dataframe df, it looks like this:

I'm trying to fill out the football_total column, for purposes of this example, let's assume that the formula is football_year_baseline * 2 in the baseline rows.

For the follow-up rows, the result needs to be cumulative based, meaning that the formula is football_total from baseline + 2. subjectid and event should be used to determine which baseline value to add 2 to.

Please note: not all subjects have a follow-up row.

So, football_row in row 2 would be 8 -> 6 + 2.

This should work for 1 or more rows of follow-up. It assumes the "baseline" is already the first row in each subject - if not, arrange() the data first.

library(dplyr)
df |>
  mutate(
    fball_total = case_when(event == "baseline" ~ football_year_baseline * 2, TRUE ~ NA_integer_),
    fball_total = coalesce(fball_total, fball_total[1] + 2 * (row_number() - 1)),
    .by = subjectid
  )
#   subjectid    event football_year_baseline football_total fball_total
# 1         1 baseline                      3              6           6
# 2         1 followup                     NA             NA           8
# 3         2 baseline                      0              0           0
# 4         2 followup                     NA             NA           2
# 5         3 baseline                      2              4           4

Since you mention there is at most one single follow up for each study id, an alternative dplyr solution using lag:

library(dplyr)

x %>% 
  mutate(football_total = case_when(
    event == "baseline" ~ football_total,
    event == "followup" ~ (lag(football_total) + 2)
  ), .by = subjectid)

Output:

  subjectid    event football_year_baseline football_total
1         1 baseline                      3              6
2         1 followup                     NA              8
3         2 baseline                      0              0
4         2 followup                     NA              2
5         3 baseline                      2              4

Data

x <- read.table(text = "subjectid	event	football_year_baseline	football_total
1	baseline	3	6
1	followup	NA	NA
2	baseline	0	0
2	followup	NA	NA
3	baseline	2	4", h = TRUE)

Extended example

To do this across multiple columns, assuming the same nomenclature convention (i.e., "xxx_total"), you can use dplyr's across() and contains(). Below I added two columns, vball_baseline and vball_total:

x %>% 
  mutate(across(contains("total"), ~ case_when(
    event == "baseline" ~ .x,
    event == "followup" ~ (lag(.x) + 2)
  )), .by = subjectid)

Extended Output

  subjectid    event football_year_baseline football_total vball_baseline vball_total
1         1 baseline                      3              6              1           2
2         1 followup                     NA              8             NA           4
3         2 baseline                      0              0              3           4
4         2 followup                     NA              2             NA           6
5         3 baseline                      2              4              5           6

Extended Data:

x <- read.table(text = "subjectid   event   football_year_baseline  football_total vball_baseline vball_total
1   baseline    3   6 1 2
1   followup    NA  NA NA NA
2   baseline    0   0 3 4
2   followup    NA  NA NA NA
3   baseline    2   4 5 6", h = TRUE)