Join three tables using lookup

Question

I need to join 3 tables:

Table1:(5325 x 6)
POLICY  YEAR   Prem Loss LRtot    LRPP
217570  2008   3651    0     0       0
.
.


Table2: (216 x 1)
POLICY
217570
251440
290102
.
.
655825

Table3: (11551 x 4)
YEAR  STATE POLICY  DIST_MIL
2011   TN    217570    11.0
2013   TN    217570    10.7
2016   TN    217570    10.7
.
.
.

I want to obtain the output like this (DIST_MIL is 2016 value):

Output Table:
POLICY  YEAR   Prem Loss LRtot    LRPP DIST_MIL
217570  2008   3651    0     0       0    10.7

How can I achieve this in R:?

There are a myriad of ways to join tables. Search the forums. For a three way join, most require the joining of 1 and 2, and then taking that result and joining with 3. — akaDrHouse, Mar 15 '17 at 16:00

akaDrHouse · Accepted Answer · 2017-03-15T18:35:43.250

0

A dplyr solution:

library(dplyr)
final3_join <- Table1 %>% inner_join(Table2,by="POLICY") %>%
inner_join(Table3,by="POLICY")

If you only want records that were in Table1 to be in the final table, then replace inner_join with left_join.

Edited per your comment. You can filter the data on the way in the last join.

final3_join <- Table1 %>% inner_join(Table2,by="POLICY") %>%
    inner_join(filter(Table3,YEAR==2016),by="POLICY")

Note: if year is defined as character then you would put it in quotes, "2016"

edited Mar 15 '17 at 18:35

answered Mar 15 '17 at 16:03

akaDrHouse

2,190
2
20
29

1

Thanks Frank for the feedback. – akaDrHouse Mar 15 '17 at 16:10
Thanks for the quick reply. This is correct but how can you modify the code to merge with just by 2016 DIST_MIL in Table 3. Your above code results shows all three distance value for the same policy. I just need the DIST_MIL value for year 2016 only. – user3408139 Mar 15 '17 at 16:18
@user3408139 I have amended my response for you. – akaDrHouse Mar 15 '17 at 20:51
Thanks a bunch. That's what I wanted. – user3408139 Mar 15 '17 at 21:23

Join three tables using lookup

1 Answers1