Get difference between rows for all columns in R

user_12165341 0
Viewed 69

This is the input:

 book1 <- structure(
      list(
        ID = 1:10, 
        X1 = c(123L, 434L, 545L, 2323L, 54L,
               23L, 54L, 24L, 53L, 44L),
        X2 = c(132L, 44L, 543L, 335L, 53L,
               33L, 42L, 24L, 35L, 55L),
        X3 = c(12L, 143L, 533L, 344L, 232L,
               5332L, 23L, 333L, 56L, 75L),
        X4 = c(123L, 434L, 545L, 2323L,
               54L, 23L, 54L, 24L, 53L, 44L),
        X5 = c(132L, 44L, 543L, 335L,
               53L, 33L, 42L, 24L, 35L, 55L),
        X6 = c(12L, 143L, 533L, 344L,
               232L, 5332L, 23L, 333L, 56L, 75L),
        X7 = c(123L, 434L, 545L, 2323L,
               54L, 23L, 54L, 24L, 53L, 44L),
        X8 = c(132L, 44L, 543L, 335L,
               53L, 33L, 42L, 24L, 35L, 55L),
        X9 = c(12L, 143L, 533L, 344L,
               232L, 5332L, 23L, 333L, 56L, 75L),
        X10 = c(123L, 434L, 545L,
                2323L, 54L, 23L, 54L, 24L, 53L, 44L),
        X11 = c(132L, 44L, 543L,
                335L, 53L, 33L, 42L, 24L, 35L, 55L),
        X12 = c(12L, 143L, 533L,
                344L, 232L, 5332L, 23L, 333L, 56L, 75L)
      ),
      class = "data.frame",
      row.names = c(NA,-10L)
    )

And this is the input I'm expecting: expected result

I think I should use for loop and the lag function, but can't get it to work. I want the first stays the same, and get the difference starting the second row.

dplyr r for-loop
3 Answers

We may use diff with rbind - diff get the column wise difference between adjacent elements with length 1 less than the original number of rows and rbind the first row

rbind(book1[1,], diff(as.matrix(book1)))

-output

      X1   X2    X3    X4   X5    X6    X7   X8    X9   X10  X11   X12
1    123  132    12   123  132    12   123  132    12   123  132    12
2    311  -88   131   311  -88   131   311  -88   131   311  -88   131
3    111  499   390   111  499   390   111  499   390   111  499   390
4   1778 -208  -189  1778 -208  -189  1778 -208  -189  1778 -208  -189
5  -2269 -282  -112 -2269 -282  -112 -2269 -282  -112 -2269 -282  -112
6    -31  -20  5100   -31  -20  5100   -31  -20  5100   -31  -20  5100
7     31    9 -5309    31    9 -5309    31    9 -5309    31    9 -5309
8    -30  -18   310   -30  -18   310   -30  -18   310   -30  -18   310
9     29   11  -277    29   11  -277    29   11  -277    29   11  -277
10    -9   20    19    -9   20    19    -9   20    19    -9   20    19

Or an option in tidyverse

library(dplyr)
book1 %>%
  mutate(across(everything(),  ~ coalesce(.x -lag(.x), .x)))
user_37322710

You may also use:

rbind(book1[1,], book1[-1,] - head(book1, -1))

      X1   X2    X3    X4   X5    X6    X7   X8    X9   X10  X11   X12
1    123  132    12   123  132    12   123  132    12   123  132    12
2    311  -88   131   311  -88   131   311  -88   131   311  -88   131
3    111  499   390   111  499   390   111  499   390   111  499   390
4   1778 -208  -189  1778 -208  -189  1778 -208  -189  1778 -208  -189
5  -2269 -282  -112 -2269 -282  -112 -2269 -282  -112 -2269 -282  -112
6    -31  -20  5100   -31  -20  5100   -31  -20  5100   -31  -20  5100
7     31    9 -5309    31    9 -5309    31    9 -5309    31    9 -5309
8    -30  -18   310   -30  -18   310   -30  -18   310   -30  -18   310
9     29   11  -277    29   11  -277    29   11  -277    29   11  -277
10    -9   20    19    -9   20    19    -9   20    19    -9   20    19
user_83802720

Update on OP request:

  book1 %>%
    mutate(id = row_number()) %>% 
    filter(id != 1) %>% 
    mutate(across(-id, ~ . - lag(., default = .[1]+.[1]*-1))) %>% 
    bind_rows(book1 %>% 
                filter(row_number()==1)) %>% 
    mutate(id = replace_na(id, 1)) %>% 
    arrange(id)

      X1   X2    X3    X4   X5    X6    X7   X8    X9   X10  X11   X12 id
1    123  132    12   123  132    12   123  132    12   123  132    12  1
2    434   44   143   434   44   143   434   44   143   434   44   143  2
3    111  499   390   111  499   390   111  499   390   111  499   390  3
4   1778 -208  -189  1778 -208  -189  1778 -208  -189  1778 -208  -189  4
5  -2269 -282  -112 -2269 -282  -112 -2269 -282  -112 -2269 -282  -112  5
6    -31  -20  5100   -31  -20  5100   -31  -20  5100   -31  -20  5100  6
7     31    9 -5309    31    9 -5309    31    9 -5309    31    9 -5309  7
8    -30  -18   310   -30  -18   310   -30  -18   310   -30  -18   310  8
9     29   11  -277    29   11  -277    29   11  -277    29   11  -277  9
10    -9   20    19    -9   20    19    -9   20    19    -9   20    19 10

Update: How to avoid the second across of my answer:

  1. provided by @akrun:
library(tidyr)
library(dplyr)

book1 %>%
  mutate(across(everything(), ~ replace_na(. - lag(.), first(.))))

book1 %>%
  mutate(across(everything(), ~ . - lag(., default = .[1]+.[1]*-1)))

First answer: Here is another dplyr way using lag:

library(dplyr)

book1 %>% 
  mutate(across(everything(), ~ . - lag(., default = .[1]*2)),
         across(everything(), ~ ifelse(row_number()==1, .*-1, .)))

      X1   X2    X3    X4   X5    X6    X7   X8    X9   X10  X11   X12
1    123  114   234   123  114   234   123  114   234   123  114   234
2    311  -88   131   311  -88   131   311  -88   131   311  -88   131
3    111  499   390   111  499   390   111  499   390   111  499   390
4   1778 -208  -189  1778 -208  -189  1778 -208  -189  1778 -208  -189
5  -2269 -282  -112 -2269 -282  -112 -2269 -282  -112 -2269 -282  -112
6    -31  -20  5100   -31  -20  5100   -31  -20  5100   -31  -20  5100
7     31    9 -5309    31    9 -5309    31    9 -5309    31    9 -5309
8    -30  -18   310   -30  -18   310   -30  -18   310   -30  -18   310
9     29   11  -277    29   11  -277    29   11  -277    29   11  -277
10    -9   20    19    -9   20    19    -9   20    19    -9   20    19
user_133216470
Related