Join 2 dataframes together if two columns match

Question

I have 2 dataframes:

CountryPoints

From.country  To.Country points
Belgium       Finland    4
Belgium       Germany    5
Malta         Italy      12
Malta         UK         1

and another dataframe with neighbouring/bordering countries:

From.country    To.Country 
    Belgium       Finland   
    Belgium       Germany   
    Malta         Italy

I would like to add another column in CountryPoints called neighbour (Y/N) depending if the key value pair is found in the neighbour/bordering countries dataframe. Is this somehow possible - so it is a kind of a join but the result should be a boolean column.

The result should be:

From.country  To.Country points  Neighbour
    Belgium       Finland    4    Y
    Belgium       Germany    5    Y
    Malta         Italy      12   Y
    Malta         UK         1    N

In the question below it shows how you can merge but it doesn't show how you can add that extra boolean column

Also, related post: https://stackoverflow.com/questions/1169248/test-if-a-vector-contains-a-given-element — zx8754, May 09 '18 at 09:33
To get boolean values you could just do `df1$From.country %in% df2$From.country & df1$To.Country %in% df2$To.Country` — Ronak Shah, May 09 '18 at 09:47
This will give me a true irrelevant if they are in the same row in df2 — user9737581, May 09 '18 at 09:54
Do you need to match it row-wise? So `Belgium Finland` in some another row would result into `N` ? — Ronak Shah, May 09 '18 at 11:08
I want that if I find Belgium Finland in neighbouring/bordering countries, a Y will be written in column Neighbour in CountryPoints. Otherwise an N will be written — user9737581, May 09 '18 at 11:59

Jaap · Answer 1 · 2018-05-09T12:44:25.953

Two alternative approaches:

1) with base R:

idx <- match(df1$From.country, df2$From.country, nomatch = 0) &
  match(df1$To.Country, df2$To.Country, nomatch = 0)
df1$Neighbour <- c('N','Y')[1 + idx]

2) with data.table:

library(data.table)
setDT(df1)
setDT(df2)

df1[, Neighbour := 'N'][df2, on = .(From.country, To.Country), Neighbour := 'Y'][]

which both give (data.table-output shown):

   From.country To.Country points Neighbour
1:      Belgium    Finland      4         Y
2:      Belgium    Germany      5         Y
3:        Malta      Italy     12         Y
4:        Malta         UK      1         N

score 2 · Answer 2 · answered May 09 '18 at 13:47

Borrowing the idea from this post:

df1$Neighbour  <- duplicated(rbind(df2[, 1:2], df1[, 1:2]))[ -seq_len(nrow(df2)) ]

df1
#   From.country To.Country points Neighbour
# 1      Belgium    Finland      4      TRUE
# 2      Belgium    Germany      5      TRUE
# 3        Malta      Italy     12      TRUE
# 4        Malta         UK      1     FALSE

score 0 · Accepted Answer · answered May 09 '18 at 11:53

What about something like this?

sortpaste <- function(x) paste0(sort(x), collapse = "_");
df1$Neighbour <- apply(df1[, 1:2], 1, sortpaste) %in% apply(df2[, 1:2], 1, sortpaste)
#  From.country To.Country points Neighbour
#1      Belgium    Finland      4      TRUE
#2      Belgium    Germany      5      TRUE
#3        Malta      Italy     12      TRUE
#4        Malta         UK      1     FALSE

Sample data

df1 <- read.table(text =
    "From.country  To.Country points
Belgium       Finland    4
Belgium       Germany    5
Malta         Italy      12
Malta         UK         1", header = T)

df2 <- read.table(text =
    "From.country    To.Country
    Belgium       Finland
    Belgium       Germany
    Malta         Italy", header = T)

Join 2 dataframes together if two columns match

3 Answers3

Sample data