Hi R-users, I have a data set like this (first ten rows):
id patient_id date code class eala ID1564262 1562 6.4.2006 12:00 5555 1 NA ID1564262 1562 6.4.2006 12:00 5555 1 NA ID1564264 1365 14.2.2006 14:35 5555 1 50 ID1564265 1342 7.4.2006 14:30 2222 2 50 ID1564266 1648 7.4.200614:30 2222 2 50 ID1564267 1263 10.2.2006 15:45 2222 2 10 ID1564267 1263 10.2.200615:45 3333 3 10 ID1564269 5646 13.5.2006 17:02 3333 3 10 ID1564270 7561 13.5.200617:02 6666 1 10 ID1564271 1676 15.5.2006 20:41 2222 2 20 How can I do a new (pivot?) data.frame in R which I can achieve by MS SQL: select eala, datepart(month, date) as month, datepart(year, date) as year, count(distinct id) as id_count, count(distinct patient_id) as patient_count, count(distinct(case when class = 1 then code else null end)) as count_1, count(distinct(case when class = 2 then code else null end)) as count_2, count(distinct(case when class = 3 then code else null end)) as count_3, into temp2 from temp1 group by datepart(month, date), datepart(year, date), eala order by datepart(month, date), datepart(year, date), eala I tried something like this but could not go further: stats <- function(x) { count <- function(x) length(na.omit(x)) c( n = count(x), uniikit = length(unique(x)) ) } library(reshape) attach(dframe) dfm <- melt(dframe, measure.var=c("id","patient_id"), id.var=c("code",""this should be month"",""this should be year), variable_name="variable") cast(dfm, code + month + year ~ variable, stats) Regards, Lauri [[alternative HTML version deleted]] ______________________________________________ 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.