Replace all values in dataframe from values in data.table in R

Question

I am fairly new to R and am trying to replace values in my data.frame named df from corresponding values in a data.table called dtb. My data.table is simply a count of all unique values in my data.frame and looks as such:

df
  fruits
1 apple
2 pear
3 banana
4 apple
5 banana  


dtb
     fruits    N
1    apple     2
2    pear      1
3    banana    2

I wish to replace my original data.frame values with the counts based upon the name. For output would look like this:

df
  fruits
1 2
2 1
3 2
4 2
5 2 

I have tried to look through observations and check if it is in the fruits column of the data table but am struggling. Could anyone help me here? Thanks

Answer 1

You can use join() functions from dplyr package and then select and rename the new column:

# 1. join by "fruits" column, i.e. create a new corresponding N column
# 2. select only column N and rename it to "fruits"
df %>%
  left_join(dtb, by = "fruits") %>%
  select(fruits = N)
#>   fruits
#> 1      2
#> 2      1
#> 3      2
#> 4      2
#> 5      2

^{Created on 2020-07-24 by the reprex package (v0.3.0)}

Data

df <- structure(list(fruits = c("apple", "pear", "banana", "apple", 
"banana")), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5"))

dtb <- structure(list(fruits = c("apple", "pear", "banana"), N = c(2L, 
1L, 2L)), row.names = c(NA, -3L), class = c("data.table", "data.frame"
), .internal.selfref = <pointer: 0x0000021f51801ef0>)

Answer 2

You can convert your data table into a simple map:

df$fruits <- sapply(df$fruits, function(x) setNames(dtb$N, dtb$fruits)[x])

which gives:

> df
  fruits
1      2
2      2
3      1
4      2
5      1

Data

dtb <- data.table::as.data.table(read.table(text="fruits    N
1    apple     2
2    pear      1
3    banana    2", header=TRUE))

df <- read.table(text="fruits
1 apple
2 pear
3 banana
4 apple
5 banana", header=TRUE)