18

Using example data like this:

example=data.frame(x=c(1,2,3,4,5,6,7,8), y=c(1,2,3,4,5,6,7,8), z=c(1,2,3,4,5,6,7,8))

which looks like this:

    x   y   z
1   1   1   1
2   2   2   2
3   3   3   3
4   4   4   4
5   5   5   5
6   6   6   6
7   7   7   7
8   8   8   8

I would like to shift all values in the z column upwards by two rows while the rest of the dataframe remains unchanged. The result should look like this:

    x   y   z
1   1   1   3
2   2   2   4
3   3   3   5
4   4   4   6
5   5   5   7
6   6   6   8
7   7   7   NA
8   8   8   NA

I only found ways to move the values of a column down, or a shifting of the whole dataframe.

Any ideas? Thanks!

zx8754
  • 52,746
  • 12
  • 114
  • 209
Anne
  • 377
  • 2
  • 4
  • 16
  • Possible duplicate of [Shifting a column down by one](http://stackoverflow.com/questions/25687727/shifting-a-column-down-by-one) – user2165 Apr 17 '16 at 07:30

5 Answers5

20

Your problem simplifies to:

  • Drop the first n elements in a vector
  • Pad n values of NA at the end

You can do this with a simple function:

shift <- function(x, n){
  c(x[-(seq(n))], rep(NA, n))
}

example$z <- shift(example$z, 2)

The result:

example
  x y  z
1 1 1  3
2 2 2  4
3 3 3  5
4 4 4  6
5 5 5  7
6 6 6  8
7 7 7 NA
8 8 8 NA
Andrie
  • 176,377
  • 47
  • 447
  • 496
  • 2
    Or `c(tail(x, -n), rep(NA, n))`. This question has a very bad smell of a duplicate... – David Arenburg Sep 23 '14 at 12:40
  • 3
    but what is the data.table solution! – rawr Sep 23 '14 at 12:42
  • @rawr, sharp as always :) – David Arenburg Sep 23 '14 at 12:43
  • 1
    @Andrie Thanks again for the help. I have been trying to utilize this to achieve a shift in the other direction (two NA values in row 1 and 2, followed by the initial series) but couldn't figure out how to do this. Can you help me out? – Anne Oct 13 '14 at 07:51
  • @Andrie could you modify to work for multiple columns, with different shifts. Say column 1 you wanted to remain original, but column 2 you wanted to shift up 1 position/row, then column 3 you want to shift up 2 positions... column 4 up 3 positions etc... – Johnny5ish Nov 04 '20 at 00:47
10
example = tibble(x=c(1,2,3,4,5,6,7,8), 
                   y=c(1,2,3,4,5,6,7,8), 
                   z=c(1,2,3,4,5,6,7,8))

example %>% mutate_at(c("z"), funs(lead), n = 2 )

# A tibble: 8 x 3
      x     y     z
  <dbl> <dbl> <dbl>
1  1.00  1.00  3.00
2  2.00  2.00  4.00
3  3.00  3.00  5.00
4  4.00  4.00  6.00
5  5.00  5.00  7.00
6  6.00  6.00  8.00
7  7.00  7.00 NA   
8  8.00  8.00 NA  
Nettle
  • 3,193
  • 2
  • 22
  • 26
  • How can this code be used to shift rows instead of columns ? – Abhishek Feb 08 '19 at 09:53
  • 1
    per `dplyr` documentation: `Warning message: funs() is deprecated as of dplyr 0.8.0....` i made the following slight-update, and it works fine: `example %>% mutate_at(c("z"), tibble::lst("z"=lead), n = 1)` – bshelt141 Apr 22 '21 at 13:31
2

I couldn't find a good duplicate, so here's another solution using length<-

shift2 <- function(x, n) `length<-`(tail(x, -n), length(x)) 
# or just shift2 <- function(x, n) c(tail(x, -n), rep(NA, n))
transform(example, z = shift2(z, 2))   
#   x y  z
# 1 1 1  3
# 2 2 2  4
# 3 3 3  5
# 4 4 4  6
# 5 5 5  7
# 6 6 6  8
# 7 7 7 NA
# 8 8 8 NA
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
0
## In case you want to shift your rows down by one step for only one column
`z <- example$'z'`

# Deleting the last z value in order to shift values 
# ahead by one period to realign with other rows

z_1 <-head(z,-1)

# We set the initial z value to zero

for (i in 1: (length(z)+1)) {
  if(i != 1){
    z[i] = Z_1[i-1]}
  else{
    z[i] = 0
  }
}
Z
# Append Z to dataframe
example$z <- z
  • This is essentially a less efficient version of the accepted answer, replacing the vectorized operation with a for loop. – Gregor Thomas Apr 22 '21 at 14:24
-2

This function in the 'useful' package moves columns in both direction: https://www.rdocumentation.org/packages/useful/versions/1.2.6/topics/shift.column

  • It is better to give an explanation or at least summarize an explanation directly into your answer than point to an URL without further help. – Léa Gris Aug 03 '19 at 14:12