I have 2 data frames like this:
A:
col1 col2
1 a
1 b
1 b
1 c
1 c
2 x
2 y
2 y
3 k
3 k
3 m
3 m
B:
col1 col2 col3
1 a 0.3
1 b 0.001
1 c 0.0004
2 x 0.005
2 y 0.09
3 k 0.00007
3 m 0.08
What I want to do is to create another col3 on A using mutate and ifelse. If the value for the col2 in B is less than 0.05, I want the value in the col3 to be "other"; else from col2 of A. The output should look like this:
A:
col1 col2 col3
1 a a
1 b other
1 b other
1 c other
1 c other
2 x x
2 y y
2 y y
3 k other
3 k other
3 m m
3 m m
I tried using mutate and ifelse combination, but couldnt figure out how to do the comparison part between A and B.
vals_for_plot = A %>%
mutate(col3 = ifelse( **value for col2 of A in B** < 0.05, "others", col2))
Thank you
We may do a join after modifying the second data
library(dplyr)
B %>%
mutate(col3 = case_when(col3 < 0.05 ~'other', TRUE ~ col2)) %>%
left_join(A, .)
Or with data.table
library(data.table)
setDT(A)[B, col3 := fcase(col3 >= 0.05, col2, default = 'others'),
on = .(col1, col2)]