Difference between each column

Hi,

I have a dataframe that you can find in below:

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

I want to take a difference each consecutive column. The result should be:

Id P1 P2 P3 P4 P5
1 9 6 4 4 4
2 5 5 5 5 5
3 6 6 6 6 6
4 4 4 3 3 3
5 2 1 1 1 0

Could you please help? I couldn't do with for loop...

Thank you!

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)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.