CODEWITHSUNDEEP
r
Klllmmm
Klllmmm

Reputation: 136

Join five tables if three variables are matched

Cost 

            Name  Class     Status   Cost
      Page, Lisa     11  Full Time  54550
      Page, Lisa     10   Contract  26795
  Taylor, Hector      7  Full Time  42540
Dawson, Jonathan     11  Full Time  35680
Dawson, Jonathan      6  Full Time  72830
Dawson, Jonathan      5   Contract  60830
     Pratt, Erik      8  Full Time  83000

Subject 

            Name  Class     Status  Subjects
      Page, Lisa     11  Full Time     Maths
      Page, Lisa     10   Contract   Science
  Taylor, Hector      7  Full Time   Science
Dawson, Jonathan     11  Full Time   English
Dawson, Jonathan      6  Full Time     Maths
Dawson, Jonathan      5   Contract     Maths
     Pratt, Erik      8 Full-Time   Hinduism

ComputerNo

            Name  Class     Status  ComputerNo
      Page, Lisa     11  Full Time      115005
      Page, Lisa     10   Contract      450005
  Taylor, Hector      7  Full Time      380025
Dawson, Jonathan     11  Full Time      152253
Dawson, Jonathan      6  Full Time      125523
Dawson, Jonathan      5   Contract      485125

LicenseNo

            Name  Class     Status  LicenseNo
      Page, Lisa     11  Full Time   HJ452632
      Page, Lisa     10   Contract   HJ452634
  Taylor, Hector      7  Full Time   HJ352236
Dawson, Jonathan     11  Full Time   HJ456236
Dawson, Jonathan      6  Full Time   HJ456230
Dawson, Jonathan      5   Contract   HJ456232
     Pratt, Erik      8  Full Time   HJ130055

Country 

            Name  Class     Status    Country
      Page, Lisa     11 Full-Time   Hong Kong
      Page, Lisa     10   Contract  Hong Kong
  Taylor, Hector      7 Full-Time UK
Dawson, Jonathan     11 Full-Time USA
Dawson, Jonathan      6 Full-Time USA
Dawson, Jonathan      5   Contract        USA
     Pratt, Erik      8 Full-Time Japan

Resultant table I'm expecting is like this CombinedDataSet 

            Name  Class     Status   Cost  Subjects  ComputerNo  LicenseNo    Country
      Page, Lisa     11  Full Time  54550     Maths      115005   HJ452632  Hong Kong
      Page, Lisa     10   Contract  26795   Science      450005   HJ452634  Hong Kong
  Taylor, Hector      7  Full Time  42540   Science      380025   HJ352236         UK
Dawson, Jonathan     11  Full Time  35680   English      152253   HJ456236        USA
Dawson, Jonathan      6  Full Time  72830     Maths      125523   HJ456230        USA
Dawson, Jonathan      5   Contract  60830     Maths      485125   HJ456232        USA
     Pratt, Erik      8  Full Time  83000  Hinduism        -NA-   HJ130055      Japan

As above, I have five data tables, which I want to make one data set through joining.

I want to match 3 variables(Name, Class & Status) in each data table & then join. If the criteria are not fulfilled in a particular table then I would like to see that either in the final table. ( as a blank cell or through a "-NA-" remark).

Upvotes: 0

Views: 51

Answers (2)

jeremycg
jeremycg

Reputation: 24945

You can do it all at once using Reduce:

Reduce(function(x, y) merge(x, y, all = TRUE, 
    by = c("Name", "Class", "Status")), list(cost, subject, computerNo, licenseNo, country))

Upvotes: 1

Parfait
Parfait

Reputation: 107567

Use the base R merge() function and list in the by() the multiple joining columns and specify all=TRUE to retain records in both right and left tables:

finaldf <- merge(cost, subject, by=c("Name", "Class", "Status"), all=TRUE)
finaldf <- merge(finaldf, computerNo, by=c("Name", "Class", "Status"), all=TRUE)
finaldf <- merge(finaldf, licenseNo, by=c("Name", "Class", "Status"), all=TRUE)
finaldf <- merge(finaldf, country, by=c("Name", "Class", "Status"), all=TRUE)

Upvotes: 1

Related Questions