Merging issue on R

Question

I am trying to merge a 100-column dataset with roughly 5k rows (df1) with a single column of another dataset (df2$X), based on the ID column.

E.g.

df1:

Id    Price   Animals 
2     1       Dog
3     1       Cat
4     3       Dog
5     4       Cat
6     5       Mouse
10    9       Cat
16    0       Cat
14    4       Cat

df2: 
Id    X   
2     Kiwi      
5     Banana    
6     Apple      
7     Berries      
8     Mango      
10    Melon

The resulting dataset should be:


Id    Price   Animals  X
2     1       Dog      Kiwi
3     1       Cat      NA
4     3       Dog      NA
5     4       Cat      Banana
6     5       Mouse    Apple
10    9       Cat      Melon
16    0       Cat      NA
14    4       Cat      NA

I tried doing:

merge(df1, df2, by="Id")

but this seems not to be working.

Thanks!

score 1 · Answer 1 · answered Nov 01 '20 at 12:29

1

library(tidyr)
library(dplyr)
df1 %>% left_join(df2)
Joining, by = "Id"
# A tibble: 8 x 4
     Id Price Animals X     
  <dbl> <dbl> <chr>   <chr> 
1     2     1 Dog     Kiwi  
2     3     1 Cat     NA    
3     4     3 Dog     NA    
4     5     4 Cat     Banana
5     6     5 Mouse   Apple 
6    10     9 Cat     Melon 
7    16     0 Cat     NA    
8    14     4 Cat     NA    
>

answered Nov 01 '20 at 12:29

Karthik S

11,348
2
11
25

Thanks! but it is not working for me? – Nov 01 '20 at 12:44
Are you getting any error? – Karthik S Nov 01 '20 at 12:45
It simply does not show the additional column in the df1. – Nov 01 '20 at 12:45
Not sure how that's possible, are you selecting an particular columns before joining? – Karthik S Nov 01 '20 at 12:47
df1$X <- left_join(df1, df2, by="ID") Like this, it works but weirdly the columns are copied twice (so I have Id.X, Animal.X etc.) – Nov 01 '20 at 12:58
Just `df1 <- left_join(etc)`, no `$X`! – Rui Barradas Nov 01 '20 at 13:06

score 1 · Accepted Answer · answered Nov 01 '20 at 13:05

1

If you have duplicate columns in both the dataframe you can select only the columns that you need before merge.

df3 <- merge(df1, df2[c('Id', 'X')], by= "Id", all.x= TRUE)

answered Nov 01 '20 at 13:05

Ronak Shah

377,200
20
156
213

Merging issue on R

2 Answers2