Message-ID: <BANLkTi=G8ck4MAgFW0iuM+RSO6OnRUo7VA@mail.gmail.com>
Date: 2011-06-03T18:08:05Z
From: Ethan Brown
Subject: VLOOKUP in R - tried everything.
In-Reply-To: <8A08D568-E460-411C-B5EE-30E23506BB29@gmail.com>
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 <pdalgd at 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 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.
>
> --
> Peter Dalgaard
> Center for Statistics, Copenhagen Business School
> Solbjerg Plads 3, 2000 Frederiksberg, Denmark
> Phone: (+45)38153501
> Email: pd.mes at cbs.dk ?Priv: PDalgd at gmail.com
>
> ______________________________________________
> 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.
>