Thanks guys! Jim got it right what I was looking for. I think that the
reason why I don't get this right with cast-function is that I don't know
how to write a proper stats-function. SQL is still much easier for me.

Lauri

2007/2/20, hadley wickham <h.wickham@gmail.com>:
>
> > 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
>

	[[alternative HTML version deleted]]

