0

I am trying to join two data.tables in R. I am joining them by name and I would like to "insert" rows from one data table into the name groups of another.

So for example: Data table A has "names" and "amounts", data table B has "names" and "addresses" (but more than one address per name). I would like a data table which has each name, the corresponding addresses, and a single "amount" for each group of names.

I tried using "left_join" in dplyr, but the amount column would get duplicated for each "address" row.

Anyone have any ideas? Thank you.

Example Picture (joining table 1 and 2 to create 3):

or even like this:

EDIT: Added a reproducible example of what the two data sets are like and what the desired output is

table_one <- data.frame(name=c("x","y","z"), amount=c("$100","200","300"))
table_two <- data.frame(name=c("x","x","y","z","z","z"), address=c("A","B","C","D","E","F"))

output <- data.frame(name=c("x","x","y","z","z","z"), 
                     address=c("A","B","C","D","E","F"), amount=c("$100","","$200","$300","",""))
Sotos
  • 51,121
  • 6
  • 32
  • 66
A333
  • 3
  • 2
  • 2
    It's better to include a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input data in a form we can copy/paste. Pictures of data are not helpful. – MrFlick Jun 19 '17 at 15:15
  • Table 3 looks more like row binding than joining to me. Maybe `bind_rows`? – aosmith Jun 19 '17 at 15:37

3 Answers3

1

Using dplyr,

library(dplyr)

left_join(table_two, table_one, by = 'name') %>% 
   mutate(amount = replace(amount, duplicated(name), NA))
#  name address amount
#1    x       A   $100
#2    x       B   <NA>
#3    y       C    200
#4    z       D    300
#5    z       E   <NA>
#6    z       F   <NA>
Sotos
  • 51,121
  • 6
  • 32
  • 66
0

Here you go.

table_one <- data.frame(name=c("x","y","z"), amount=c("$100","$200","$300"))
table_two <- data.frame(name=c("x","x","y","z","z","z"), address=c("A","B","C","D","E","F"))

output <- data.frame(name=c("x","x","y","z","z","z"), 
                     address=c("A","B","C","D","E","F"), amount=c("$100","","$200","$300","",""))


test <- merge(table_one, table_two, by = 'name')
test$amount <- as.character(test$amount)
test$amount[duplicated(test[,c(1,2)])] <- ""
test
Balter
  • 1,085
  • 6
  • 12
0

We can do this with match

i1 <- with(table_one, match(name, table_two$name))
table_two$amount <- ""
table_two$amount[i1] <- as.character(table_one$amount)
table_two
#   name address amount
#1    x       A   $100
#2    x       B       
#3    y       C    200
#4    z       D    300
#5    z       E       
#6    z       F
akrun
  • 874,273
  • 37
  • 540
  • 662