Hi,
Is there are function similar to excel vlookup in R. Please let me know.
Thanks,
Sachin
____________________________________________________________________________________
[[alternative HTML version deleted]]
I think that merge is what you want: set.seed(24032008) x <- data.frame(ID=sample(10), Value=rnorm(10)) idx <- sample(5) merge(idx, x, by.x=1, by.y=1) On 24/03/2008, Sachin J <sachinj.2006 at yahoo.com> wrote:> Hi, > > Is there are function similar to excel vlookup in R. Please let me know. > > Thanks, > Sachin > > > ____________________________________________________________________________________ > > > [[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. >-- Henrique Dallazuanna Curitiba-Paran?-Brasil 25? 25' 40" S 49? 16' 22" O
?findInterval On 3/24/08, Sachin J <sachinj.2006 at yahoo.com> wrote:> Hi, > > Is there are function similar to excel vlookup in R. Please let me know. > > Thanks, > Sachin > > > ____________________________________________________________________________________ > > > [[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. >-- Jim Holtman Cincinnati, OH +1 513 646 9390 What is the problem you are trying to solve?
Another way: If x is a two column matrix, as suggested by Henrique D., ID Value 1 7 0.000656733 2 6 0.201764789 3 1 0.671113391 4 10 -0.739727826 5 9 -1.111310154 6 5 -0.859455833 7 2 -1.408229877 8 8 0.993126295 9 3 -0.171906808 10 4 -0.140107677 And you are looking up the value corresponding to "ID" ID <- 4 x[(1:dim(x)[1])[x[,1]==ID],2] will also do it, and you can vary the value of the 2 in order to query the column of interest, much as you can do with vlookup in the E program. Ben -----Original Message----- From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Sachin J Sent: Monday, March 24, 2008 9:25 AM To: r-help at r-project.org Subject: [PS] [R] vlookup in R Hi, Is there are function similar to excel vlookup in R. Please let me know. Thanks, Sachin
Sachin J <sachinj.2006 at yahoo.com> wrote in news:897147.50760.qm at web37606.mail.mud.yahoo.com:> Is there are function similar to excel vlookup in R. Please let me > know. >Caveat: definition of VLOOKUP done from memory and by checking OO.o Calc function of same name. (Don't have Excel on this machine.) VLOOKUP looks up a single value in the first column of an Excel range and returns a column value (offset by a given integer) from the first matching row in that "range". The indexing functions ("extract" or "[" ) can be used:> df4V1 V2 V3 1 4.56 1 0.1 2 8.42 1 0.2 3 0.79 3 0.3 4 5.39 3 0.4 5 0.95 4 0.5 6 7.73 5 0.6 7 7.17 6 0.7 8 3.89 7 0.8 9 0.54 10 1.0 10 9.53 9 0.9> df4[df4$V1==0.79,2][1] 3 vlookup <- function(val, df, row){ df[df[1] == val, row][1] }> vlookup(0.79, df4, 2)[1] 3 I thought there was an optional 4th argument to VLOOKUP that specifies the action to be taken if there is no exact match. You may need to change the equality in that function to an inequality and identify the first column value that is less than or equal to "val". If I remember correctly, Excel assumes that the first column is ordered ascending. -- David Winsemius
Hi Henrique, This is what I am trying to accomplish: I want to read values in V1 and V2 and populate the column V4 with R or B based on the values in V3 i.e. if its A,B,C then its R else if D,F then B. You can assume V1, V2 are dataframe1 and V3, V4 are in dataframe2 (note V4 is empty initially i.e. its a new column).> df1V1V2 A R BR CR DB FB> df2V3V4 AR CR DB FB AR CR BR BR BR BR AR DB Thanks in advance for your help. ----- Original Message ---- From: Henrique Dallazuanna <wwwhsd@gmail.com> To: Sachin J <sachinj.2006@yahoo.com> Cc: r-help@r-project.org Sent: Monday, March 24, 2008 10:41:52 AM Subject: Re: [R] vlookup in R I think that merge is what you want: set.seed(24032008) x <- data.frame(ID=sample(10), Value=rnorm(10)) idx <- sample(5) merge(idx, x, by.x=1, by.y=1) On 24/03/2008, Sachin J <sachinj.2006@yahoo.com> wrote:> Hi, > > Is there are function similar to excel vlookup in R. Please let me know. > > Thanks, > Sachin > > > ____________________________________________________________________________________ > > > [[alternative HTML version deleted]] > > ______________________________________________ > 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. >-- Henrique Dallazuanna Curitiba-Paraná-Brasil 25° 25' 40" S 49° 16' 22" O ____________________________________________________________________________________ [[alternative HTML version deleted]]
Thank you all for your help. I will definately go through the suggested
document.
Thanks again.
Sachin
----- Original Message ----
From: Henrik Bengtsson <hb@stat.berkeley.edu>
To: Sachin J <sachinj.2006@yahoo.com>
Cc: Henrique Dallazuanna <wwwhsd@gmail.com>; r-help@r-project.org
Sent: Monday, March 24, 2008 12:43:42 PM
Subject: Re: [R] vlookup in R
# Setup the translation map
transMap <- c(A="R", B="R", C="R",
D="B", F="B");
# The input data
v3 <- c("A", "C", "D", "F",
"A", "C", "B", "B", "B",
"B", "A", "D");
# The translated data
v4 <- transMap[v3];
df2 <- data.frame(V3=v3, V4=v4);
I recommend you to read 'An Introduction to R' that comes with any R
installation. There is a lot of useful stuff in there; it takes many
reads to grasp parts of it but it is worth it if you're going to use R
for more than, say, two weeks. You can find it via help.start().
/Henrik
On Mon, Mar 24, 2008 at 8:25 AM, Sachin J <sachinj.2006@yahoo.com>
wrote:> Hi Henrique,
>
> This is what I am trying to accomplish:
> I want to read values in V1 and V2 and populate the column V4 with R or B
based on the
> values in V3 i.e. if its A,B,C then its R else if D,F then B. You can
assume V1, V2 are dataframe1
> and V3, V4 are in dataframe2 (note V4 is empty initially i.e. its a new
column).
>
> > df1
> V1V2
> A R
> BR
> CR
> DB
> FB
>
> > df2
>
> V3V4
> AR
> CR
> DB
> FB
> AR
> CR
> BR
> BR
> BR
> BR
> AR
> DB
>
>
>
> Thanks in advance for your help.
>
>
>
>
> ----- Original Message ----
> From: Henrique Dallazuanna <wwwhsd@gmail.com>
> To: Sachin J <sachinj.2006@yahoo.com>
> Cc: r-help@r-project.org
> Sent: Monday, March 24, 2008 10:41:52 AM
> Subject: Re: [R] vlookup in R
>
> I think that merge is what you want:
>
> set.seed(24032008)
> x <- data.frame(ID=sample(10), Value=rnorm(10))
> idx <- sample(5)
> merge(idx, x, by.x=1, by.y=1)
>
> On 24/03/2008, Sachin J <sachinj.2006@yahoo.com> wrote:
> > Hi,
>
> >
> > Is there are function similar to excel vlookup in R. Please let me
know.
> >
>
> > Thanks,
> > Sachin
> >
> >
> >
____________________________________________________________________________________
> >
> >
> > [[alternative HTML version deleted]]
> >
> > ______________________________________________
>
> > 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.
> >
>
>
>
> --
> Henrique Dallazuanna
> Curitiba-Paraná-Brasil
> 25° 25' 40" S 49° 16' 22" O
>
>
>
____________________________________________________________________________________
>
>
> [[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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.
>
>
____________________________________________________________________________________
[[alternative HTML version deleted]]