Reputation: 2067
I have two datasets I am trying to join together.
df1
looks like:
ID date_f
1 4281 2019-02-21
2 1108827 2004-03-15
3 6201 2012-02-27
4 310158 2010-03-01
5 711065 2016-02-25
6 314808 2003-03-11
7 45012 2004-05-12
8 745732 2014-11-21
9 1458891 2013-10-28
10 316206 2007-05-30
Whereas df2
looks like:
ID date year
1 6201 1999-12-31 1999
2 6201 2000-12-31 2000
3 6201 2001-12-31 2001
4 6201 2002-12-31 2002
5 6201 2003-12-31 2003
6 6201 2004-12-31 2004
7 6201 2017-12-31 2017
8 6201 2005-12-31 2005
9 6201 2006-12-31 2006
10 6201 2007-12-31 2007
11 6201 2008-12-31 2008
12 6201 2009-12-31 2009
13 6201 2010-12-31 2010
14 6201 2011-12-31 2011
15 6201 2012-12-31 2012
16 6201 2013-12-31 2013
17 6201 2014-12-31 2014
18 6201 2015-12-31 2015
19 6201 2016-12-31 2016
20 6201 2018-12-31 2018
I am trying to join them (the dates do not match):
Method:
Join by ID
and when date < date_f
Expected Output (Using the first 5 observations from df1
:
ID date_f date year
1 4281 2019-02-21 2018-12-31 2018
2 1108827 2004-03-15 2003-12-31 2003
3 6201 2012-02-27 2011-12-31 2011
4 310158 2010-03-01 2009-12-31 2009
5 711065 2016-02-25 2015-03-31 2014
For example row 2 above has a date_f
in df1
of 2004-03-15
one way would be to merge it with the year()
and it would get joined with 2004-12-31
- however this date is after the date in df1
. So I am trying to merge it with the previous date which is 2003-12-31
.
The final row 5 would have been joined with 2016-03-31
however the date_f
is less than this date. 2016-02-25
in date_f
< 2016-03-31
in date
.
Data
df1 <- structure(list(ID = c(4281L, 1108827L, 6201L, 310158L, 711065L,
314808L, 45012L, 745732L, 1458891L, 316206L), date_f = c("2019-02-21",
"2004-03-15", "2012-02-27", "2010-03-01", "2016-02-25", "2003-03-11",
"2004-05-12", "2014-11-21", "2013-10-28", "2007-05-30")), row.names = c(NA,
-10L), .internal.selfref = <pointer: 0x0000000002511ef0>, class = "data.frame")
Data 2
df2 <- structure(list(ID = c(6201L, 6201L, 6201L, 6201L, 6201L, 6201L,
6201L, 6201L, 6201L, 6201L, 6201L, 6201L, 6201L, 6201L, 6201L,
6201L, 6201L, 6201L, 6201L, 6201L, 314808L, 314808L, 314808L,
314808L, 314808L, 314808L, 314808L, 314808L, 314808L, 314808L,
314808L, 314808L, 314808L, 314808L, 314808L, 314808L, 314808L,
314808L, 314808L, 314808L, 45012L, 45012L, 45012L, 45012L, 45012L,
45012L, 45012L, 45012L, 45012L, 45012L, 45012L, 45012L, 45012L,
45012L, 45012L, 45012L, 45012L, 45012L, 45012L, 45012L, 316206L,
316206L, 316206L, 316206L, 316206L, 316206L, 316206L, 316206L,
316206L, 316206L, 316206L, 316206L, 316206L, 316206L, 316206L,
316206L, 316206L, 310158L, 310158L, 310158L, 310158L, 310158L,
310158L, 310158L, 310158L, 310158L, 310158L, 310158L, 310158L,
310158L, 310158L, 310158L, 310158L, 310158L, 310158L, 310158L,
310158L, 745732L, 745732L, 745732L, 745732L, 745732L, 745732L,
745732L, 745732L, 745732L, 745732L, 745732L, 745732L, 745732L,
745732L, 745732L, 745732L, 745732L, 745732L, 745732L, 745732L,
745732L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L,
1458891L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L,
1458891L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L, 1458891L,
4281L, 4281L, 4281L, 4281L, 4281L, 4281L, 4281L, 711065L, 711065L,
711065L, 711065L, 711065L, 711065L, 711065L, 711065L, 711065L,
711065L, 711065L, 711065L, 711065L, 711065L, 711065L, 711065L,
711065L, 711065L, 1108827L, 1108827L, 1108827L, 1108827L, 1108827L,
1108827L, 1108827L, 1108827L, 1108827L, 1108827L, 1108827L, 1108827L,
1108827L, 1108827L, 1108827L, 1108827L, 1108827L, 1108827L),
date = c("1999-12-31", "2000-12-31", "2001-12-31", "2002-12-31",
"2003-12-31", "2004-12-31", "2017-12-31", "2005-12-31", "2006-12-31",
"2007-12-31", "2008-12-31", "2009-12-31", "2010-12-31", "2011-12-31",
"2012-12-31", "2013-12-31", "2014-12-31", "2015-12-31", "2016-12-31",
"2018-12-31", "1999-12-31", "2000-12-31", "2001-12-31", "2002-12-31",
"2003-12-31", "2004-12-31", "2005-12-31", "2006-12-31", "2007-12-31",
"2008-12-31", "2009-12-31", "2010-12-31", "2011-12-31", "2012-12-31",
"2013-12-31", "2014-12-31", "2015-12-31", "2016-12-31", "2017-12-31",
"2018-12-31", "1999-12-31", "2000-12-31", "2001-12-31", "2002-12-31",
"2003-12-31", "2004-12-31", "2005-12-31", "2006-12-31", "2007-12-31",
"2008-12-31", "2009-12-31", "2010-12-31", "2011-12-31", "2012-12-31",
"2013-12-31", "2014-12-31", "2015-12-31", "2016-12-31", "2017-12-31",
"2018-12-31", "1999-12-31", "2000-12-31", "2001-12-31", "2002-12-31",
"2003-12-31", "2004-12-31", "2005-12-31", "2006-12-31", "2007-12-31",
"2008-12-31", "2009-12-31", "2010-12-31", "2011-12-31", "2012-12-31",
"2013-12-31", "2014-12-31", "2015-12-31", "1999-12-31", "2000-12-31",
"2001-12-31", "2002-12-31", "2003-12-31", "2004-12-31", "2005-12-31",
"2006-12-31", "2007-12-31", "2008-12-31", "2009-12-31", "2010-12-31",
"2011-12-31", "2012-12-31", "2013-12-31", "2014-12-31", "2015-12-31",
"2016-12-31", "2017-12-31", "2018-12-31", "1999-01-31", "2000-01-31",
"2001-01-31", "2002-01-31", "2003-01-31", "2004-01-31", "2005-01-31",
"2006-01-31", "2007-01-31", "2008-01-31", "2009-01-31", "2010-01-31",
"2011-01-31", "2012-01-31", "2013-01-31", "2014-01-31", "2015-01-31",
"2016-01-31", "2017-01-31", "2018-01-31", "2019-01-31", "1999-12-31",
"2000-12-31", "2001-12-31", "2002-12-31", "2003-12-31", "2004-12-31",
"2005-12-31", "2006-12-31", "2007-12-31", "2008-12-31", "2009-12-31",
"2010-12-31", "2011-12-31", "2012-12-31", "2013-12-31", "2014-12-31",
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2012-12-31",
"2013-12-31", "2014-12-31", "2015-12-31", "2016-12-31", "2017-12-31",
"2018-12-31", "1999-03-31", "2000-03-31", "2001-03-31", "2002-03-31",
"2003-03-31", "2004-03-31", "2005-03-31", "2006-03-31", "2007-03-31",
"2008-03-31", "2009-03-31", "2010-03-31", "2011-03-31", "2012-03-31",
"2013-03-31", "2014-03-31", "2015-03-31", "2016-03-31", "2001-12-31",
"2002-12-31", "2003-12-31", "2004-12-31", "2005-12-31", "2006-12-31",
"2007-12-31", "2008-12-31", "2009-12-31", "2010-12-31", "2011-12-31",
"2012-12-31", "2013-12-31", "2014-12-31", "2015-12-31", "2016-12-31",
"2017-12-31", "2018-12-31"), year = c(1999L, 2000L, 2001L,
2002L, 2003L, 2004L, 2017L, 2005L, 2006L, 2007L, 2008L, 2009L,
2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2018L, 1999L,
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L,
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L,
2018L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L,
2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L,
2016L, 2017L, 2018L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L,
2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L,
2014L, 2015L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L,
2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L,
2015L, 2016L, 2017L, 2018L, 1998L, 1999L, 2000L, 2001L, 2002L,
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L,
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 1999L, 2000L,
2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L,
2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L,
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 1998L, 1999L,
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L,
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2001L, 2002L,
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L,
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L)), row.names = c(NA,
-181L), .internal.selfref = <pointer: 0x0000000002511ef0>, class = "data.frame")
Upvotes: 1
Views: 62
Reputation: 887291
An easier option is with rolling joins from data.table
library(data.table)
setDT(df2)[, date_f := date][df1, on = .(ID, date_f), roll = Inf]
# ID date year date_f
#1: 4281 2018-12-31 2018 2019-02-21
#2: 1108827 2003-12-31 2003 2004-03-15
#3: 6201 2011-12-31 2011 2012-02-27
#4: 310158 2009-12-31 2009 2010-03-01
#5: 711065 2015-03-31 2014 2016-02-25
#6: 314808 2002-12-31 2002 2003-03-11
#7: 45012 2003-12-31 2003 2004-05-12
#8: 745732 2014-01-31 2013 2014-11-21
#9: 1458891 2012-12-31 2012 2013-10-28
#10: 316206 2006-12-31 2006 2007-05-30
NOTE: Here, we assume the 'date/date_f' columns to be Date
class
Upvotes: 1
Reputation: 1248
I think the code below will give you what you need.
data <- df2 %>%
inner_join(df1, by="ID") %>%
filter(date < date_f) %>%
group_by(ID) %>%
summarise(maxDate = max(date)) %>%
inner_join(df1, by="ID")
Upvotes: 1