Hi,
May be this helps:
dat <- read.table(text="ID YEAR_MONTH ATT_1 ATT_2
1 201301 Y 1
1 201302 Y 1
1 201302 N 0
1 201302 Y 0
1 201303 N 1
3 201301 N 1
3 201302 N 0
3 201302 Y 0
3 201302 Y 1
3 201303 Y
1",sep="",header=TRUE,stringsAsFactors=FALSE)
res <- read.table(text="ID YEAR_MONTH YEARMONTH_LAG1 ATT1_CHNG
ATT2_CHNG
1 201301 NA NA
NA
1 201302 201301 0 0
1 201303 201302 2
1
3 201301 NA NA
NA
3 201302 201301 0 0
3 201303 201302 1
1",sep="",header=TRUE)
dat$YEARMONTH_LAG1 <- with(dat, ave(YEAR_MONTH, ID, FUN = function(x) c(NA,
head(x,
-1))))
res1 <- unsplit(lapply(split(dat, list(dat$ID, dat$YEAR_MONTH), drop = FALSE),
function(x) {
within(x, {
ATT2_CHNG <- sum(ATT_2[-1] != ATT_2[-nrow(x)])
ATT1_CHNG <- sum(ATT_1[-1] != ATT_1[-nrow(x)])
})
}), list(dat$ID, dat$YEAR_MONTH))
res2 <- res1[!with(res1, ave(YEAR_MONTH, list(ID), FUN = function(x)
duplicated(x))),
c(1:2, 5:7)]
res2[, 4:5] <- lapply(res2[, 4:5], function(x) ave(x, res2$ID, FUN =
function(x) c(NA,
x[-length(x)])))
row.names(res2) <- 1:nrow(res2)
identical(res, res2)
#[1] TRUE
A.K.
On Thursday, May 15, 2014 11:31 AM, Abhinaba Roy <abhinabaro...@gmail.com>
wrote:
Hi R-helpers,
I have a dataframe as shown below
ID YEAR_MONTH ATT_1 ATT_2
1 201301 Y 1
1 201302 Y 1
1 201302 N 0
1 201302 Y 0
1 201303 N 1
3 201301 N 1
3 201302 N 0
3 201302 Y 0
3 201302 Y 1
3 201303 Y 1
The final dataframe I want should look like
ID YEAR_MONTH YEARMONTH_LAG1 ATT1_CHNG ATT2_CHNG
1 201301 NA NA
NA
1 201302 201301 0
0
1 201303 201302 2
1
3 201301 NA NA
NA
3 201302 201301 0
0
3 201303 201302 1
1
Note:
1. 'YEARMONTH_LAG1 ' is the previous month corresponding to the current
month. E.g., if YEAR_MONTH == 201301 then YEARMONTH_LAG1 = NA (as there is
no record for 201212, and there is none in my data as all starts from
201201). Similarly if YEAR_MONTH == 201302 then YEARMONTH_LAG1 = 201301.
2. ATT1_CHNG is the number of times the level has changed (i.e., from Y to
N and vice-verse) for 'ATT_1' in the previous month (i.e., in
YEARMONTH_LAG1)
3. ATT2_CHNG is the number of times the level has changed (i.e., from 0 to
1 and vice-verse) for ATT_2 in the previous month
How can this be done in R?
--
Regards
Abhinaba Roy
[[alternative HTML version deleted]]
______________________________________________
R-help@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.
______________________________________________
R-help@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.
