frequency table with several variables in R

r aggregate frequency
user1043144 picture user1043144 · Aug 7, 2012 · Viewed 15.7k times · Source

I am trying to replicate a table often used in official statistics but no success so far. Given a dataframe like this one:

d1 <- data.frame( StudentID = c("x1", "x10", "x2", 
                          "x3", "x4", "x5", "x6", "x7", "x8", "x9"),
             StudentGender = c('F', 'M', 'F', 'M', 'F', 'M', 'F', 'M', 'M', 'M'),
             ExamenYear    = c('2007','2007','2007','2008','2008','2008','2008','2009','2009','2009'),
             Exam          = c('algebra', 'stats', 'bio', 'algebra', 'algebra', 'stats', 'stats', 'algebra', 'bio', 'bio'),
             participated  = c('no','yes','yes','yes','no','yes','yes','yes','yes','yes'),  
             passed      = c('no','yes','yes','yes','no','yes','yes','yes','no','yes'),
             stringsAsFactors = FALSE)

I would like to create a table showing PER YEAR , the number of all students (all) and those who are female, those who participated and those who passed. Please note "ofwhich" below refers to all students.

A table I have in mind would look like that:

cbind(All = table(d1$ExamenYear),
  participated      = table(d1$ExamenYear, d1$participated)[,2],
  ofwhichFemale     = table(d1$ExamenYear, d1$StudentGender)[,1],
  ofwhichpassed     = table(d1$ExamenYear, d1$passed)[,2])

I am sure there is a better way to this kind of thing in R.

Note: I have seen LaTex solutions, but I am not use this will work for me as I need to export the table in Excel .

Thanks in advance

Answer

Andy picture Andy · Aug 7, 2012

Using plyr:

require(plyr)
ddply(d1, .(ExamenYear), summarize,
      All=length(ExamenYear),
      participated=sum(participated=="yes"),
      ofwhichFemale=sum(StudentGender=="F"),
      ofWhichPassed=sum(passed=="yes"))

Which gives:

  ExamenYear All participated ofwhichFemale ofWhichPassed
1       2007   3            2             2             2
2       2008   4            3             2             3
3       2009   3            3             0             2

Related questions

How to sum a variable by group

I have a data frame with two columns. First column contains categories such as "First", "Second", "Third", and the second column has numbers that represent the number of times I saw the specific groups from "Category". For example: Category Frequency …

Read More
Mean per group in a data.frame

I have a data.frame and I need to calculate the mean per group (i.e. per Month, below). Name Month Rate1 Rate2 Aira 1 12 23 Aira 2 18 73 Aira 3 19 45 Ben 1 53 19 Ben 2 22 87 Ben 3 19 45 Cat 1 22 87 Cat 2 67 43 Cat 3 45 32 My desired output is like below, where the …

Read More
Count number of rows within each group

I have a dataframe and I would like to count the number of rows within each group. I reguarly use the aggregate function to sum data as follows: df2 <- aggregate(x ~ Year + Month, data = df1, sum) Now, I …

Read More