Summarize the data per column and per row

Question

I have a sample data frame below. (1 means Yes, 2 means No)

Age Range        Ans
10 - 20           1 
21 - 30           2
31 - 40           1
10 - 20           2
21 - 30           2 
21 - 30           1
21 - 30           2

Desired Output :

Age_Range        Yes        No
10 - 20           1         1
21 - 30           1         3
31 - 40           1         0

How can I do it in R?

score 6 · Answer 1 · answered Apr 29 '14 at 05:51

6

In base R, you're basically just asking for table:

table(dat$Age.Range, dat$Ans)
#           1 2
#   10 - 20 1 1
#   21 - 30 1 3
#   31 - 40 1 0

If you wanted to convert it to have your column names:

tab <- table(dat$Age.Range, dat$Ans)
data.frame(Age_Range=rownames(tab), Yes=tab[,"1"], No=tab[,"2"])
#         Age_Range Yes No
# 10 - 20   10 - 20   1  1
# 21 - 30   21 - 30   1  3
# 31 - 40   31 - 40   1  0

answered Apr 29 '14 at 05:51

josliber

43,891
12
98
133

1

+1. This would possibly work too: `setNames(as.data.frame.matrix(table(dat)), c("Yes", "No"))` – A5C1D2H2I1M1N2O1R2T1 May 03 '14 at 06:55

Gary Weissman · Accepted Answer · 2014-04-29T05:47:21.890

I like to use data.table for these tasks.

Make the data frame:

library(data.table)

df <- read.table(text='Age Range        Ans
10 - 20           1 
21 - 30           2
31 - 40           1
10 - 20           2
21 - 30           2 
21 - 30           1
21 - 30           2',row.names=NULL)

df <- as.data.table(df)

df[,`Age Range`:=paste(row.names,Age,Range)]

Try

df[,list(Yes=sum(Ans==1),No=sum(Ans==2)),by=`Age Range`]

#    Age Range Yes No
# 1:   10 - 20   1  1
# 2:   21 - 30   1  3
# 3:   31 - 40   1  0

Or try a dplyr approach:

library(dplyr)    

df %.% group_by(`Age Range`) %.% summarize(Yes=sum(Ans==1),No=sum(Ans==2))

#   Age Range Yes No
# 1   10 - 20   1  1
# 2   21 - 30   1  3
# 3   31 - 40   1  0

Needs `text=` for the table to be created. – jbaums Apr 29 '14 at 05:35 — jbaums, Apr 29 '14 at 05:35

Summarize the data per column and per row

2 Answers2