-1

I have a question about merging two datasets in R with conditions.

The first dataset shows which airport runway (Runway) was used on which date and whether a plane arrived or departed from that runway (A/D).

Dataset 1:

Date      Runway   Arrival/Departure
01-01-08    89      A
01-01-08    120     A
01-01-08    97      D
01-01-09    89      A
01-02-09    92      D
01-02-10    89      A

The second dataset describes the number of complaints per runway and per arrival/departure. For instance, the complaints for the second dataset belong to runway 89 and arrival (A) (though this is not mentioned in the second dataset itself as you can see).

Dataset 2: 

Date       Complaints
01-01-08    12
01-01-09    15
01-02-10    53

Now I want to add the second data set to the first one, again based on date. Moreover, I want the values of the complaints of dataset 2 to be linked with Runway 89 and arrival of dataset 1. The complaints for the other runways and arrival/ departure will thus become NA.

This is an example of the final dataset that I want to create: 

Date       Runway   A/D     Complaints
01-01-08    89      A       12
01-01-08    120     A       NA
01-01-08    97      D       NA
01-01-09    89      A       15
01-02-09    92      D       NA
01-02-10    89      A       53

I thought that this would work with an if statement: if runway 89 and arrival, merge dataset based on date. But I have not obtained the desired result yet and I have no idea how to proceed. Simply merging also did not work, for that will give give complaints for all the runways, but I only want it linked with runway 89 and arrival.

If anyone can help me out, I would really appreciate it!

Jaap
  • 81,064
  • 34
  • 182
  • 193
Veraaa
  • 301
  • 2
  • 6
  • 15
  • a quick fix is to add Runway and A/D to Dataset2? – chinsoon12 Apr 11 '16 at 09:03
  • Thanks for your quick response! The entire dataset consists of more complaint files (so for instance also for Runway 120, A), etc, so I'm not sure if I can do that, because it would still not be properly linked then? – Veraaa Apr 11 '16 at 09:08
  • how do you know that Dataset2 is for 89, A? – chinsoon12 Apr 11 '16 at 09:36
  • I have obtained a few open source data files dat specify which complaint dataset belongs to which runway and arrival/departure. – Veraaa Apr 11 '16 at 10:05
  • Possible duplicate: http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right – Jaap Apr 11 '16 at 10:13
  • So when you read in each dataset2, you must have specify each runway and a/d each time. You could have reconstructed your output without dataset1 – chinsoon12 Apr 11 '16 at 10:14

1 Answers1

0

I had this in mind:

Form data

df1 <- data.frame(Date=c("01-01-08","01-01-08","01-01-08","01-01-09","01-02-09","01-02-10"),Runway=c(89,120,97,89,92,89),AD=c("A","A","D","A","D","A"))
df2 <- data.frame(Date=c("01-01-08","01-01-09","01-02-10"),Complaints=c(12,15,53))

Merge by date

df3 <- merge(df1,df2,by="Date",all.x=T)

Simple loop for removing irrelevant values

for(i in 1:6)
  {
  if(df3$Runway[i]==89)df3Complaints[i] else df3$Complaints[i] <- NA
}

You can also include the Arrival/Departure variable in the loop if your original working data contains departures from gate 89 and you don't want those to be associated with the complaints:

for(i in 1:6)
  {
  if(df3$Runway[i]==89 && df3$AD[i]=="A")df3Complaints[i] else df3$Complaints[i] <- NA
}
Olli J
  • 649
  • 2
  • 7
  • 23