r conditional time range search

user_4368782 0
Viewed 29

I have two datasets

Dataset A, this contains hog's Weight and when it was measured

Row# ID   Year   Weight
1    1    2011   27.59
2    1    2012   36.5
3    1    2015   40.29
4    2    2018   56.9
5    3    2002   26.1
6    3    2009   86.8

Dataset B, farmers revenue and the year revenue was reported. Please note the ID in the farmers dataset is the same as ID in the 1st DatasetA (hog) , this is because hogIDs are unique to that farmer.

ID    Year  Revenue
3     2002  5
1     2013  3
1     2012  1
2     NA    NA

My goal is to create a final dataset that retains only rows from DatasetA (hog) that have farmers income within 2 years and before reported weight. Sorry this is complicated. What I mean by income within 2 years and before reported weight is this, for example consider ID 1 from Dataset A, this hog (ID 1) has three recorded weights 2011, 2012, 2015. The corresponding farmers income (Dataset B, ID 1) is reported on 2012 and 2013.

In the final dataset for hogID 1, we will exclude row#1 from Dataset A because for this hogID 1 the weight was recorded on 2011, but the corresponding farmers income in Dataset B is recorder on 2012 and 2013 which is after the recorded weight on 2011. We want only rows from DatasetA (hog weight) with weights recorded after the reported Income not before and the Income should be within 2 years of weight. Sorry if my after and before criteria is confusing. The final dataset would look like this

    ID   Year   Weight  Status
    1    2011   27.59   Exclude, no Income before recorded weight and within 2 years range 
    1    2012   36.5    Include, Income reported on 2012 
    1    2015   40.29   Include, Income reported on 2013 
    2    2018   56.9    Exclude, no Income recorded
    3    2002   26.1    Include, Income reported on 2002
    3    2009   86.8    Exclude, no Income before recorded weight and within 2 years range

I suppose I could do this with for loops and nested ifelse statements, I am interested in an efficient approach. Any suggestions or help is much appreciated . Thanks in advance.

conditional-statements r
1 Answers

From the B dataset you can get the max and min Year values, join the data with A by 'ID' and keep only those rows that are in the required range.

library(dplyr)

B %>%
  group_by(ID) %>%
  summarise(min_year = min(Year), 
            max_year = max(Year)) %>%
  na.omit() %>%
  left_join(A, by = 'ID') %>%
  filter(Year >= min_year & Year <= max_year + 2)

#    ID min_year max_year   Row  Year Weight
#  <int>    <int>    <int> <int> <int>  <dbl>
#1     1     2012     2013     2  2012  36.5 
#2     1     2012     2013     3  2015  40.29
#3     3     2002     2002     5  2002  26.1

You can later drop the columns that you don't need from the result.

user_39629140
Related