Combine two columns of different dataframes

Question

I have the following dataframes:

> dput(df)
structure(list(word = c("hello", "red", "hello"), polarity = c(0, 0, 0)), row.names = c(5L, 10L, 17L), class = "data.frame")
> dput(df2)
structure(list(x = c("hello", "red"), y = c(0.1, -0.3)), row.names = c(1607, 1743), class = "data.frame")

i.e.

             word polarity
5           hello        0
10            red        0
17          hello        0

and

                  x    y
1607          hello  0.1
1743            red -0.3

I would like to combine these dataframes in such a way the desired result is:

             word polarity
5           hello      0.1
10            red     -0.3
17          hello      0.1

Answer 1

We can use match between the 'word' and 'x' column from 'df', 'df2', respectively to get the position, use that to extract the corresponding 'y' from 'df2'

df$polarity <- df2$y[match(df$word, df2$x)]

-output

 df
    word polarity
5  hello      0.1
10   red     -0.3
17 hello      0.1

Answer 2

Here's a tidyverse solution, using left_join.

library(tidyverse)

df1 %>%
  # Remove polarity column from df1
  select(-polarity) %>%
  # Left join using the word column
  # First you'll need to rename the x column in df2 to be the same in df1 (i.e., word)
  left_join(df2 %>%
              rename("word" = x), 
            # The name of the column to use for the left join
            by = "word")

#   word    y
#1 hello  0.1
#2   red -0.3
#3 hello  0.1

Answer 3

Use a left join or inner join in dplyr. you don't actually need to rename the columns beforehand.

new_df<-left_join(df1,df2, by = c("word" = "x))

You will have the extra column from the join because joins retain all columns you can either get rid of it before the join or after.

###Before the join run this

df1$polarity = NULL

####join
names(new_df)<- c("word", "polarity")

#### if performed  after the  join
new_df$polarity = NULL
names(new_df)<- c("word", "polarity")

Answer 4

you can use df$polarity<-df2$y