Grant Gillis
2010-Oct-28 16:42 UTC
[R] adding copies of rows toa data frame based upon start and end dates
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]]
Joshua Wiley
2010-Oct-28 19:31 UTC
[R] adding copies of rows toa data frame based upon start and end dates
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/