0

I have two data sets. One with only a registration number (A), and the other with a list of registration numbers and the type of model next to the registration number(B).

A

A

ircraft.reg.num Action  Description Service.Type    C/O Order.Type  Created.on  Actual.release.date
5Y-BX   Re-Assembling of Aircraft           C/O ZS08    40512   40532
5Y-BX   Re-Assembling of Aircraft           C/O ZS08    40512   40532
5Y-BX   Re-Assembling of Aircraft           C/O ZS08    40512   40532
5Y-BX       Dual Control Collective Lever           ZS08    40497   40550
5Y-BX       Dual Control Collective Lever           ZS08    40497   40550
5Y-BX   Perform SB  63-10 MGB plug          ZS08    40548   40550
5Y-BX   Perform SB  63-10 MGB plug          ZS08    40548   40550
5Y-BX   Defect  Pitot static cover burnt            ZS08    40497   40550
5Y-CD   Airworthiness Inspection & Test             ZS08    40711   40711
5Y-CD   Airworthiness Inspection & Test             ZS08    40711   40711
5Y-CD   Airworthiness Inspection & Test             ZS08    40711   40711

B

Reg.    Aircraft.Model
5O-MV   AS 355 
5O-Unknown  SA 318 
5O-Unknown  SA 318 
5T-Unlnown  SA 318
5T-BF   BO 105 
5T-BW   AS 350 
5T-BX   AS 350 
5T-BY   AS 350 
5T-CA   BK 117 
5T-CC   AS 350 
5T-CD   AS 350 
5T-DK   AS 350 
5T-DS   AS 350 
5T-DS   AS 350

I want know if the registration number in A equals to the registration number in B, what is the type of model?

I currently use:

if(A$Aircraft.reg.num -> B$Reg.){
model <- reg.no$Aircraft.Model
}

But it is not working because my data.frames aren't even in length.

Any suggestions to how I can get the result without using an "if" function?

Thanks, Carmen

smci
  • 32,567
  • 20
  • 113
  • 146
Carmen
  • 117
  • 1
  • 7
  • 1
    you may need `merge` rather than an if statement – Cath Oct 14 '15 at 10:35
  • 1
    Make this a **[reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)**. Use `dput()` to dump the dataframes so we can actually write code. – smci Oct 14 '15 at 10:38

2 Answers2

0

You can use join function in plyr package to use is as a kind of VLOOKUP in excel. 

library(plyr)
newdf <- join(A,B[,1], by = "aircraft.reg.num", type = "left join")

the code above should give you same id number if it exists and NA if it doesn't. You can find more examples in the link below with a similar question.

How to do vlookup and fill down (like in Excel) in R?

Community
  • 1
  • 1
Alihan Zıhna
  • 95
  • 2
  • 8
0

could also use sqldf package. It allows SQL style queries on dataframes.

library(sqldf)
df <- sqldf('SELECT A."aircraft.reg.num", B."Aircraft.Model"
              FROM A INNER JOIN B ON A."aircraft.reg.num" = B."Reg."')

You could also use LEFT JOIN if you want to see which Aircraft registrations are missing in B.

Note: Column names containing a period must be surrounded with "..." or with [...] as period is an SQL operator.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
amwill04
  • 1,330
  • 1
  • 11
  • 18