Divide one column of table by another and insert into a new column

Question

Here is a small example of my input:

Term <- c("Fall 2010", "Fall 2010", "Fall 2011", "Fall 2011", "Fall 2011", "Fall 2011",       "Fall 2010", "Fall 2010", "Fall 2011", "Fall 2011", "Fall 2011", "Fall 2011")
College <- c("COE", "COBA", "COBA", "COLFA", "COE", "COBA", "COBA", "COBA", "COBA", "COBA", "COBA", "COLFA")
mydata <- data.frame(Term, College)
mydata

#Used the tables library to create a count of the occurrences.

require(tables)
tab<- tabular(Factor(College) ~ (Factor(Term)), data=mydata)
tab

I would like to calculate a percentage change from fall 2010 to fall 2011 for each row and put that in a column of the table. Any help on this would be much appreciated.

Do you absolutely have to use `tablular` from the `tables` package? If you were OK with using `table` from base you could just add the colums. — konvas, Jul 07 '14 at 20:37
I am using the tabular function to create tables that ultimately end up in a PDF report, so ultimately, I need to be able generate nice looking tables...Having said that, I am open to any suggestion to calculate the percent change over time. — user3799924, Jul 07 '14 at 20:47

score 5 · Answer 1 · answered Jul 07 '14 at 20:46

5

First, there is no reason to use tables package here. table(mydata$College, mydata$Term) from base R will give you the same result. The problem with both of these options is that it is very hard to manipulate their classes.

A better option will be using dcast from package reshape2

library(reshape2)
tab <- dcast(mydata, College ~ Term)
tab$Per_Change <- tab[, 3]/tab[, 2]
tab
##   College Fall 2010 Fall 2011 Per_Change
## 1    COBA         3         5   1.666667
## 2     COE         1         1   1.000000
## 3   COLFA         0         2        Inf

answered Jul 07 '14 at 20:46

David Arenburg

91,361
17
137
196

4

You can change a table to data.frame quite easily with `as.data.frame.matrix` and then calculate the % change. ie `as.data.frame.matrix(table(mydata$College, mydata$Term))` – user20650 Jul 07 '14 at 20:48
@user20650, yes you can, don't see why though. Why not just use `dcast`. You can post it as an answer though – David Arenburg Jul 07 '14 at 20:56
Of course David, your answer is obviously fine. It was just a minor comment to show how a `table` can be manipulated (certainly not an answer :)) – user20650 Jul 07 '14 at 21:00
Thanks for the suggestions. All of them achieve the desired result that I asked for. Now, I just have to figure out how to get this into a latex table ready for output. That is why I was using the tabular function earlier. :) – user3799924 Jul 08 '14 at 03:05
@user3799924, Check [here](http://stackoverflow.com/questions/5465314/tools-for-making-latex-tables-in-r). There are multiple way to do this with R, even easier if you use RStudio – David Arenburg Jul 08 '14 at 07:26

jenswirf · Answer 2 · 2014-07-07T21:18:35.693

You can also use dplyr

require(dplyr)
require(tidyr)

mydata = tbl_df(mydata)

mydata %.%
  mutate (Term = make.names(Term)) %.%
  group_by (Term, College) %.%
  summarise (n=n()) %.%
  spread (Term, n, fill=0) %.%
  mutate (delta = (Fall.2011-Fall.2010) / Fall.2010)

#  College Fall.2010 Fall.2011     delta
#     COBA         3         5 0.6666667
#      COE         1         1 0.0000000
#    COLFA         0         2       Inf

Divide one column of table by another and insert into a new column

2 Answers2