When I saw this, I thought of pivoting the data to be longer, find the cumulative differences within ID, and then pivoting back to wide as shown below:
library(tidyverse)
df_orig <- tribble(
~Id, ~Total, ~P1, ~P2, ~P3, ~P4, ~P5,
1, 10, 1, 3, 2, 0, 0,
2, 7, 2, 0, 0, 0, 0,
3, 6, 0, 0, 0, 0, 0,
4, 4, 0, 0, 1, 0, 0,
5, 3, 1, 1, 0, 0, 1
)
df_long <- df_orig %>%
pivot_longer(-Id) %>%
mutate(
value2=case_when(
name=="Total"~value, #first value is positive
TRUE~-value #rest are negative to use cumsum
)
) %>%
group_by(Id) %>% #startsum over for each Id
mutate(
value3=cumsum(value2) #value3 is answer
) %>%
ungroup()
df_long
#> # A tibble: 30 x 5
#> Id name value value2 value3
#> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 1 Total 10 10 10
#> 2 1 P1 1 -1 9
#> 3 1 P2 3 -3 6
#> 4 1 P3 2 -2 4
#> 5 1 P4 0 0 4
#> 6 1 P5 0 0 4
#> 7 2 Total 7 7 7
#> 8 2 P1 2 -2 5
#> 9 2 P2 0 0 5
#> 10 2 P3 0 0 5
#> # ... with 20 more rows
df_cumdiffs <- df_long %>%
filter(name!="Total") %>%
pivot_wider(id_cols=Id, names_from="name", values_from="value3")
df_cumdiffs
#> # A tibble: 5 x 6
#> Id P1 P2 P3 P4 P5
#> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 1 9 6 4 4 4
#> 2 2 5 5 5 5 5
#> 3 3 6 6 6 6 6
#> 4 4 4 4 3 3 3
#> 5 5 2 1 1 1 0
Created on 2021-05-30 by the reprex package (v2.0.0)