> Haven't quite learned to 'cast' yet, but I have always used the 'apply' > functions for this type of processing: > > > x <- "id patient_id date code class eala > + ID1564262 1562 6.4.200612:00 5555 1 NA > + ID1564262 1562 6.4.200612:00 5555 1 NA > + ID1564264 1365 14.2.200614:35 5555 1 50 > + ID1564265 1342 7.4.200614:30 2222 2 50 > + ID1564266 1648 7.4.200614:30 2222 2 50 > + ID1564267 1263 10.2.200615:45 2222 2 10 > + ID1564267 1263 10.2.200615:45 3333 3 10 > + ID1564269 5646 13.5.200617:02 3333 3 10 > + ID1564270 7561 13.5.200617:02 6666 1 10 > + ID1564271 1676 15.5.200620:41 2222 2 20" > > > > x.in <- read.table(textConnection(x), header=TRUE) > > # 'by' seems to drop NAs so convert to a character string for processing > > x.in$eala <- ifelse(is.na(x.in$eala), "NA", as.character(x.in$eala)) > > # convert date to POSIXlt so we can access the year and month > > myDate <- strptime(x.in$date, "%d.%m.%Y%H:%M") > > x.in$year <- myDate$year + 1900 > > x.in$month <- myDate$mon+1
To do this with the reshape package, all you need is: > x.in$patient_id <- factor(x.in$patient_id) > dfm <- melt(x.in, id=c("code", "month", "year"), m=c("id","patient_id")) > cast(dfm, code + month + year ~ variable, stats) code month year id_n id_uniikit patient_id_n patient_id_uniikit 1 2222 2 2006 1 1 1 1 2 2222 4 2006 2 2 2 2 3 2222 5 2006 1 1 1 1 4 3333 2 2006 1 1 1 1 5 3333 5 2006 1 1 1 1 6 5555 2 2006 1 1 1 1 7 5555 4 2006 2 1 2 1 8 6666 5 2006 1 1 1 1 Which looks like what you want from your R code, but not your SQL. Hadley ______________________________________________ R-help@stat.math.ethz.ch 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.