<div dir="ltr">I'm trying to figure out how to merge two data tables using the dates in both. One table a set of people, which has the to and from dates and their address at each place they've lived. The other has their work history, also with to and from dates. Obviously, there isn't a one-to-one relationship; individuals may have several jobs while staying in the same place, several homes over the course of a job, and any sort of overlapping you can imagine. Both tables are reasonably large; the residences has about 950k rows, and the employment has about 1.2M rows.<div>
<br></div><div>To give you a bit of flavor, the first ten rows of each:</div><div><div>> work.history[1:10, list(icrdn, fromdate, todate, state, postalcode)]</div><div> icrdn fromdate todate state postalcode</div>
<div> 1: 145 Apr 1988 Jan 1990 FL 33432</div><div> 2: 145 Jan 1990 Jan 1997 FL 33432</div><div> 3: 145 Jan 1997 Dec 2011 FL 33444</div><div> 4: 145 Jan 1997 Dec 2011 FL 33444</div><div>
5: 145 Jan 1997 Dec 2011 FL 33444</div><div> 6: 170 Oct 1983 Apr 2002 NE 68114</div><div> 7: 170 Sep 1972 Dec 2011 IL 60443</div><div> 8: 170 Sep 1972 Dec 2011 IL 61821-3066</div><div> 9: 183 Aug 2000 Dec 2011 GA 30305</div>
<div>10: 183 Aug 2000 Dec 2011 GA 30305</div><div>> residences[1:10]</div><div> icrdn fromdate todate state postalcode</div><div> 1: 145 10/1992 03/2004 FL 33432</div><div> 2: 145 03/2004 FL 33487</div>
<div> 3: 170 09/1995 IL 61821</div><div> 4: 183 05/1993 08/2000 GA 30342</div><div> 5: 183 08/2000 09/2001 GA 30342</div><div> 6: 183 09/2001 08/2004 GA 30305</div><div>
7: 183 08/2004 GA 30073</div><div> 8: 183 02/2005 GA 30342</div><div> 9: 183 06/2006 GA 30075</div><div>10: 183 07/1974 05/1993 GA 30338</div></div><div>
<br></div><div>The 'icrdn' column is an identifier unique to each person.</div><div><br></div><div>What I'm looking for is a data table with a row for each residence-job pair. Any residence that doesn't have a job in the sample can be safely dropped, and vice-versa.</div>
<div><br></div><div>Thanks in advance for any help anyone can offer.<br clear="all"><div>-------<br>Nathaniel Graham<br><a href="mailto:npgraham1@gmail.com" target="_blank">npgraham1@gmail.com</a><br><a href="mailto:npgraham1@uky.edu" target="_blank">npgraham1@uky.edu</a></div>
</div></div>