[R] adding copies of rows toa data frame based upon start and end dates

[Joshua Wiley]

Dear Grant,

This is far from the prettiest solution, but:

## Your sample data
tmp <- structure(list(ID = 1001:1007, GRID = c(1L, 1L, 1L, 1L, 1L, 1L,
1L), FOOD = c(0L, 0L, 0L, 0L, 0L, 0L, 0L), WB1 = c(319L, 659L,
325L, 322L, 654L, 301L, 349L), WB2 = c(999L, 671L, 662L, 655L,
899L, 651L, 669L), S = c("F", "M", "F", "F", "M", "M", "F"),
    A = c("A", "A", "A", "A", "A", "A", "A"), DRUG = c("I", "C",
    "I", "C", "I", "C", "I"), FREQ = c(1482L, 1408L, 769L, 1561L,
    1288L, 1593L, 1521L), DATESTART = c("4/17/91", "4/17/91",
    "4/17/91", "4/18/91", "4/18/91", "4/18/91", "4/18/91"),
    DATECOLLAR = c("4/17/91", "4/17/91", "4/17/91", "4/18/91", "4/18/91",
    "4/18/91", "4/18/91"), DATEEND = c("6/4/91", "6/25/91", "6/4/93",
    "5/27/91", "5/27/91", "7/11/91", "11/2/91"), DAYSONAIR = c(48, 69, 779,
    39, 39, 84, 198)), .Names = c("ID", "GRID", "FOOD", "WB1",
"WB2", "S", "A", "DRUG", "FREQ", "DATESTART", "DATECOLLAR", "DATEEND",
"DAYSONAIR"), class = "data.frame", row.names = c(NA, -7L))

## Convert from character to date class
tmp$DATESTART <- as.Date(tmp$DATESTART, "%m/%d/%y")
tmp$DATECOLLAR <- as.Date(tmp$DATECOLLAR, "%m/%d/%y")
tmp$DATEEND <- as.Date(tmp$DATEEND, "%m/%d/%y")

## Repeat each id DAYSONAIR + 1 times, and create DATE for each row
tmp.long <- data.frame(tmp[rep(1:nrow(tmp), tmp[ , "DAYSONAIR"]+1), ],
  DATE = as.Date(unlist(lapply(1:nrow(tmp), function(x) {
    tmp[x, "DATESTART"] + 0:tmp[x, "DAYSONAIR"]})),
    origin = "1970-01-01"))

## show structure of long version
str(tmp.long)


HTH,


Josh

On Thu, Oct 28, 2010 at 9:42 AM, Grant Gillis <grant.j.gillis at gmail.com> wrote:
> Hello All and thanks in advance for any advice.
>
> I have a data frame with rows corresponding radio-collared animals (see
> sample data below).  There is a start date (DATESTART), and end date
> (DATEEND), and the number of days on air (DAYSONAIR).  What I would like to
> do is add a column called DATE to so that each row ID has a row for every
> day that the radio collar was on the air while copying all other data.  For
> example ID 1001 would expand into 48 rows beginning with 4/17/91 and ending
> with 6/4/91.  all other values would remain the same for each new
> rowUnfortunately I have not gotten anywhere with my attempts
>
>
> Thank you!!
>
>
>
>
> ID    GRID    FOOD    WB1    WB2    S    A    DRUG    FREQ    DATESTART
> DATECOLLAR    DATEEND    DAYSONAIR
> 1001    1    0    319    999    F    A    I    1482    4/17/91
> 4/17/91        6/4/91    48.00
> 1002    1    0    659    671    M    A    C    1408    4/17/91
> 4/17/91        6/25/91    69.00
> 1003    1    0    325    662    F    A    I    0769    4/17/91
> 4/17/91        6/4/93    779.00
> 1004    1    0    322    655    F    A    C    1561    4/18/91
> 4/18/91        5/27/91    39.00
> 93    1005    1    0    654    899    M    A    I    1288    4/18/91
> 4/18/91        5/27/91    39.00
> 94    1006    1    0    301    651    M    A    C    1593    4/18/91
> 4/18/91        7/11/91    84.00
> 95    1007    1    0    349    669    F    A    I    1521    4/18/91
> 4/18/91        11/2/91    198.00
>
>        [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>



-- 
Joshua Wiley
Ph.D. Student, Health Psychology
University of California, Los Angeles
http://www.joshuawiley.com/