A simple way to repeat, replicate, or explode data.table rows in R

January 31, 2014 · R · data.table

Note: I found this technique on an old forum post somewhere a while back. Once I find the link I will add it to this post!

So, the first thing I will mention is that if you aren’t using data.table, then you need to switch to it now. When I first started with R, I was using data.frame because all the tutorials online were using it. I didn’t realize how slow it was until I started working on larger data sets. Bottom line, drop data.frame and start using data.table, if you haven’t already. Here’s a good introduction with working examples:

http://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.pdf

A lot of times you might work with aggregated data, meaning the numerical values are summarized by a set of factors. An example would be the number of drivers by their age group and gender, where age group and gender are the factors. A lot of analyses can be done with this type of data. In addition, you can further summarize down to individual factors by using sum/mean on the numerical columns.

However, there are times when you need to expand the data. One case would be to make a histogram for those aggregate numerical fields. There are quite a few posts online that show how to use bar charts (base and ggplot2) as a substitute for histograms, since the histogram functions automatically calculate bins. There are other posts that mention repeating individual rows using the value in a numerical column. The latter is what I will show here.

First thing you should do is load the data.table library. In your R console or code add the following.

library(data.table)

Once data.table loads, create a simple data.table with two columns that we will use as a test.

dt <- data.table(fact = c("a", "b", "c"), count = c(1, 2, 3))
dt

Output:

   fact count
1:    a     1
2:    b     2
3:    c     3

We have a factor column (fact) with three levels, and a count column. Now before we start expanding this data set, I want to point out one interesting aspect of data.table (I haven’t tried this on data.frame). Once you understand it you will undoubtedly find new uses for it!

Try selecting a specific row from the data.table.

dt[1]

Output:

   fact count
1:    a     1

Cool, we get the first row back. Now try something else.

dt[c(1,1)]

Output:

   fact count
1:    a     1
2:    a     1

You see what happened there? In the first example we selected the first row. In the second example, we used a vector of indices (in this case 1 and 1), so it returned the first row twice, nice! Lets try something else.

dt[c(1,2,2,3,3,3)]

Output:

   fact count
1:    a     1
2:    b     2
3:    b     2
4:    c     3
5:    c     3
6:    c     3

There we go! We just expanded the original three row data set into six rows. Row ‘a’ once, ‘b’ twice, ‘c’ three times. So you can see how a simple vector on a data.table can change the output. Great, now how do we do this for non-trivial data sets? We can’t do it manually so we need to do it in code.

Without going into too much detail, you basically need to generate a vector with indices repeated using the numerical column (in our case ‘count’). Here’s how you do it in code.

dt[rep(seq(1, nrow(dt)), dt$count)]

Output:

   fact count
1:    a     1
2:    b     2
3:    b     2
4:    c     3
5:    c     3
6:    c     3

The key parts of the code are the ‘rep’, ‘seq’, and ‘nrow’ functions. Try the following code.

rep(seq(1, nrow(dt)), dt$count)

Output:

[1] 1 2 2 3 3 3

This works by creating a sequence from 1 to 3, where ‘nrow(dt)’ returns the number of rows in data.table dt. The sequence is then repeated using the ‘count’ column, which is returned as a vector. Each value in the sequence is repeated by the number in the corresponding row of the ‘count’ column.

So that is one of the many ways you can repeat, replicate, or explode your data.table rows for further analysis!