0

To make it simple, i would like a column C to be equal to the Lag of a column B, with the shift argument depending on the integers in the column A, so I want:

   A | B | C  
   0 | 5 | 5 
   2 | 6 | NA 
   3 | 7 | NA 
   2 | 8 | 6

I tried: 

library(dplyr)
library(Hmisc)
data <- mutate(data, 
    C= Lag(B, shift=as.integer(A)),

but it doesn't work, i get NAs only, it's probably a type issue but i'm not sure as even with the as.integer it doesn't work, Does someone have any idea why it doesn't work?

Thanks a lot

MCmr
  • 11
  • 2

2 Answers2

0

lag from dplyr also has the shift argument, so no need to load another package. One way to do it would be,

library(dplyr)
df$C <- diag(sapply(df$A, function(i) lag(df$B, i)))
df
#  A B  C
#1 0 5  5
#2 2 6 NA
#3 3 7 NA
#4 2 8  6
Sotos
  • 51,121
  • 6
  • 32
  • 66
  • I get the error "n must be a single positive integer", even when i add as.integer before i – MCmr Oct 05 '16 at 10:06
  • do you have any negative integers in `df$A`? Can you share the `str(data)`? – Sotos Oct 05 '16 at 10:06
  • Those are all num, but yes i have negative integers in df$A, with Lag (Hmisc) negative integers take the forward values instead of the values before! And zeros seem to be a problem too, when i dont have any negative values, but i have zeros, i get the error : Error in if (n == 0) return(x) : missing value where TRUE/FALSE needed – MCmr Oct 05 '16 at 10:15
  • How do you want to treat those? Would it be ok if you converted negatives to positives? (i.e. `diag(sapply(abs(df$A), function(i) lag(df$B, i)))` As for 0 it seems to be working on the example you shared. – Sotos Oct 05 '16 at 10:20
  • I need to take the forward values so I cant convert from negative to positive, maybe i need to find something else than lag – MCmr Oct 05 '16 at 12:26
  • I mean take the values from the rows that are after and not before – MCmr Oct 05 '16 at 14:42
  • I found a solution: i did df$C <- apply(as.data.frame(df$A), 1, function(i){ nth(df$B, i) }) And i changed my input so that i dont have negative values anymore – MCmr Oct 05 '16 at 14:42
0

Solution: The shift argument does not seem to take a column values as integers, so i did it in another way: 

df$C <- apply(as.data.frame(df$A), 1, function(i){ 
    nth(df$B, i) 
})

It takes the nth value of B, with n being in the A col

(Thanks a lot for helping me Sotos)

MCmr
  • 11
  • 2