CODEWITHSUNDEEP
rdataframesearchmatchgrepl
newt335
newt335

Reputation: 199

Check column A in table 1 if contains value from column A in table 2 and return the value of column B table 2

In the this example, I am trying to see whether values in column A in table 1 appear within the values in column A table 2.

If it does I want to add column B in table 1, which returns the value of column B in table 2 where the matches occur.

Desired output:

Table 1

A B
dhjasd sd Y02sd 105
Y02 dsd 105
sds Y0545 dsas dds 106
Y093 sad
sd Y304sd

Reference Table:

Table 2

A B
Y01 102
Y06 103
Y02 105
Y08 110
Y0545 106

Can I please get any advice on how I should approach this? Thank you for your help!

Upvotes: 0

Views: 62

Answers (1)

Isaac
Isaac

Reputation: 986

Using dplyr you can do the following:

table1 |> 
  select(A) |> 
  mutate(values = stringr::str_extract(A, "Y\\d+")) |> 
  left_join(table2, by = c("values" = "A")) |> 
  select(A, B)

# A tibble: 5 × 2
  A                      B
  <chr>              <dbl>
1 dhjasd sd Y02sd      105
2 Y02 dsd              105
3 sds Y0545 dsas dds   106
4 Y093 sad              NA
5 sd Y304sd             NA

Upvotes: 1

Related Questions