The R Book

Expanding a Table into a Dataframe

For the purposes of model-fitting, we often want to expand a table of explanatory variables to create a dataframe with as many repeated rows as specified by a count. Here are the data:

count.table<-read.table("c:\\temp \\tabledata.txt",header=T)
attach(count.table)
names(count.table)
[1]  "count"  "sex"  "age"  "condition"

count.table
   count     sex     age     condition
1     12    male   young       healthy
2      7    male     old       healthy
3      9  female   young       healthy
4      8  female     old       healthy
5      6    male   young   parasitized
6      7    male     old   parasitized
7      8  female   young   parasitized
8      5  female     old   parasitized

The idea is to create a new dataframe with a separate row for each case. That is to say, we want 12 copies of the first row (for healthy young males), seven copies of the second row (for healthy old males), and so on. The trick is to use lapply to apply the repeat function rep to each variable in count.table such that each row is repeated by the number of times specified in the vector called count:

lapply(count.table,function(x)rep(x, count.table$count))

Then we convert this object from a list to a dataframe using as.data.frame like this:

dbtable<-as.data.frame(lapply(count.table,function(x) rep(x, count.table$count)))

To tidy up, we probably want to remove the redundant vector of counts:

dbtable<-dbtable[,-1]
dbtable sex age condition 1 male young healthy 2 male young healthy 3 male young healthy 4 male young healthy 5 male young healthy 6 male young healthy 7 male young healthy 8 male young ...