How to arrange the values in a column to a new column based on a condition and a substring?

I have a data frame including three columns as below. I need to add a new column based on the following condition:
If in var_field, the string following $ equals the string in text, put the corresponding value in var in the new column called new_col. When text equals NA, the new_col should remain NA as well. I would really appreciate your advice.

The expected new column should look like column "new_col".

Another approach, using various tidyverse functions.

Set up test data:

testdata <- tribble(
~var, ~text, ~var_field,
"A",	"happy",	"A$excited",
"B",	"sad",	"B$angry",
"C",	"angry",	"C$sad",
"D",	"excited",	"D$happy",
"E",	NA,	"E$nervous",
"F",	NA,	"F$blue",
"G",	NA,	"G$lonely")

create a reference for which text returns which var

lookup <- as_vector(testdata$var)
names(lookup) <- testdata$text

then do the creation of the new column

testdata %>% mutate(
  field_text = str_extract(var_field, "(?<=\$)(.*)"), #drop the leading character and "$"
  new_col = case_when(
    is.na(text) ~ NA_character_,
    .default = lookup[field_text]
  ) # created the new_col as per spec
) %>% 
  select(-field_text) # drop the simplified var_field as no longer needed

gives

# A tibble: 7 × 4
  var   text    var_field new_col
  <chr> <chr>   <chr>     <chr>  
1 A     happy   A$excited D      
2 B     sad     B$angry   C      
3 C     angry   C$sad     B      
4 D     excited D$happy   A      
5 E     NA      E$nervous NA     
6 F     NA      F$blue    NA     
7 G     NA      G$lonely  NA  

Edit: this assumes that the non-NA options under text are unique, which is not correct according to OP.

For 1st(!) match in base R:

df_ <- read.table(header = T, text = "
var text    var_field
A   happy   A$excited
B   sad B$angry
C   angry   C$sad
D   excited D$happy
E   NA  E$nervous
F   NA  F$blue
G   NA  G$lonely")


suffix <- sapply(strsplit(df_$var_field, "$", fixed = TRUE), `[`, 2)
df_$new_col <- df_$var[match(df_$text, suffix)]
df_
#>   var    text var_field new_col
#> 1   A   happy A$excited       D
#> 2   B     sad   B$angry       C
#> 3   C   angry     C$sad       B
#> 4   D excited   D$happy       A
#> 5   E    <NA> E$nervous    <NA>
#> 6   F    <NA>    F$blue    <NA>
#> 7   G    <NA>  G$lonely    <NA>

^{Created on 2023-06-08 with reprex v2.0.2}

Try this:

quux %>%
  mutate(text2 = sub(".*\\$", "", var_field)) %>%
  left_join(quux, by = c(text2 = "text"), suffix = c("", ".y"), multiple = "first") %>%
  mutate(new_col2 = var.y) %>%
  select(-ends_with(".y"), -text2)
#   var    text var_field new_col new_col2
# 1   A   happy A$excited       D        D
# 2   B     sad   B$angry       C        C
# 3   C   angry     C$sad       B        B
# 4   D excited   D$happy       A        A
# 5   E    <NA> E$nervous    <NA>     <NA>
# 6   F    <NA>    F$blue    <NA>     <NA>
# 7   G    <NA>  G$lonely    <NA>     <NA>

Data

quux <- structure(list(var = c("A", "B", "C", "D", "E", "F", "G"), text = c("happy", "sad", "angry", "excited", NA, NA, NA), var_field = c("A$excited", "B$angry", "C$sad", "D$happy", "E$nervous", "F$blue", "G$lonely"), new_col = c("D", "C", "B", "A", NA, NA, NA)), class = "data.frame", row.names = c(NA, -7L))

Here is another approach:

library(data.table)

as.data.table(tstrsplit(df$var_field, "$",fixed = T))[
  df, on=.(V2 = text)][, .(var, text=V2,var_field,new_col=V1)]

Output:

      var    text var_field new_col
   <char>  <char>    <char>  <char>
1:      A   happy A$excited       D
2:      B     sad   B$angry       C
3:      C   angry     C$sad       B
4:      D excited   D$happy       A
5:      E    <NA> E$nervous    <NA>
6:      F    <NA>    F$blue    <NA>
7:      G    <NA>  G$lonely    <NA>