CODEWITHSUNDEEP
rfor-loop
Lennon Lee
Lennon Lee

Reputation: 244

making a loop to match the value in one table to another, test the logicality , export the result

I have two tables. I want to match every value in one table to another, and with a criteria, I want to test whether this value is compatible in the second table. After testing, I would like to export the results into a third table, and I would like to do it with the for loop function in r, like:

df1 <- read.table(text = "ID1 Value1
A 1
B 2
C 3", stringsAsFactors = FALSE, header = TRUE)

df2 <-read.table(text = "ID1 Value2
D 1
E 2
F 3", stringsAsFactors = FALSE, header = TRUE)

For matching every value in df1 to df2 I want the criteria to be

BOTH

df1$Value1 >= 0.5*df2$Value2

AND 

df1$Value1 <= 2*df2$Value2

For example, when matching A in df1 to D in df2, the criteria is A>=0.5D AND A<=2D, if yes, export the result to a new table.

The algorithm will be in this picture: enter image description here

The final table will be:

ID1 Value1 ID2 Value2
A 1 D 1
A 1 E 2
B 2 D 1
B 2 E 2
B 3 F 3
C 3 E 2
C 3 F 3

Upvotes: 1

Views: 55

Answers (2)

Darren Tsai
Darren Tsai

Reputation: 35554

A dplyr way with outer():

library(dplyr)

outer(setNames(df1$Value1, df1$ID1), setNames(df2$Value2, df2$ID1), function(x, y){
  x >= 0.5*y & x <= 2*y
}) %>% as.table %>% as.data.frame %>%
  subset(Freq, select = -Freq) %>%
  left_join(df1, by = c("Var1" = "ID1")) %>%
  left_join(df2, by = c("Var2" = "ID1")) %>%
  arrange(Var1, Var2)

#   Var1 Var2 Value1 Value2
# 1    A    D      1      1
# 2    A    E      1      2
# 3    B    D      2      1
# 4    B    E      2      2
# 5    B    F      2      3
# 6    C    E      3      2
# 7    C    F      3      3

Upvotes: 1

Sotos
Sotos

Reputation: 51592

Not sure why you need to do it via for loop, but here is a more R-way of doing it,

d1 <- setNames(expand.grid(df1$Value1, df2$Value2), c('Value2', 'Value1'))
d2 <- setNames(expand.grid(df1$ID1, df2$ID2), c('ID2', 'ID1'))
d1 <- transform(d1, cond1 = Value2 * 0.5, cond2 = Value2 * 2)
final_d <- cbind(d2, d1)
final_d[final_d$Value1 >= final_d$cond1 & final_d$Value1 <= final_d$cond2,]

which gives,

  ID2 ID1 Value2 Value1 cond1 cond2
1   A   D      1      1   0.5     2
2   B   D      2      1   1.0     4
4   A   E      1      2   0.5     2
5   B   E      2      2   1.0     4
6   C   E      3      2   1.5     6
8   B   F      2      3   1.0     4
9   C   F      3      3   1.5     6

Upvotes: 1

Related Questions