Left join only selected columns in R with the merge() function

Question

I am trying to LEFT Join 2 data frames but I do not want join all the variables from the second data set:

As an example, I have dataset 1 (DF1):

  Cl    Q   Sales  Date
   A    2   30     01/01/2014
   A    3   24     02/01/2014
   A    1   10     03/01/2014
   B    4   10     01/01/2014
   B    1   20     02/01/2014
   B    3   30     03/01/2014

And I would like to left join dataset 2 (DF2):

Client  LO  CON
   A    12  CA
   B    11  US
   C    12  UK
   D    10  CA
   E    15  AUS
   F    91  DD

I am able to left join with the following code:

merge(x = DF1, y = DF2, by = "Client", all.x=TRUE) :

   Client Q    Sales   Date             LO      CON
   A      2    30      01/01/2014       12      CA
   A      3    24      02/01/2014       12      CA
   A      1    10      03/01/2014       12      CA
   B      4    10      01/01/2014       11      US
   B      1    20      02/01/2014       11      US
   B      3    30      03/01/2014       11      US

However, it merges both column LO and CON. I would only like to merge the column LO.

   Client Q    Sales   Date             LO      
   A      2    30      01/01/2014       12      
   A      3    24      02/01/2014       12      
   A      1    10      03/01/2014       12      
   B      4    10      01/01/2014       11      
   B      1    20      02/01/2014       11     
   B      3    30      03/01/2014       11

you need to rename "Cl" into "Client" in DF1 (in the written example above). Otherwise the merge function does not work. — Thieme Hennis
– Thieme Hennis, Commented Nov 24, 2014 at 23:56

stanekam · Accepted Answer · 2014-06-12 18:36:35Z

137

You can do this by subsetting the data you pass into your merge:

merge(x = DF1, y = DF2[ , c("Client", "LO")], by = "Client", all.x=TRUE)

Or you can simply delete the column after your current merge :)

answered Jun 12, 2014 at 18:36

stanekam

4,0392 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dpel Over a year ago

remembering that you need to include the variable you are merging by

Ben G · Accepted Answer · 2020-01-27 16:18:58Z

31

I think it's a little simpler to use the dplyr functions select and left_join ; at least it's easier for me to understand. The join function from dplyr are made to mimic sql arguments.

 library(tidyverse)

 DF2 <- DF2 %>%
   select(client, LO)

 joined_data <- left_join(DF1, DF2, by = "Client")

You don't actually need to use the "by" argument in this case because the columns have the same name.

edited Jan 27, 2020 at 16:18

answered Jun 7, 2018 at 16:43

Ben G

4,3582 gold badges24 silver badges46 bronze badges

Comments

rosie-betzler · Accepted Answer · 2021-07-27 17:08:07Z

6

Alternative solution using left_join() and select() from the dplyr package, without intermediate steps:

DF1 <- DF1 %>%
  left_join(DF2, by = "Client") %>%
  select(-CON)

answered Jul 27, 2021 at 17:08

rosie-betzler

1312 silver badges1 bronze badge

Comments

Akshay Kadidal · Accepted Answer · 2018-08-07 06:45:29Z

5

Nothing elegant but this could be another satisfactory answer.

merge(x = DF1, y = DF2, by = "Client", all.x=TRUE)[,c("Client","LO","CON")]

This will be useful especially when you don't need the keys that were used to join the tables in your results.

answered Aug 7, 2018 at 6:45

Akshay Kadidal

5351 gold badge7 silver badges15 bronze badges

Comments

Quechua · Accepted Answer · 2022-01-25 14:48:06Z

4

One-liner with dplyr

DF_joined <- left_join(DF1, select(DF2, -CON), by = "Client")

answered Jan 25, 2022 at 14:48

Quechua

16510 bronze badges

Comments

Mabar · Accepted Answer · 2022-02-10 15:16:33Z

3

For Client column in both tables:

DF_joined <- DF1 %>% left_join(DF2 %>% select(Client,CON))

answered Feb 10, 2022 at 15:16

Mabar

311 bronze badge

Collectives™ on Stack Overflow

Left join only selected columns in R with the merge() function

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related