Even after I discovered match(), it took me a little while to figure out how to use it for this task, so to add on to Peter's comment--to add a column for total for each value of "coll.minus.release", try the following:
data$ParasitoidMatch <- data$ParasitoidTotal[match(data$coll.minus.release, data$release.days)] Note also that match() only returns the first match it finds, and by default returns NA for no match. On Fri, Jun 3, 2011 at 10:43 AM, peter dalgaard <pda...@gmail.com> wrote: > > On Jun 3, 2011, at 16:59 , bjmjarrett wrote: > >> I am attempting to emulate the VLOOKUP function from Excel in R. >> >> I want to compare one column (coll.minus.release) with another >> (release.days) to get the number of parasitoid released at that time >> (TotalParasitoids). >> >> for example: >> >> coll.minus.release release.days ParasitoidTotal >> -12 -266 1700 >> 8 -259 1000 >> 8 -225 1000 >> 28 -216 1000 >> 41 -28 1148 >> 77 -12 1144 >> 105 0 1160 >> 105 8 972 >> 125 28 1146 >> 125 41 1004 >> 125 77 1003 >> 125 97 1010 >> .... >> 2772 NA NA >> 2801 NA NA >> 2834 NA NA >> >> >> vlookup <- function(x) data[data$release.days==x,6] # as I have three other >> columns that are not of interest >> >> vlookup(-12) = 1144, and so on, which is great. >> >> However, when I try: >> >> unlist(sapply(coll.minus.release,vlookup)) to apply it to the whole >> coll.minus.release >> >> it works up to a point, as it doesn't give me 132 values for the 132 values >> of coll.minus.release. Is this because the table of release.days and >> TotalParasitoid has less values than coll.minus.release (108 compared to >> 132)? To fill the gap I put in 0, and as none of the coll.minus.release >> values = 0 I think it wouldn't affect it. > > > I wager that a look at setdiff(coll.minus.release,release.days) and vice > versa would be illuminating. Notice that with your definition, > vlookup(31415926) or any other number absent from release.days gives a > zero-length vector. > > Presumably, you are looking for match(). > > >> >> Other things I have tried include findInterval and match. >> >> data[findInterval(x=data$coll.minus.release,vec=data$release.days,"ParasitoidTotal")] >> >> didn't work as it said vec must be sorted non-decreasingly and didn't work >> when I randomised the release.days and ParasitoidTotal columns as it doesn't >> matter which order they are in. >> >> Thanks for reading all the way through - I wanted all the information I felt >> you might need to help me in it. >> >> Any help will be greatly appreciated. >> >> -- >> View this message in context: >> http://r.789695.n4.nabble.com/VLOOKUP-in-R-tried-everything-tp3571107p3571107.html >> Sent from the R help mailing list archive at Nabble.com. >> >> ______________________________________________ >> R-help@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. > > -- > Peter Dalgaard > Center for Statistics, Copenhagen Business School > Solbjerg Plads 3, 2000 Frederiksberg, Denmark > Phone: (+45)38153501 > Email: pd....@cbs.dk Priv: pda...@gmail.com > > ______________________________________________ > R-help@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. > ______________________________________________ R-help@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.
