Matching Pairs for R Dataframes

user_13275613 0
Viewed 32

I have a data frame that contains the career records of employees in different offices of a large corporations. I want to identify every pair of employees who have shared working experience in a same office. My data frame structure looks like below

    Year   Office     Employee_Name
    2011   Logistics  Henry
    2012   Logistics  Henry
    2013   HR         Henry
    
    2012   Marketing  Peter
    2013   HR         Peter
    2014   HR         Peter
    2015   HR         Peter

    2010   Logistics  Bob
    2011   Logistics  Bob
    2012   Logistics  Bob

In the above sample, Henry and Peter worked together in HR in 2013. Henry also worked with Bob in logistics in 2011 and 2012. I want the final results can be something like:

Year_of_shared_experience Person_A Person_B
1                         Henry    Peter
2                         Henry    Bob

The order of Person_A and Person_B does not matter (i.e., it can be Henry in Person_A or it can be Peter in Person_A column). Thanks!

dataframe r
2 Answers

You could merge the table with itself (i.e., a "self-join") and then filter out duplicate entries:

# read data
dat = "
Year   Office     Employee_Name
2011   Logistics  Henry
2012   Logistics  Henry
2013   HR         Henry
2012   Marketing  Peter
2013   HR         Peter
2014   HR         Peter
2015   HR         Peter
2010   Logistics  Bob
2011   Logistics  Bob
2012   Logistics  Bob"
dat = read.table(text=dat, header=TRUE)

# self-join
dat = merge(dat, dat, all=TRUE, by=c("Year", "Office"))

# filter out duplicates
dat = dat[dat$Employee_Name.x < dat$Employee_Name.y,]

dat 
#>    Year    Office Employee_Name.x Employee_Name.y
#> 4  2011 Logistics             Bob           Henry
#> 8  2012 Logistics             Bob           Henry
#> 12 2013        HR           Henry           Peter
user_3423310

An option in tidyverse

library(dplyr)
full_join(dat, dat, by = c("Year", "Office")) %>%
         filter(Employee_Name.x < Employee_Name.y)
user_37322710
Related