CODEWITHSUNDEEP
rdataframe
user1987607
user1987607

Reputation: 2157

R match dataframe and concatenate values

I have the following 2 dataframes: df1 and df2. 

df1 <- data.frame("name" = c("a", "a", "b", "b", "b"), "col1" = c("s", "t", "u","v","w"))


df2 <- data.frame("name" = c("a", "b", "b"), "col2" =  c(1, 2, 3))

For each row of df2, I want to check if there is a match between the values in the "name" columns of both dataframes. If this is the case, the value of col2 and col1 should be concatenated together. The output would be written into a new dataframe df3, that would look like this

> output
  name col3
1    a  s:1
2    a  t:1
3    b  u:2
4    b  v:2
5    b  w:2
6    b  u:3
7    b  v:3
8    b  w:3

Upvotes: 1

Views: 99

Answers (3)

akrun
akrun

Reputation: 887831

An option with data.table

library(data.table)
setDT(df1)[df2, .(name, col3 = paste(col1, col2, sep=":")), on = .(name)]
#   name col3
#1:    a  s:1
#2:    a  t:1
#3:    b  u:2
#4:    b  v:2
#5:    b  w:2
#6:    b  u:3
#7:    b  v:3
#8:    b  w:3

Upvotes: 1

GKi
GKi

Reputation: 39717

You can use merge and create a new data.frame where you paste col1 and col2.

with(merge(df1, df2), data.frame(name, col3=paste(col1, col2, sep=":")))
#  name col3
#1    a  s:1
#2    a  t:1
#3    b  u:2
#4    b  u:3
#5    b  v:2
#6    b  v:3
#7    b  w:2
#8    b  w:3

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389235

We can left_join df1 and df2 by = "name" and combine the columns using unite to create new column. 

library(dplyr)
library(tidyr)
df1 %>% left_join(df2, by = "name") %>% unite(col3, col1, col2, sep = ":")

#  name col3
#1    a  s:1
#2    a  t:1
#3    b  u:2
#4    b  u:3
#5    b  v:2
#6    b  v:3
#7    b  w:2
#8    b  w:3

Or in base R

transform(merge(df1, df2, all.x = TRUE), col3 = paste(col1, col2, sep = ":"))

Upvotes: 0

Related Questions