[R] something like vlookup in R?

[Glen Barnett]

Your question is impossible to parse "I would like sort of " ... sort of what?

vlookup is an Excel function that returns the value in one column
based on a value in another column; by
default it finds the index of the largest value in the first column of
the table less than the target, and returns
the corresponding element of the specified column (default is the second column)

If you want to look up cdf values and return seq or rand values, then
it depends on how they're stored (e.g.
separate vectors vs a matrix or a data frame). You should post some
pasteable code that illustrates what you
want.

My initial example works if they're stored as separate vectors (I read
into a data frame but attach()'ed it so
I could refer to the columns as if they were individual vectores,
while my following function works on data frames.
There are other ways to do it.

mytable <- read.table(stdin(),header=TRUE)
cdf seq rand
0.00E+00 0 0.2621
1.56E-03 20 0.9643
1.55E-02 40 0.4948
5.30E-02 60 0.7337
1.16E-01 80 0.8004
1.97E-01 100 0.9257
2.88E-01 120 0.0476
3.80E-01 140 0.2661
4.68E-01 160 0.1255
5.48E-01 180 0.7012
6.18E-01 200 0.9158
6.79E-01 220 0.7360
7.32E-01 240 0.5178
7.76E-01 260 0.0421
8.13E-01 280 0.5683
8.44E-01 300 0.0671
8.70E-01 320 0.7144
8.92E-01 340 0.7122
9.10E-01 360 0.2885
9.26E-01 380 0.1859
9.38E-01 400 0.1089
9.49E-01 420 0.5134
9.57E-01 440 0.2221
9.65E-01 460 0.5004
9.71E-01 480 0.4378
9.76E-01 500 0.8518
9.80E-01 520 0.8035
9.83E-01 540 0.2498
9.86E-01 560 0.8592
9.88E-01 580 0.1269
9.90E-01 600 0.7134

attach(mytable)
> max(which(cdf<=0.5))
[1] 9
> seq[max(which(cdf<=0.5))]
[1] 160
> rand[max(which(cdf<=0.3))]
[1] 0.0476

In fact, we can readily define a function which probably does most of
what you want:

lookv <- function(x,ltab,rcol=2) ltab[max(which(ltab[,1]<=x)),rcol]
> lookv(0.5,mytable)
[1] 160
> lookv(0.3,mytable,3)
[1] 0.0476

Hope that helps

On Fri, May 28, 2010 at 11:13 AM, Roslina Zakaria <zroslina at yahoo.com> wrote:
> Hi r users,
>
> I would like sort of
>
> cdf         seq   rand
> 0.00E+00    0     0.262123478
> 1.56E-03    20    0.964293344
> 1.55E-02    40    0.494827113
> 5.30E-02    60    0.733726005
> 1.16E-01    80    0.800408948
> 1.97E-01    100   0.925748466
> 2.88E-01    120   0.047578356
> 3.80E-01    140   0.266060366
> 4.68E-01    160   0.125522629
> 5.48E-01    180   0.701193274
> 6.18E-01    200   0.915799432
> 6.79E-01    220   0.735984375
> 7.32E-01    240   0.517838069
> 7.76E-01    260   0.042085025
> 8.13E-01    280   0.568346202
> 8.44E-01    300   0.067140721
> 8.70E-01    320   0.71437727
> 8.92E-01    340   0.712210456
> 9.10E-01    360   0.288460952
> 9.26E-01    380   0.185857723
> 9.38E-01    400   0.108859523
> 9.49E-01    420   0.513351848
> 9.57E-01    440   0.22214423
> 9.65E-01    460   0.500350963
> 9.71E-01    480   0.437818537
> 9.76E-01    500   0.851771599
> 9.80E-01    520   0.803521836
> 9.83E-01    540   0.249824519
> 9.86E-01    560   0.859248634
> 9.88E-01    580   0.126926481
> 9.90E-01    600   0.713431196
>                   0.253425703
>                   0.830195013
>                   0.723929563
>                   0.027588733
>                   0.091067232
>                   0.689504685
>                   0.890469069
>                   0.710440382
>
>
>
>
>
>
>        [[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.
>
>