[R] aggregate empty row for pretty appearance also subtotal if possible

[jim holtman]

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?