0

I have a dataframe in R with an ID column and a year column. I also have another dataframe with stats for given ID year combinations. For example

TableA:

ID YEAR
aaa 2010
bbb 2009
ccc 2014

TableB: 

ID YEAR
aaa 2008
aaa 2009
aaa 2010
aaa 2011
bbb 2009
ccc 2014
ccc 2015
ccc 2016

(Columns3-5: Various stats)

I want to remove any row in TableB (in R) where the year is GREATER THAN the year for the ID in table 1. So the result will remove {aaa,2011;ccc,2015;ccc,2016}.

This is what I have right now:

`
TableC = TableB
for (row in nrow(TableA)){
  ID = as.character(TableA[row,'ID'])
  year = TableA[row,'YEAR']
  TableC = TableC[! (TableC$ID==ID & TableC$YEAR>year),]
}`

The problem is that TableC is resetting through each iteration of the loop, so my final result is only removing {ccc,2015;ccc,2016}. Any ideas on how I should alter my code to get the result I'm looking for?

thelatemail
  • 91,185
  • 12
  • 128
  • 188
Jeremy Losak
  • 45
  • 5
  • Welcome to Stackoverflow. Please provide a [reproducible example](http://stackoverflow.com/q/5963269/3250126). `dput()` should help with that. – loki Nov 06 '16 at 22:30
  • You could probably just do `merge(A,B, by="ID")` and then subset it after that – thelatemail Nov 06 '16 at 22:37
  • 1
    Man I should have thought of that.... simply subset any row in the newly merged table where B$YEAR > A$YEAR. Thanks for the help – Jeremy Losak Nov 06 '16 at 22:39

0 Answers0