[R] Reshape (pivot) question

[hadley wickham]

> 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