0

My two (input) data.frames look like this:

df1

 Group Name      Vote
    AB    a Sentence1
    AB    b Sentence2
    AB    c Sentence3
    XY    d Sentence4
    XY    e Sentence5
    XY    f Sentence6
    ZW    a Sentence7
    ZW    b Sentence8
    ZW    c Sentence9

df2

 AB XY ZW
  a  d  a
  b  e  b
  c  f  c

I need to replace df2 respective contents with df1$Vote. Notice that both group AB and ZW share the same Name elements - so it important to also check that both (column) names & content match between tables.

Like this:

Resulting df3:

        AB        XY        ZW
 Sentence1 Sentence4 Sentence7
 Sentence2 Sentence5 Sentence8
 Sentence3 Sentence6 Sentence9

Great thanks & appreciation for any help!

Code for df1 & df2:

df1 <- data.frame(Group = c("AB", "AB", "AB", "XY", "XY", "XY", "ZW", "ZW", "ZW"), 
                  Name = c("a", "b", "c", "d", "e", "f", "a", "b", "c"), 
                  Vote = c("Sentence1", "Sentence2", "Sentence3", "Sentence4", "Sentence5", "Sentence6", "Sentence7", "Sentence8", "Sentence9"))

df2 <- data.frame(AB = c("a", "b", "c"), 
                  XY = c("d", "e", "f"), 
                  ZW = c("a", "b", "c"))
HardiK
  • 35
  • 3
  • One way: `df2[] <- lapply(df2, function(x) x = df1$Vote[match(x,df1$Name)])` – Jaap Mar 10 '18 at 20:47
  • Thanks! It is important to check repsective Group name also besides Name. Let's say there is a new Group called PO also with a Name "a" – HardiK Mar 10 '18 at 20:55
  • `df2 %>% gather(Group, Name) %>% left_join(., df1) %>% select(-Name) %>% group_by(Group) %>% mutate(r = row_number()) %>% spread(Group, Vote) %>% select(-r)` – Jaap Mar 10 '18 at 21:06
  • or with *data.table*: `melt(setDT(df2), measure.vars = 1:3)[df1, on = .(variable = Group, value = Name)][, dcast(.SD, rowid(variable) ~ variable, value.var = 'Vote')][, variable := NULL][]` – Jaap Mar 10 '18 at 21:25

1 Answers1

0

Here is the dplyr approach:

library(tidyverse)


result <- df2 %>% 
  left_join(df1, by=c("AB" = "Name")) %>% select(XY, ZW, AB=Vote) %>% 
  left_join(df1, by=c("XY" = "Name")) %>% select(AB, XY=Vote, ZW) %>% 
  left_join(df1, by=c("ZW" = "Name")) %>% select(AB, XY, ZW=Vote)

result
Stephan
  • 2,056
  • 1
  • 9
  • 20