mkapur mkapur - 4 months ago 9
R Question

repeat row data with categorical variable a varying number of times

I have a dataframe that looks like this:

DATE SURVEY SPECIES COUNT
07/12/16 13 Paka 1
07/12/16 14 Paka 1
07/12/16 15 Grouper 2
07/12/16 16 Grouper 3
07/12/16 18 Grouper 1
07/12/16 20 Lehi 3


Where 'Count' is the number of individuals observed for each given survey. I'd like to reshape the data so that there is a single row (record) for each individual: e.g.:

DATE SURVEY SPECIES
07/12/16 13 Paka
07/12/16 14 Paka
07/12/16 15 Grouper
07/12/16 15 Grouper
07/12/16 16 Grouper
07/12/16 16 Grouper
07/12/16 16 Grouper
07/12/16 18 Grouper
07/12/16 20 Lehi
07/12/16 20 Lehi
07/12/16 20 Lehi


With the data for each survey replicated as many times as needed. I considered using the melt() or summarise() functions but am stuck because the COUNT variable ranges anywhere from 1 - 10.

Thanks

Answer

Suppose you original data frame is dat, You can do the following to get your desired data frame new_dat:

new_dat <- dat[rep.int(1:nrow(dat), dat$COUNT), -4]
rownames(new_dat) <- 1:nrow(new_dat)

The key is the row indexing vector rep.int(1:nrow(dat), dat$COUNT), while the -4 only signals dropping the 4th column, i.e., column COUNT.


Test

x <- "DATE    SURVEY  SPECIES COUNT
      07/12/16    13  Paka    1
      07/12/16    14  Paka    1
      07/12/16    15  Grouper 2
      07/12/16    16  Grouper 3
      07/12/16    18  Grouper 1
      07/12/16    20  Lehi    3"

dat <- read.table(text = x, header = TRUE)

#DATE   SURVEY  SPECIES COUNT
#07/12/16   13  Paka    1
#07/12/16   14  Paka    1
#07/12/16   15  Grouper 2
#07/12/16   16  Grouper 3
#07/12/16   18  Grouper 1
#07/12/16   20  Lehi    3

new_dat <- dat[rep.int(1:nrow(dat), dat$COUNT), -4]
rownames(new_dat) <- 1:nrow(new_dat)

#        DATE SURVEY SPECIES
#1   07/12/16     13    Paka
#2   07/12/16     14    Paka
#3   07/12/16     15 Grouper
#4   07/12/16     15 Grouper
#5   07/12/16     16 Grouper
#6   07/12/16     16 Grouper
#7   07/12/16     16 Grouper
#8   07/12/16     18 Grouper
#9   07/12/16     20    Lehi
#10  07/12/16     20    Lehi
#11  07/12/16     20    Lehi
Comments