how to match two column of one data frame with one column of other data frame

Question

I have two big data set

df1

full.name      first.name   age
bob marley     bob          10
jus bieber     jus          12 
xyz abcdef     xyz          14
abc qwerty     abc          15
hey hello      hey          10
jack ma        jack         12
zuke mark      mark         15

df2
name         age1
asd dfg      23
bob          10
jus bieber   12
xyz          23
abc qwerty   21
hey hello    10
jack         12
zuke mark    17  
bradd pit    50

and I want my answer like this

full.name      first.name   age     name       age1
bob marley     bob          10      bob          10
jus bieber     jus          12      jus bieber   12
xyz abcdef     xyz          14      xyz          23
abc qwerty     abc          15      abc qwerty   21
hey hello      hey          10      hey hello    10
jack ma        jack         12      jack         12
zuke mark      mark         15      zuke mark    17

I want to match full.name and first.name of df1 with name with df2 if

full.name matched with name or
first.name with name

and print value of age1 from whichever value of column of df1 matched with name(column) of df2

How about something like this? https://stackoverflow.com/a/38246955/322912 — Roman Luštrik, Feb 26 '19 at 17:27
Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Mako212, Feb 26 '19 at 17:35

score 0 · Accepted Answer · answered Feb 26 '19 at 17:37

0

This could be what you want although the name column is not in the final output.

library(tidyverse)

df3 <- df1 %>%
  rowid_to_column() %>%
  gather(type, name, -age, -rowid) %>%
  left_join(df2, by = "name") %>%
  group_by(rowid) %>%
  mutate(age1 = ifelse(is.na(age1), unique(age1[!is.na(age1)]), age1)) %>%
  spread(type, name) %>%
  ungroup()

df3
# # A tibble: 7 x 5
#   rowid   age  age1 first.name full.name 
#   <int> <int> <int> <chr>      <chr>     
# 1     1    10    10 bob        bob marley
# 2     2    12    12 jus        jus bieber
# 3     3    14    23 xyz        xyz abcdef
# 4     4    15    21 abc        abc qwerty
# 5     5    10    10 hey        hey hello 
# 6     6    12    12 jack       jack ma   
# 7     7    15    17 mark       zuke mark

DATA

df1 <- read.table(text = "full.name      first.name   age
'bob marley'     bob          10
'jus bieber'     jus          12 
'xyz abcdef'     xyz          14
'abc qwerty'     abc          15
'hey hello'      hey          10
'jack ma'        jack         12
'zuke mark'      mark         15",
                  header = TRUE, stringsAsFactors = FALSE)

df2 <- read.table(text = "name         age1
'asd dfg'      23
bob          10
'jus bieber'   12
xyz          23
'abc qwerty'   21
'hey hello'    10
jack         12
'zuke mark'    17  
'bradd pit'    50",
                  header = TRUE, stringsAsFactors = FALSE)

answered Feb 26 '19 at 17:37

www

38,575
12
48
84

i am getting error in rowid_to_column() data is missing – xyz Feb 26 '19 at 18:22
Did you load the `tidyverse` package? – www Feb 26 '19 at 18:24
Is the code working for the example data frames I provided? – www Feb 26 '19 at 18:27
actually i m having a different data set so i was working on that but data set is almost similar with the example – xyz Feb 26 '19 at 18:33
So my question is if the code works on the example data frames? – www Feb 26 '19 at 18:36
@xyz Glad to help. Please accept my post as the answer if it solves your question. – www Feb 26 '19 at 18:42
i want to ask you more question how can i print if there was string in column age and age1 – xyz Feb 26 '19 at 18:45
Not clear to me what do you want to ask. Please ask a new question and provide a reproducible example and clear description. – www Feb 26 '19 at 18:49

how to match two column of one data frame with one column of other data frame

1 Answers1