Hi R-users,
I would like to search for the values of seq that match my rand values. In
excel I will use =VLOOKUP(G2,$E$2:$F$32,2). For example, for rand=.262 it will
give me approximately seq=120 and rand=0.964293344, seq=460 and etc.
E F G
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
Thank you so much for your help.
[[alternative HTML version deleted]]
Hello, I'm trying to figure out exactly what you want. You have data with three columns (cdf, seq, and rand). You want to take a number in column "rand", find the closest match to it in "cdf" and return the value of "seq" for the same row as the closest match in "cdf"? Since you did not fix (i.e., use the $ in the Excel formula) the G2 and 2 in your formula, should we assume that you would also like to do this for the entire "rand" column (as in Excel by 'dragging' the formula down a column)? Josh On Thu, May 27, 2010 at 6:20 PM, Roslina Zakaria <zroslina at yahoo.com> wrote:> Hi R-users, > > I would like to search for the values of seq that match my rand values.? In excel I will use =VLOOKUP(G2,$E$2:$F$32,2).? For example, for rand=.262 it will give me approximately seq=120 and rand=0.964293344, seq=460 and etc. > > E???????????F???? G > 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 > Thank you so much for your help. > > > > > ? ? ? ?[[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 Senior in Psychology University of California, Riverside http://www.joshuawiley.com/
?findInterval On Thu, May 27, 2010 at 9:20 PM, Roslina Zakaria <zroslina at yahoo.com> wrote:> Hi R-users, > > I would like to search for the values of seq that match my rand values.? In excel I will use =VLOOKUP(G2,$E$2:$F$32,2).? For example, for rand=.262 it will give me approximately seq=120 and rand=0.964293344, seq=460 and etc. > > E???????????F???? G > 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 > Thank you so much for your help. > > > > > ? ? ? ?[[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 that you are trying to solve?
Does this do what you are looking for?
###########################
#Create data based off what you provided
datasamp <- structure(list(cdf = c(0, 0.00156, 0.0155, 0.053, 0.116, 0.197,
0.288, 0.38, 0.468, 0.548, 0.618, 0.679, 0.732, 0.776, 0.813,
0.844, 0.87, 0.892, 0.91, 0.926, 0.938, 0.949, 0.957, 0.965,
0.971, 0.976, 0.98, 0.983, 0.986, 0.988, 0.99), seq = c(0L, 20L,
40L, 60L, 80L, 100L, 120L, 140L, 160L, 180L, 200L, 220L, 240L,
260L, 280L, 300L, 320L, 340L, 360L, 380L, 400L, 420L, 440L, 460L,
480L, 500L, 520L, 540L, 560L, 580L, 600L), rand = c(0.262123478,
0.964293344, 0.494827113, 0.733726005, 0.800408948, 0.925748466,
0.047578356, 0.266060366, 0.125522629, 0.701193274, 0.915799432,
0.735984375, 0.517838069, 0.042085025, 0.568346202, 0.067140721,
0.71437727, 0.712210456, 0.288460952, 0.185857723, 0.108859523,
0.513351848, 0.22214423, 0.500350963, 0.437818537, 0.851771599,
0.803521836, 0.249824519, 0.859248634, 0.126926481, 0.713431196
)), .Names = c("cdf", "seq", "rand"), class =
"data.frame", row.names = c(NA,
-31L))
#use findInterval() to find the row numbers and then return those rows
from the dataframe for the seq column
datasamp[findInterval(x=datasamp$rand, vec=datasamp$cdf),"seq"]
Josh
On Thu, May 27, 2010 at 6:20 PM, Roslina Zakaria <zroslina at yahoo.com>
wrote:> Hi R-users,
>
> I would like to search for the values of seq that match my rand values.? In
excel I will use =VLOOKUP(G2,$E$2:$F$32,2).? For example, for rand=.262 it will
give me approximately seq=120 and rand=0.964293344, seq=460 and etc.
>
> E???????????F???? G
> 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
> Thank you so much for your help.
>
>
>
>
> ? ? ? ?[[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
Senior in Psychology
University of California, Riverside
http://www.joshuawiley.com/
Hi:
I think the idea here is to use the cdf intervals as the lookup table for
rand (the entire
vector) and then return the seq value corresponding to the found interval.
Combining
the ideas from Jim Holtman (use findInterval()) and Josh Wiley, what worked
for me
was to split the OP's data into three vectors. Starting from Josh's
datasamp,
datasamp <- structure(list(cdf = c(0, 0.00156, 0.0155, 0.053, 0.116, 0.197,
0.288, 0.38, 0.468, 0.548, 0.618, 0.679, 0.732, 0.776, 0.813,
0.844, 0.87, 0.892, 0.91, 0.926, 0.938, 0.949, 0.957, 0.965,
0.971, 0.976, 0.98, 0.983, 0.986, 0.988, 0.99), seq = c(0L, 20L,
40L, 60L, 80L, 100L, 120L, 140L, 160L, 180L, 200L, 220L, 240L,
260L, 280L, 300L, 320L, 340L, 360L, 380L, 400L, 420L, 440L, 460L,
480L, 500L, 520L, 540L, 560L, 580L, 600L), rand = c(0.262123478,
0.964293344, 0.494827113, 0.733726005, 0.800408948, 0.925748466,
0.047578356, 0.266060366, 0.125522629, 0.701193274, 0.915799432,
0.735984375, 0.517838069, 0.042085025, 0.568346202, 0.067140721,
0.71437727, 0.712210456, 0.288460952, 0.185857723, 0.108859523,
0.513351848, 0.22214423, 0.500350963, 0.437818537, 0.851771599,
0.803521836, 0.249824519, 0.859248634, 0.126926481, 0.713431196
)), .Names = c("cdf", "seq", "rand"), class =
"data.frame", row.names c(NA,
-31L))
cdf <- datasamp[, 1]
sq <- datasamp[, 2]
rand <- c(datasamp$rand, 0.253425703, 0.830195013, 0.723929563,
0.027588733, 0.091067232, 0.689504685, 0.890469069, 0.710440382)
rm(datasamp)
cbind(rand, sq[findInterval(rand, cdf)], sq[findInterval(rand, cdf) + 1])
rand
[1,] 0.26212348 100 120
[2,] 0.96429334 440 460
[3,] 0.49482711 160 180
[4,] 0.73372600 240 260
[5,] 0.80040895 260 280
[6,] 0.92574847 360 380
[7,] 0.04757836 40 60
...
I noticed that the original result seemed to be an interval below what the
OP
expected, so add one to the vector of indices returned from findInterval()
to
get the third column.
HTH,
Dennis
On Thu, May 27, 2010 at 6:20 PM, Roslina Zakaria <zroslina@yahoo.com>
wrote:
> Hi R-users,
>
> I would like to search for the values of seq that match my rand values. In
> excel I will use =VLOOKUP(G2,$E$2:$F$32,2). For example, for rand=.262 it
> will give me approximately seq=120 and rand=0.964293344, seq=460 and etc.
>
> E F G
> 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
> Thank you so much for your help.
>
>
>
>
> [[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]]