# Counts of binned data by group

I have continuous data $A$ and categorical data $O$. I need counts of $A$ in bins by group $O$. I'm working in R.

I know how to bin data (using cut2) and how to get the counts of $O$ (using aggregate or by or describe or summaryBy). I could also get what I need by running one of these functions with a subset (bin) of the data one by one, but is there a way to do this automatically in one command?

Update:
The output I want is just basic descriptive statistics (counts in this case, but also means and/or medians for example):

Age (A)................Outcome (O)
........................Yes....No
18-27 ...................10....12
28-37 ....................2....11
38-47 ....................9.....7


For example:

by(subset(X, X$Age>47)$Age, subset(X, X$Age>47)$Outcome, length)


gives me the number of data points for ages > 47 where Outcome is true and where O is false. I want to do this for all bins automatically, without having to run separate commands with long > and < conditions for each bin.

• Could you show a reproducible example of what you can do, and a mock-up of what would you like to achieve? You are mentioning very powerful functions (e.g. summaryBy), and most likely you can do what you want with them, but it is hard to understand what you are missing. – Aniko Mar 17 '11 at 12:33
• @Aniko: I've updated the question with example output. – SabreWolfy Mar 17 '11 at 12:41

I really like using the summary.formula function in the Hmisc package for these tasks.

Some artificial data:

A <- rnorm(100, mean=50, sd=20)
G <- gl(2, 50)
B <- unclass(G)*10 + rnorm(100, sd=3)


Descriptives by G:

summary( G ~ cut2(A,g=4) + B, method="reverse")

Descriptive Statistics by G
+------------------------------+-----------------------------+-----------------------------+
|                              |1                            |2                            |
|                              |(N=50)                       |(N=50)                       |
+------------------------------+-----------------------------+-----------------------------+
|cut2(A, g = 4) : [ 5.19, 43.0)|           22% (11)          |           28% (14)          |
+------------------------------+-----------------------------+-----------------------------+
|    [42.98, 49.7)             |           18% ( 9)          |           32% (16)          |
+------------------------------+-----------------------------+-----------------------------+
|    [49.68, 60.3)             |           26% (13)          |           24% (12)          |
+------------------------------+-----------------------------+-----------------------------+
|    [60.27,107.5]             |           34% (17)          |           16% ( 8)          |
+------------------------------+-----------------------------+-----------------------------+
|B                             | 7.849173/10.153238/11.737293|18.766779/20.155744/22.139419|
+------------------------------+-----------------------------+-----------------------------+


Summarizing B by G and ranges of A with a custom function:

summary(B ~ cut2(A, g=4) + stratify(G), fun=function(x)c(mean(x),sd(x)))
B by g    N=100

+--------------+-------------+--+---------+--------+--+--------+--------+
|              |             |N |         |B       |N |        |B       |
+--------------+-------------+--+---------+--------+--+--------+--------+
|cut2(A, g = 4)|[ 5.19, 43.0)|11|10.351295|3.069181|14|20.55056|3.659311|
|              |[42.98, 49.7)| 9|10.269449|2.460152|16|19.18630|1.628746|
|              |[49.68, 60.3)|13| 9.432093|2.928439|12|21.07909|3.064897|
|              |[60.27,107.5]|17| 9.683748|2.800625| 8|20.65612|2.685183|
+--------------+-------------+--+---------+--------+--+--------+--------+
|Overall       |             |50| 9.870604|2.777788|50|20.25773|2.844987|
+--------------+-------------+--+---------+--------+--+--------+--------+


The documentation describes lots of options to control what is calculated and how the output looks like.

• @Aniko: Thanks. I'm working through your answer now. The first g in the first formula must be G. Why do you have a third data set (B), if A is your continuous and G is your factor/categorical? – SabreWolfy Mar 17 '11 at 13:29
• I fixed the problem with g - it should have been G. I used B because I wanted to have a third variable inside the table for the second example. Also, I kept cut-ting A to make rows of it to mirror your table. – Aniko Mar 17 '11 at 15:54
• @Aniko: Thanks. The first part of the answer (after I excluded B) is returning what I need. I'm having difficulty getting the mean/media/sd instead of the counts. I don't know how to use the second part of the answer without B. – SabreWolfy Mar 18 '11 at 12:46
• Do you want the mean/median of A by bins of A? Then just use A where I used B. I thought that means/medians got to imply the presence of a third variable. – Aniko Mar 18 '11 at 13:07
• @Aniko: Yes, I want means, etc. of A by bins of A by outcome. I replaced B with A but did not get the output I need. I've done it again and this time I think it is giving me what I need. Thanks. – SabreWolfy Mar 18 '11 at 14:02

library(Hmisc)
library(doBy)
set.seed(123)

A=rnorm(100, mean=50, sd=20)

bin.data<-data.frame(bins.A=cut2(A, g=4),G=gl(2,50), A)

summaryBy(A~bins.A+G,data=bin.data,FUN=each(length,mean,median))


will give output for age:

        bins.A G A.length   A.mean A.median
1 [ 3.82,40.2) 1       14 29.44193 29.06172
2 [ 3.82,40.2) 2       11 29.41250 29.62849
3 [40.18,51.4) 1       12 44.04858 43.98966
4 [40.18,51.4) 2       13 45.93984 45.48458
5 [51.41,64.0) 1       11 57.00405 58.01543
6 [51.41,64.0) 2       14 57.24880 57.11421
7 [64.03,93.7] 1       13 74.35300 74.15924
8 [64.03,93.7] 2       12 77.01414 75.09460


for each combination of the bin factor and the grouping factor.

• @Jim: I'm not sure that this gives me what I need. I want continuous variable A (age) in BINS by categorical G (see example in my question). Your solution groups A by G. – SabreWolfy Mar 18 '11 at 12:15
• @Jim: Thanks. The updated version of your answer gives me what I need. – SabreWolfy Mar 22 '11 at 8:45
• @Jim: Please could you indicate how I could get a $\chi^2$ p-value for each of the two outcomes per bin? I need to know if there is a difference in the counts for each of the two outcomes in each bin. (I've asked Aniko as well, as the two solutions are different.) – SabreWolfy Mar 22 '11 at 9:07
• @Jim Finally, the Hmisc library is not needed here?... – chl Mar 22 '11 at 9:24
• @chl. Sorry for being late in responding. The function cut2 is in the Hmisc library. – Jim M. Mar 28 '11 at 3:40

As an alternative to the Hmisc package, you can use table or xtabs:

table(cut(X$Age, c(0, 27, 37, 47, 999), X$Outcome)
xtabs(~ cut(Age, c(0, 27, 37, 47, 999) + Outcome, data=X)

• Got table to work using cut2. Couldn't get xtabs to work. Thanks for the ideas. – SabreWolfy Mar 31 '11 at 13:35