How to find the value based on two columns

Question

The data set is:

col_1,col_2,value
8521,13394,24
8521,14353,15
...
1112074,1112073,52

I want a lookup for a number in the value column given numbers for the first two columns. I know using data.frame or array can solve this problem. But the maximum value in col_1 or col_2 is so big that I cannot build a 1112074*3 data.frame. How to solve this problem?

Could you re-phrase the question to make it a little more clear? — Hack-R
– Hack-R, Commented Jul 29, 2014 at 4:27
Make a 1112074*2 data.frame having col_1 and col_2. Value can be calculated when required. — rnso
– rnso, Commented Jul 29, 2014 at 4:46

MrFlick · Accepted Answer · 2014-07-29 04:46:06Z

4

If i understand you correctly, you want a lookup for a number in the value column given numbers for the first two columns

Here's one way using a simple data.frame and a loop-up function

dd<-data.frame(
   col_1 = c(8521, 8521, 1112074),
   col_2 = c(13394, 14353, 1112073),
   value = c(24,15,52)
)

getval<-function(c1,c2, data=dd) {
    data$value[data$col_1==c1 & data$col_2==c2]
}

getval(8521, 14353)
# [1] 15

Unfortunately this procedure isn't very fast. If you plan to do this often, you might consider using the data.table library which allows you to index your table for faster look-up

library(data.table)
dt<-data.table(
   col_1 = c(8521, 8521, 1112074),
   col_2 = c(13394, 14353, 1112073),
   value = c(24,15,52)
)
setkey(dt, col_1, col_2)

getval<-function(c1,c2, data=dt) {
    dt[.(c1,c2)][, value]
}
getval(8521, 14353)
# [1] 15

answered Jul 29, 2014 at 4:46

MrFlick

209k19 gold badges300 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to find the value based on two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related