Help please with a tricky problem: compare one element to a row in a table

[Lill]

Hi

Please help I am stuck with a really tricky problem:
Hi I have a really tricky problem. I tried several approaches and so far I got lost.

I have a table with one column which contains 9 and 2.

This is the second table:

> my_crazy_data
   V1 V2  V3  V4  V5  V6  V7
1   1  a 2.0 3.0 4.0 9.0 6.0
2   1  b 0.2 0.1 0.8 0.9 0.2
3   1  c 0.1 0.3 0.4 0.1 0.8
4   1  d 0.1 0.1 0.3 0.1 0.4
5   2  a 1.0 2.0 3.0 4.0 5.0
6   2  b 0.1 0.4 0.3 0.5 0.7
7   2  c 0.1 0.4 0.2 0.5 0.8
8   2  d 0.2 0.5 0.6 0.7 0.9
9   3  a 8.0 2.0 3.0 4.0 5.0
10  3  b 0.1 0.4 0.3 0.5 0.7
11  3  c 0.1 0.4 0.2 0.5 0.8
12  3  d 0.2 0.5 0.6 0.7 0.9

Now I want to take the first element from the first table (in this case 9) and find it in the first row V3-V7 as V1 is the index. Once I found 9 (in this case V6) I want to return all V6 for the index V1=1 with V1 and V2 so a result would look like:

1 a 9.0
1 b 0.9
1 c 0.1 
1 d 0.1

then proceed the second element from table1 (in this case 2) and same procedure but only search in the first row that has the first V1=2 so I would search for 2 in line 5 and once I found it only output the columns for index V1=2 so a result would look like:

2 a 2.0
2 b 0.4
2 c 0.4
2 d 0.5

How can I do that? Please help - any method would ok.

Maybe doing this will work:

# Your vector of values to look up

v <- c(9, 2, 4)

# Split the second table by V1

d.split <- split(d, d$V1)

do.call(rbind,
        mapply(function(x, y) {
          setNames(x[, c(1:2, 2 + match(y, x[1, -(1:2)]))], 
                   c('V1', 'V2', 'val'))
          }, d.split, v, SIMPLIFY=FALSE)
)

but the do.call breaks if it can not find an element? How can I search in the row and then return 0 if there is no element that matches and carry on?

Thank you so much!

xx

 

[JesperHP]

What do you mean with do.call breaks?If it is somekind of error occuring perhaps you could use tryCatch?

 

[Lill]

What do you mean with do.call breaks?If it is somekind of error occuring perhaps you could use tryCatch?

Hi,

Yes sorry the code works for

v <- c(9, 2, 4)

but I acutually have 270 elements in there and It cant compare them anymore. Or if I have an element in v that is not in table2 so it cant find it.

I always get the following error:

Error in `[.data.frame`(x, , c(1:2, 2 + match(y, x[1, -(1:2)], nomatch = NA_integer_,  : undefined columns selected