Combining data sets with different IDs?

Question

I'm learning R and trying to do a comparison of MLB player salaries vs. WAR

One dataset contains all the salaries and the other contains WAR information, and they have their variables labelled differently. For example, each of the team names in one set is an abbreviation (ATL, NYY, HOU vs. Braves, Yankees, Astros). With that example, I'm sure I could find a way to make ATL == Braves, but with all the players that doesn't seem so easy.

The players are labelled in one dataset as the first 5 letters of the last name, first two letters of the first name, and then two numbers to differentiate matches. The other set simply has the names. An example would be Bryce Harper vs. harpebr03.

There's no apparent way to sort via alphabetical order and matching up each player's variable ID would take way too much time. Is there an easier way to link these differently named variables in R?

Answer 1

Here is some code that deals with the second part of your question regarding matching names. (BTW including sample data always helps)

require(dplyr)  ## for join

# create two example dataframes 
d1 <- data.frame(player_first_last=c("Joe Smith", "Jack Johnson", "A Jones", "Slow Mo"), d1_value=c(1,2,3,4))
d2 <- data.frame(player_last_first2=c("SmithJo","JohnsJa", "MoSl"), d2_value=c(11,12,13))

# first two letters of first name using substr, and trimming whitespace
d1$player_first2 <- trimws(substr(d1$player_first, 1, 2 ))

# last name - regex capturing everything after the first whitespace
# this is a "regular expressions" 
d1$player_last <- gsub(".+\\s(.+)", "\\1", d1$player_first_last)

# concatenate last 5 and first 2 using paste0
d1$player_last_first2 <- paste0(substr(d1$player_last,1,5), d1$player_first2)

# join on constructed name -- full_join in case there are differences
d3 <- full_join(d1, d2, by="player_last_first2")

Results in d3

  player_first_last d1_value player_first2 player_last player_last_first2 d2_value

1         Joe Smith        1            Jo       Smith            SmithJo        1
2      Jack Johnson        2            Ja     Johnson            JohnsJa        2
3           A Jones        3             A       Jones             JonesA       NA
4           Slow Mo        4            Sl          Mo               MoSl        3