[R] aggregate empty row for pretty appearance also subtotal if possible
jim holtman
jholtman at gmail.com
Sat Oct 4 04:41:04 CEST 2008
You can try reshape:
> n <- 2000
> x <- data.frame(a=sample(letters[1:2],n,TRUE), b=sample(LETTERS[1:2],n,TRUE),
+ c=sample(month.abb[1:2], n, TRUE), d=sample(LETTERS[25:26], n,
TRUE), value=runif(n))
> str(x)
'data.frame': 2000 obs. of 5 variables:
$ a : Factor w/ 2 levels "a","b": 2 1 2 1 1 2 2 2 2 1 ...
$ b : Factor w/ 2 levels "A","B": 1 2 1 1 2 1 1 1 2 1 ...
$ c : Factor w/ 2 levels "Feb","Jan": 2 2 2 1 1 1 1 2 2 1 ...
$ d : Factor w/ 2 levels "Y","Z": 1 2 1 2 1 2 2 1 1 1 ...
$ value: num 0.416 0.470 0.581 0.500 0.067 ...
> require(reshape)
> x.m <- melt(x, measured="value")
Using a, b, c, d as id variables
> cast(x.m, a+b+c+d ~ ., fun.aggregate=length, margins=c('a', 'b', 'c'))
a b c d (all)
1 a A Feb Y 124
2 a A Feb Z 124
3 a A Jan Y 109
4 a A Jan Z 115
5 a B Feb Y 120
6 a B Feb Z 152
7 a B Jan Y 141
8 a B Jan Z 129
9 a (all) (all) (all) 1014
10 b A Feb Y 124
11 b A Feb Z 150
12 b A Jan Y 115
13 b A Jan Z 136
14 b B Feb Y 117
15 b B Feb Z 131
16 b B Jan Y 119
17 b B Jan Z 94
18 b (all) (all) (all) 986
19 (all) A (all) (all) 997
20 (all) B (all) (all) 1003
21 (all) (all) Feb (all) 1042
22 (all) (all) Jan (all) 958
>
On Fri, Oct 3, 2008 at 3:49 PM, Sharma, Dhruv <Dhruv.Sharma at penfed.org> wrote:
> Hi,
> By the way if there are many columns in the aggregate like x1,x2,x3 for
> example how would the subtotal code change?
> Is it possible to get subtotal for multiple columns by Group.1 e.g.
> below?
>
> Thanks
> Dhruv
>
>
> -----Original Message-----
> From: jim holtman [mailto:jholtman at gmail.com]
> Sent: Thursday, October 02, 2008 10:28 PM
> To: Sharma, Dhruv
> Cc: r-help at r-project.org
> Subject: Re: [R] aggregate empty row for pretty appearance also subtotal
> if possible
>
>
> Here is one way of doing it:
>
>> dat <- read.table(textConnection("Group1 Group2 x
> + A Y 1
> + B N 1
> + A Y 1
> + B N 420164904
> + A N 3"), header=TRUE, as.is=TRUE)
>> closeAllConnections()
>> d <- aggregate(dat$x, list(dat$Group1, dat$Group2), sum) # split on
>> Group.1, and add row with sum d.l <- lapply(split(d, d$Group.1),
>> function(.df){
> + rbind(.df, list('','',sum(.df$x)))
> + })
>> do.call(rbind, d.l)
> Group.1 Group.2 x
> A.1 A N 3
> A.3 A Y 2
> A.31 5
> B.2 B N 420164905
> B.21 420164905
>
>
> On Thu, Oct 2, 2008 at 5:46 PM, Sharma, Dhruv <Dhruv.Sharma at penfed.org>
> wrote:
>> Hi,
>> To pretty print aggregates by various dimensions I needed to add a
>> empty row in output of aggregate.
>>
>> For example.
>>
>> d<-(aggregate(data[,cbind("x")], by=list(data$group1,data$group2),
>> sum))
>>
>> Group.1 Group.2 x
>> 1 A N 3
>> 2 A Y 2
>> 3 B N 420164905
>>
>> Is there a way to add an empty row between group1 and group 2.
>> So that it looks like
>> Group.1 Group.2 x
>> 1 A N 3
>> 2 A Y 2
>> 3
>> 4 B N 420164905
>>
>>
>> I need to format a series of aggregates by multi dimensions and I
>> wanted to break the data by empty row between group 1 that people can
>> see some space.
>>
>> Also is there a way to add subtotals by group 1 into the mix :
>> Group.1 Group.2 x
>> 1 A N 3
>> 2 A Y 2
>> 3 5
>> 4 B N 420164905
>> 5 420164905
>>
>>
>> original data is something like:
>> data
>> Group1 Group2 x
>> A Y 1
>> B N 1
>> A Y 1
>> B N 420164904
>> A N 3
>>
>>
>> thanks
>> Dhruv
>>
>> [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help at r-project.org 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.
>>
>
>
>
> --
> Jim Holtman
> Cincinnati, OH
> +1 513 646 9390
>
> What is the problem that you are trying to solve?
>
--
Jim Holtman
Cincinnati, OH
+1 513 646 9390
What is the problem that you are trying to solve?
More information about the R-help
mailing list