R help - Jan 2012 - reshape dataframe to array (pivot table)

Hello,

I would like to reshape a dataframe into an array. This is kind a similar task
as Excel performs with a Pivot table. To illustrate it:

LOC <- factor(c(1,2,2,3,1,1))
SPEC1 <- c(0,0,23,0,12,11)
SPEC2 <- c(1,2,0,0,0,4)

df <- data.frame(LOC,SPEC1,SPEC2) # original dataframe

a <-
array(NA,dim=c(length(levels(LOC)),1,2),dimnames=list(levels(LOC),"LOC",c("SPEC1","SPEC2")))
#new array set up, how it should look like

The final array is splitted by the SPEC (for each SPEC an new layer), and
the LOC is collapsed so that there is only one line per level of LOC.
The array should get populated with:
1) the sums
2) and presence/absence (can be easily calculated from sums)

What is the best way to do that? I looked into reshape and apply...probably one
of them is suitable but I don't know how...Or has that to be done with a
loop?

cheers,
/johannes
-- 
"Feel free" - 10 GB Mailbox, 100 FreeSMS/Monat ...

Hi,

I wouldn't know how to fill the data into the array form you want, but 
you can get the aggregated data with

dfm <- melt( df )
dfc <- cast( dfm, LOC ~ variable, sum )
 > dfc
   LOC SPEC1 SPEC2
1   1    23     5
2   2    23     2
3   3     0     0

Hope this helps as a first step!

Rgds,
Rainer


On 2012-01-24 17:15, Johannes Radinger wrote:
> Hello,
>
> I would like to reshape a dataframe into an array. This is kind a similar
task as Excel performs with a Pivot table. To illustrate it:
>
> LOC<- factor(c(1,2,2,3,1,1))
> SPEC1<- c(0,0,23,0,12,11)
> SPEC2<- c(1,2,0,0,0,4)
>
> df<- data.frame(LOC,SPEC1,SPEC2) # original dataframe
>
> a<-
array(NA,dim=c(length(levels(LOC)),1,2),dimnames=list(levels(LOC),"LOC",c("SPEC1","SPEC2")))
#new array set up, how it should look like
>
> The final array is splitted by the SPEC (for each SPEC an new layer), and
> the LOC is collapsed so that there is only one line per level of LOC.
> The array should get populated with:
> 1) the sums
> 2) and presence/absence (can be easily calculated from sums)
>
> What is the best way to do that? I looked into reshape and apply...probably
one of them is suitable but I don't know how...Or has that to be done with a
loop?
>
> cheers,
> /johannes

Johannes Radinger wrote on 01/24/2012 10:15:29 AM:
> Hello,
> 
> I would like to reshape a dataframe into an array. This is kind a 
> similar task as Excel performs with a Pivot table. To illustrate it:
> 
> LOC <- factor(c(1,2,2,3,1,1))
> SPEC1 <- c(0,0,23,0,12,11)
> SPEC2 <- c(1,2,0,0,0,4)
> 
> df <- data.frame(LOC,SPEC1,SPEC2) # original dataframe
> 
> a <- array(NA,dim=c(length(levels(LOC)),1,2),dimnames=list(levels
> (LOC),"LOC",c("SPEC1","SPEC2"))) #new array
set up, how it should look
like
> 
> The final array is splitted by the SPEC (for each SPEC an new layer), 
and
> the LOC is collapsed so that there is only one line per level of LOC.
> The array should get populated with:
> 1) the sums
> 2) and presence/absence (can be easily calculated from sums)
> 
> What is the best way to do that? I looked into reshape and 
> apply...probably one of them is suitable but I don't know how...Or 
> has that to be done with a loop?
> 
> cheers,
> /johannes

Try this:

sums <- apply(df[, 2:3], 2, tapply, df$LOC, sum)
pres <- sums>0
combine <- abind(sums, pres, along=0)
aperm(combine, c(2, 1, 3))

, , SPEC1

  [,1] [,2]
1   23    1
2   23    1
3    0    0

, , SPEC2

  [,1] [,2]
1    5    1
2    2    1
3    0    0

Jean
	[[alternative HTML version deleted]]