"Select query equivalent" in R to select from one data frame and paste values to the second frame accordingly

I have two data frames:

temp <- data.frame(
  team1 = c("Chennai Super Kings","Deccan Chargers","Delhi Daredevils"),
  team2 = c("Mumbai Indians","Royal Challengers Bangalore","Gujarat Lions")
)

teamdata <- data.frame(
  teamname=c("Chennai Super Kings","Deccan Chargers","Delhi Daredevils",
             "Mumbai Indians","Royal Challengers Bangalore","Gujarat Lions"),
  matchesplayed = c("100","200","300","400","500","600"),
  matcheswon = c("50","100","150","200","250","300")
)

In the temp data frame I want to add variables such as team1matchesplayed and team2matchesplayed or team1matcheswon and team2matcheswon according to the name of the team in variables team1 and team2 of the temp dataframe. The values should be populated from teamdata data frame. New columns should be generated in the temp data frame.

P.S: This is my first question on here and may not be the best representation. Apologies: Sorry for attaching images earlier. Thank you for pointing it out.

2 answers

  • answered 2017-06-17 17:55 Parfait

    Simply merge twice on both team1 and team2 respectively:

    # NESTED MERGE
    mdf <- merge(merge(temp, teamdata, by.x=c("team1"), by.y=c("teamname"), all.x=TRUE),
                 teamdata, by.x=c("team2"), by.y=c("teamname"), all.x=TRUE)
    
    # RENAME COLUMNS
    mdf <- setNames(mdf, c("team2", "team1", "team1_matchesplayed", "team1_matcheswon", 
                           "team2_matchesplayed", "team2_matcheswon"))
    
    # REORDER COLUMNS
    mdf <- mdf[c("team1", "team2", "team1_matchesplayed", "team2_matchesplayed", 
                 "team1_matcheswon", "team2_matcheswon")]
    mdf
    
    #                 team1                       team2 team1_matchesplayed team2_matchesplayed team1_matcheswon team2_matcheswon
    # 1    Delhi Daredevils               Gujarat Lions                 300                 600              150              300
    # 2 Chennai Super Kings              Mumbai Indians                 100                 400               50              200
    # 3     Deccan Chargers Royal Challengers Bangalore                 200                 500              100              250
    

  • answered 2017-06-17 17:55 Ajay Ohri

    > library(sqldf)
    Loading required package: gsubfn
    Loading required package: proto
    Loading required package: RSQLite
    
    > temp2=sqldf("select temp.*,matchesplayed as team1matchesplayed,matcheswon as team1matcheswon from temp,teamdata where temp.team1=teamdata.teamname")
    > temp2
                    team1                       team2 team1matchesplayed
    1 Chennai Super Kings              Mumbai Indians                100
    2     Deccan Chargers Royal Challengers Bangalore                200
    3    Delhi Daredevils               Gujarat Lions                300
      team1matcheswon
    1              50
    2             100
    3             150
    > temp3=sqldf("select temp2.*,matchesplayed as team2matchesplayed,matcheswon as team2matcheswon from temp2,teamdata where temp2.team2=teamdata.teamname")
    > temp3
                    team1                       team2 team1matchesplayed
    1 Chennai Super Kings              Mumbai Indians                100
    2     Deccan Chargers Royal Challengers Bangalore                200
    3    Delhi Daredevils               Gujarat Lions                300
      team1matcheswon team2matchesplayed team2matcheswon
    1              50                400             200
    2             100                500             250
    3             150                600             300