1

I have the following dataframe:

xR <- data.frame("A" = c(15, 13.5, 12, 9.1, NA, NA, NA, NA), 
             "B" = c(NA, 13.6, 8.4, 6.7, 5.6, 2.0, NA, NA), 
             "C" = c(NA, NA, 8.5, 2.43, 1.23, NA, NA, NA))

How do I shift the rows in columns B and C up by 1 row to get:

xR1 <- data.frame("A" = c(15, 13.5, 12, 9.1, NA, NA, NA, NA), 
             "B" = c(13.6, 8.4, 6.7, 5.6, 2.0, NA, NA, NA), 
             "C" = c(NA, 8.5, 2.43, 1.23, NA, NA, NA, NA))

My dataframe has 100+ columns so trying to automate it. Thanks for your help

user1655130
  • 419
  • 1
  • 3
  • 13
  • 1
    Write a function that does the shifting `shift_1 <- function(x) c(x[-1], x[1])` and apply it to your data `xR[, cols] <- lapply(xR[, cols], shift_1)`, where `cols` is a character vector of column names, i.e. `cols <- c("B", "C")` – markus Nov 11 '20 at 15:15

4 Answers4

3

In base you can use [-1] to skip the first element and c with NA to add NA at the end:

data.frame(xR[1], lapply(xR[-1], function(x) c(x[-1], NA)))
#     A    B    C
#1 15.0 13.6   NA
#2 13.5  8.4 8.50
#3 12.0  6.7 2.43
#4  9.1  5.6 1.23
#5   NA  2.0   NA
#6   NA   NA   NA
#7   NA   NA   NA
#8   NA   NA   NA

or modification in place using the thoughts from @Ricardo:

xR[,-1] <- rbind(xR[-1,-1], NA)
xR
#     A    B    C
#1 15.0 13.6   NA
#2 13.5  8.4 8.50
#3 12.0  6.7 2.43
#4  9.1  5.6 1.23
#5   NA  2.0   NA
#6   NA   NA   NA
#7   NA   NA   NA
#8   NA   NA   NA

or only using sub-setting:

xR[,-1] <- xR[seq_len(nrow(xR)) + 1, -1]
GKi
  • 37,245
  • 2
  • 26
  • 48
  • Aside from this @GKi- is it possible to shift col B up 1 row, col C up 2 rows and so on? Thanks – user1655130 Nov 11 '20 at 16:01
  • 1
    Try: `xR[,-1] <- lapply(seq_len(ncol(xR)-1), function(i) c(xR[-seq_len(i),i+1], rep(NA, i)))` – GKi Nov 11 '20 at 16:10
  • Final question @GKi - is it possible to shift values in Col B so that it starts on same row of Col A where first value of Col B is lower than Col A? Hope this makes sense. If not, I'll create an example - thanks – user1655130 Nov 11 '20 at 16:14
  • Maybe? `i <- which.max(!is.na(xR$B)); i <- i-which.max(xR$B[i] < xR$A); if(i > 0) c(xR$B[-seq_len(i)], rep(NA, i)) else xR$B` – GKi Nov 11 '20 at 16:23
  • Thanks @GKi - this didn't work - I've asked it as a separate question as I think it helps explain it better - thanks again for your help – user1655130 Nov 11 '20 at 18:33
1

Using base R:

xR[,2:3] = rbind(xR[-1,2:3], NA)

Then you can change the 2:3 to all the columns that you want.

Ricardo Semião e Castro
  • 4,366
  • 1
  • 8
  • 27
0
library(tidyverse)
xR1 <- xR %>%
  mutate(B = lead(B),
         C = lead(C))

which gives:

     A    B    C
1 15.0 13.6   NA
2 13.5  8.4 8.50
3 12.0  6.7 2.43
4  9.1  5.6 1.23
5   NA  2.0   NA
6   NA   NA   NA
7   NA   NA   NA
8   NA   NA   NA

Automating across many columns (assuming you don't want to do the shifting for column A) is quite easy:

xR1 <- xR %>%
  mutate(across(-A, lead))
deschen
  • 10,012
  • 3
  • 27
  • 50
0

You can automate this across a wide selection of columns with

xR %>% 
  dplyr::mutate_at(.vars = vars(B:C),
                   .funs = ~ dplyr::lead(.x, 1))
henhesu
  • 756
  • 4
  • 9