Hi,
May be this helps:
dat1 <- structure(list(ID_CASE = c("CS00000026A",
"CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A", "CS00000191C",
"CS00000191C", "CS00000191C",
"CS00000191C", "CS00000191C", "CS00000191C",
"CS00000191C", "CS00000191C",
"CS00000191C"), Month = c(201301L, 201302L, 201303L, 201304L,
201305L, 201306L, 201307L, 201308L, 201309L, 201310L, 201302L,
201303L, 201304L, 201305L, 201306L, 201307L, 201308L, 201309L,
201310L)), .Names = c("ID_CASE", "Month"), class =
"data.frame", row.names = c(NA,
-19L))
##expected output ##check the numbers (?typo)
##corrected version
res <- structure(list(ID_CASE = c("CS00000026A",
"CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A",
"CS00000026A", "CS00000026A", "CS00000191C",
"CS00000191C", "CS00000191C",
"CS00000191C", "CS00000191C", "CS00000191C",
"CS00000191C", "CS00000191C",
"CS00000191C", "CS00000191C"), Month = c(201301L, 201302L,
201303L,
201304L, 201305L, 201306L, 201307L, 201308L, 201309L, 201310L,
201301L, 201302L, 201303L, 201304L, 201305L, 201306L, 201307L,
201308L, 201309L, 201310L), Lag_1 = c(NA, 201301L, 201302L, 201303L,
201304L, 201305L, 201306L, 201307L, 201308L, 201309L, NA, 201301L,
201302L, 201303L, 201304L, 201305L, 201306L, 201307L, 201308L,
201309L), Lag_2 = c(NA, NA, 201301L, 201302L, 201303L, 201304L,
201305L, 201306L, 201307L, 201308L, NA, NA, 201301L, 201302L,
201303L, 201304L, 201305L, 201306L, 201307L, 201308L), Lag_3 = c(NA,
NA, NA, 201301L, 201302L, 201303L, 201304L, 201305L, 201306L,
201307L, NA, NA, NA, 201301L, 201302L, 201303L, 201304L, 201305L,
201306L, 201307L)), .Names = c("ID_CASE", "Month",
"Lag_1", "Lag_2",
"Lag_3"), class = "data.frame", row.names = c(NA, -20L))
res1 <- do.call(rbind, lapply(split(dat1, dat1$ID_CASE), function(x) {
??? Mo <- unique(dat1$Month)
??? m1 <- matrix(NA, nrow = length(Mo), ncol = 3)
??? d1 <- setNames(as.data.frame(sapply(1:ncol(m1), function(i) {
??????? m1[-seq(i), i] <- head(Mo, -i)
??????? m1[, i]
??? })), paste("Lag", 1:3, sep = "_"))
??? cbind(ID_CASE = unique(x$ID_CASE), Month = Mo, d1)
}))
row.names(res1) <- 1:nrow(res1)
res1$ID_CASE <- as.character(res1$ID_CASE)
all.equal(res, res1)
# [1] TRUE
##If you have datetime,
as.numeric(Sys.time())
A.K.
On Monday, May 12, 2014 6:18 AM, Abhinaba Roy <abhinabaroy09 at gmail.com>
wrote:
Hi R helpers,
I have a dataframe as,
? ID_CASE? Month
CS00000026A 201301
CS00000026A 201302
CS00000026A 201303
CS00000026A 201304
CS00000026A 201305
CS00000026A 201306
CS00000026A 201307
CS00000026A 201308
CS00000026A 201309
CS00000026A 201310
CS00000191C 201302
CS00000191C 201303
CS00000191C 201304
CS00000191C 201305
CS00000191C 201306
CS00000191C 201307
CS00000191C 201308
CS00000191C 201309
CS00000191C 201310
I want the output as,
? ID_CASE? Month? Lag_1? Lag_2? Lag_3
CS00000026A 201301? NA? ? ? NA? ? ? NA
CS00000026A 201302? 201301? NA? ? ? NA
CS00000026A 201303? 201202? 201201? NA
CS00000026A 201304? 201203? 201202? 201201
CS00000026A 201305? 201204? 201203? 201202
CS00000026A 201306? 201305? 201304? 201303
CS00000026A 201307? 201306? 201305? 201304
CS00000026A 201308? 201307? 201306? 201305
CS00000026A 201309? 201308? 201307? 201306
CS00000026A 201310? 201309? 201308? 201307CS00000191C 201301? NA? ? ? NA? ? NA
CS00000191C 201302? 201301? NA? ? NA
CS00000191C 201303? 201302? 201301? NA
CS00000191C 201304? 201303? 201302? 201301
CS00000191C 201305? 201304? 201303? 201302
CS00000191C 201306? 201305? 201304? 201303
CS00000191C 201307? 201306? 201305? 201304
CS00000191C 201308? 201307? 201306? 201305
CS00000191C 201309? 201308? 201307? 201306
CS00000191C 201310? 201309? 201308? 201307
where
? - Lag_1 is lagged by 1 Month
? - Lag_2 is lagged by 2 Months
? - Lag_3 is lagged by 3 Months.
I have used the following code to atleast get Lag_1
df <- ddply(df,.(ID_CASE),transform, Lag_1 <- c(NA,Month[-nrow(df)]))
But this does not give me the desired output for Lag_1.
And how can this be done if I have a *date-time* object instead of an
*int*column 'Month' as in the current example?
Any help on this will be appreciated.
--
Regards
Abhinaba Roy
Statistician
??? [[alternative HTML version deleted]]
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.