R Summarize data with by group of three variables

Question

I would like to summarize by transplant experiment data to get a) total individuals and b) total individuals by sex for each location, substrate and replicate combination. I have provided a simplified dataset with two records for each site, substrate, and replicate combination. I know how to create a contingency table in R, but not sure how to create a table (dataframe) where I am summarizing the data for three variables.

Transplant.Test <- structure(list(Location = c("Kampinge", "Kampinge", "Kampinge", "Kampinge",
                                               "Kampinge", "Kampinge", "Kampinge", "Kampinge",
                                               "Kampinge", "Kampinge", "Kampinge", "Kampinge",
                                               "Kaseberga", "Kaseberga", "Kaseberga", 
                                               "Kaseberga", "Kaseberga", "Kaseberga", 
                                               "Kaseberga", "Kaseberga", "Kaseberga", 
                                               "Kaseberga", "Kaseberga", "Kaseberga"),
                                  Substrate = c("Kampinge", "Kampinge", "Kampinge", "Kampinge",
                                                "Kampinge", "Kampinge", "Kaseberga","Kaseberga",
                                                "Kaseberga", "Kaseberga", "Kaseberga",
                                                "Kaseberga", "Kampinge", "Kampinge",
                                                "Kampinge", "Kampinge", "Kampinge", "Kampinge",
                                                "Kaseberga", "Kaseberga", "Kaseberga",
                                                "Kaseberga", "Kaseberga", "Kaseberga"),
                                 Replicate = c(1L, 1L, 2L, 2L, 3L, 3L, 1L, 1L, 2L, 2L, 3L, 3L,
                                               1L, 1L, 2L, 2L, 3L, 3L, 1L, 1L, 2L, 2L, 3L, 3L), 
                                 Sex = c("m", "m", "m", "m", "m", "m", "m", "m", "m", "m", "m",
                                         "m", "m", "f", "f", "f", "m", "f", "f", "f", "f", "f",
                                         "m", "m")), 
                                .Names = c("Location", "Substrate", "Replicate", "Sex"), 
                                class = "data.frame", row.names = c(NA, -24L))

The results would be two datatables; Table A would have "Location", "Substrate", "Replicate" and "Total" and Table B would have "Location", "Subtrate", "Replicate", "Male" and "Female" as columns.

Table B would look like: enter image description here

Whereas, Table A would just have a total rather than "Male" and Female".

Can you post an example of your expected outcomes? The first one sounds like you just want `data.frame(table(Transplant.Test[1:3]))`, or am I misunderstanding the question? — A5C1D2H2I1M1N2O1R2T1, Jul 16 '14 at 12:56
Or are you looking for `ftable`? `ftable(Transplant.Test, row.vars=c("Location", "Substrate", "Replicate"), col.vars="Sex")` — A5C1D2H2I1M1N2O1R2T1, Jul 16 '14 at 13:01
Your first solution works fine, but then your second solution requires and extra step in order to get the columns for each sex. — Keith W. Larson, Jul 16 '14 at 13:14

score 2 · Accepted Answer · answered Jul 16 '14 at 13:16

You might be interested in dcast from "reshape2".

Try the following:

library(reshape2)
dcast(Transplant.Test, Location + Substrate + Replicate ~ "count", 
      value.var="Sex", fun.aggregate=length)
#     Location Substrate Replicate count
# 1   Kampinge  Kampinge         1     2
# 2   Kampinge  Kampinge         2     2
# 3   Kampinge  Kampinge         3     2
# 4   Kampinge Kaseberga         1     2
# 5   Kampinge Kaseberga         2     2
# 6   Kampinge Kaseberga         3     2
# 7  Kaseberga  Kampinge         1     2
# 8  Kaseberga  Kampinge         2     2
# 9  Kaseberga  Kampinge         3     2
# 10 Kaseberga Kaseberga         1     2
# 11 Kaseberga Kaseberga         2     2
# 12 Kaseberga Kaseberga         3     2

dcast(Transplant.Test, Location + Substrate + Replicate ~ Sex, 
      value.var="Sex", fun.aggregate=length)
#     Location Substrate Replicate f m
# 1   Kampinge  Kampinge         1 0 2
# 2   Kampinge  Kampinge         2 0 2
# 3   Kampinge  Kampinge         3 0 2
# 4   Kampinge Kaseberga         1 0 2
# 5   Kampinge Kaseberga         2 0 2
# 6   Kampinge Kaseberga         3 0 2
# 7  Kaseberga  Kampinge         1 1 1
# 8  Kaseberga  Kampinge         2 2 0
# 9  Kaseberga  Kampinge         3 1 1
# 10 Kaseberga Kaseberga         1 2 0
# 11 Kaseberga Kaseberga         2 2 0
# 12 Kaseberga Kaseberga         3 0 2

score 0 · Answer 2 · answered Jul 16 '14 at 13:56

require(plyr)

Table_A <- count(Transplant.Test, c('Location','Substrate', 'Replicate')) names(Table_A) <- c("Location", "Substrate", "Replicate", "Total")

Table_B <- count(Transplant.Test, c('Location','Substrate', 'Replicate', 'Sex')) names(Table_B) <- c("Location", "Substrate", "Replicate", "Sex", 'Total')

R Summarize data with by group of three variables

2 Answers2