CODEWITHSUNDEEP
raggregatesummarization
mindlessgreen
mindlessgreen

Reputation: 12112

Summary / Aggregate in R without dropping levels

I would like to summarise or aggregate tables without dropping empty levels. I wonder if anyone has any ideas on this?

As an example, Here is a data frame

df1<-data.frame(Method=c(rep("A",3),rep("B",2),rep("C",4)),
       Type=c("Fast","Fast","Medium","Fast","Slow","Fast","Medium","Slow","Slow"),
            Measure=c(1,1,2,1,3,1,1,2,2))

Two approaches using base and doBy package.

#base
aggregate(Measure~Method+Type,data=df1,FUN=length)

require(doBy)
summaryBy(Measure~Method+Type,data=df1,FUN=length)

They both give the same results sorted differently, but the issue is that I would like all combinations of Method and Type and missing measures inserted as NAs. Or all levels of both my factors must be maintained.

df1$Type
df1$Method

Maybe plyr has something, but I don't know how that works.

Upvotes: 3

Views: 2683

Answers (5)

Skaqqs
Skaqqs

Reputation: 4140

Update for 2021

I think this can be accomplished now with stats::aggregate() using drop = FALSE. No extra packages needed. The result is a regular ole dataframe where empty levels are NA.

aggregate(Measure ~ Method + Type, data = df1, FUN = length, drop = FALSE)

  Method   Type Measure
1      A   Fast       2
2      B   Fast       1
3      C   Fast       1
4      A Medium       1
5      B Medium      NA
6      C Medium       1
7      A   Slow      NA
8      B   Slow       1
9      C   Slow       2

Upvotes: 1

mindlessgreen
mindlessgreen

Reputation: 12112

Thanks for your answers. I think all of them works to give the result. But the comment by Mark Heckmann with this code

ddply(df1, .(Method, Type), summarise, Measure=length(Measure), .drop=F)

seems to give a nice clean output dataframe with good header and with minimal code. On the downside, it needs additional package.

Upvotes: 0

Gavin Simpson
Gavin Simpson

Reputation: 174823

You could try by() in base R. For example,

tab <- with(df1, by(df1, list(Method = Method, Type = Type), FUN = length))
Method: A
Type: Fast
[1] 2
------------------------------------------------------------ 
Method: B
Type: Fast
[1] 1
------------------------------------------------------------ 
Method: C
Type: Fast
[1] 1
------------------------------------------------------------ 
Method: A
Type: Medium
[1] 1
------------------------------------------------------------ 
Method: B
Type: Medium
[1] NA
------------------------------------------------------------ 
Method: C
Type: Medium
[1] 1
------------------------------------------------------------ 
Method: A
Type: Slow
[1] NA
------------------------------------------------------------ 
....

Note that that is just the print() method making it look complicated. If we unclass() tab, we see it is just a multi-way table in this instance:

R> unclass(tab)
      Type
Method Fast Medium Slow
     A    2      1   NA
     B    1     NA    1
     C    1      1    2
attr(,"call")
by.data.frame(data = df1, INDICES = list(Method = Method, Type = Type), 
    FUN = nrow)

and you can work with that as it is just an array (a matrix). And if you prefer this in the long format, you can easily unwind it:

nr <- nrow(tab)
ltab <- cbind.data.frame(Method = rep(rownames(tab), times = nr),
                         Type = rep(colnames(tab), each = nr),
                         Count = c(tab))
ltab

R> ltab
  Method   Type Count
1      A   Fast     2
2      B   Fast     1
3      C   Fast     1
4      A Medium     1
5      B Medium    NA
6      C Medium     1
7      A   Slow    NA
8      B   Slow     1
9      C   Slow     2

Upvotes: 1

Matthew Plourde
Matthew Plourde

Reputation: 44614

In base R, by will return a result for missing values.

result <- by(df1, INDICES=list(df1$Method, df1$Type), FUN=nrow)
cbind(expand.grid(attributes(result)$dimnames), as.vector(result))

#   Var1   Var2 as.vector(result)
# 1    A   Fast                 2
# 2    B   Fast                 1
# 3    C   Fast                 1
# 4    A Medium                 1
# 5    B Medium                NA
# 6    C Medium                 1
# 7    A   Slow                NA
# 8    B   Slow                 1
# 9    C   Slow                 2

Upvotes: 1

Sven Hohenstein
Sven Hohenstein

Reputation: 81693

Have a look at tapply:

with(df1, tapply(Measure, list(Method, Type), FUN = length))

#   Fast Medium Slow
# A    2      1   NA
# B    1     NA    1
# C    1      1    2

Upvotes: 4

Related Questions