Difficulty with join

tidyverse
#1

Hi. I am having trouble visualizing how to do the join I want.

I have a table t that looks like this:

Person Commission
====== ==========
Art 10%
Bill 15%
Ed 17%
Sue 20%

I have a data frame df that looks like this:

word1 word2 word3 word4
===== ===== ===== =====
Art sold a book
Sue sold a painting
Bill and Sue sold

I would like to join t and df and get 10% in row 1, 20% in row 2, and it is possible to get both 15% and 20% in row3?

t <- data.frame(Person=c("Art","Bill","Ed","Sue"), Commission=c(10%, 15%, 17%, 20%))
df <- data.frame(word1=c("Art","Sue","Bill"), word2=c("sold","sold","and"), word3=c("a","a","Sue"), word4=c("book","painting","sold"))

#2

Can you provide a sample of the output as well?, it is not clear what kind of output you expect, specially for row3.

#3

A solution for the example is quite simple, bit not sure how scalable it would be

df %>%
  left_join( t, by = c("word1" = "Person") %>%
  left_join(t, by = c("word2" = "Person")
#4

Calum, your code gives me an error "unexpected end of input".

Also, I am hoping the joining can be with the rows of df, not with its columns.

#5

Andresrcs

row1: Art .10 in two columns
row2: Sue .20 in two columns
row3: Bill .15 Sue .20 in four columns?

#6

A proper dataframe in code would be easier to grasp, it is always better to make technical questions in a reproducible manner.

#7

My bad:

df %>%
  left_join( t, by = c("word1" = "Person")) %>%
  left_join(t, by = c("word2" = "Person"))
#8

And based on what you replied:

t <- data.frame(Person=c("Art","Bill","Ed","Sue"), 
                Commission=c("10%", "15%", "17%", "20%"))
df <- data.frame(word1=c("Art","Sue","Bill"), 
                 word2=c("sold","sold","and"), 
                 word3=c("a","a","Sue"), 
                 word4=c("book","painting","sold"))

require(dplyr)

df %>%
    left_join( t, by = c("word1" = "Person")) %>%
    left_join(t, by = c("word3" = "Person")) %>%
    select(word1, word3, Commission.x, Commission.y)

word1 word3 Commission.x Commission.y
1 Art a 10%
2 Sue a 20%
3 Bill Sue 15% 20%

#9

I amused myself going a little overboard on this...

library(tidyverse)
t <- tibble(
  Person = c("Art", "Bill", "Ed", "Sue"),
  Commission = c("10%", "15%", "17%", "20%")
)

df <- tibble(
  word1 = c("Art", "Sue", "Bill"),
  word2 = c("sold", "sold", "and"),
  word3 = c("a", "a", "Sue"),
  word4 = c("book", "painting", "sold")
)


(wf1 <- map(
  names(df),
  ~ {select(df, .x) %>%
      rename("Person" = .x)}
))

(wf2 <- map(
  wf1,
  ~ {
    if (any(.$Person %in% t$Person)) {
      .
    } else {
      NULL
    }}
))
(wf3 <- wf2[lengths(wf2)])


(comframes <- map(
  wf3,  ~ left_join(., t)
))

bind_cols(comframes) %>%
  select(
    starts_with("Person"),
    starts_with("Comm")
  ) %>%
  select_if(~ !all(is.na(.)))

# A tibble: 3 x 4
  Person...1 Person...3 Commission...2 Commission...4
  <chr>      <chr>      <chr>          <chr>         
1 Art        Art        10%            10%           
2 Sue        Sue        20%            20%           
3 Bill       Bill       15%            15%