I am trying to figure out how to count the number of records in Table B (clinic visits) that occurred in the 6 months before any given event in Table A (survey administration). I am seeking advice about merging or relating these tables in R, and then querying them based on a date column:
Table A contains data on surveys that were administered to each study participant roughly every 6 months (though not the same admin date for each participant). This contains Participant ID and Survey Date, with a 4-5 unique dates for each participant ID:
PartID SurveyDate
12 12/1/12
12 6/8/12
12 12/15/11
12 5/29/11
13 12/15/12
13 6/20/12
13 12/7/11
13 6/15/11
14 11/28/12
14 6/1/12
14 1/1/12
14 6/30/11
Additionally I have a table of clinic visits for each participant and their result (binary) for a certain disease test. Clinic visits occur throughout the year and may happen 0, 1, or many times between each survey administration. At each clinic visit, a test is done and the result is recorded at 1 if positive, 0 if negative.
Part_ID Clinic_date Test_result
1 12 12/1/12 0
2 12 11/30/12 1
3 12 7/1/12 0
4 12 4/1/12 1
5 12 11/15/11 0
6 12 6/15/11 1
7 12 6/5/11 0
8 12 4/1/11 1
9 12 10/15/10 0
10 12 10/13/10 1
11 12 7/15/10 0
12 13 11/30/12 1
13 13 7/1/12 1
14 13 4/1/12 0
15 13 11/15/11 0
16 13 6/15/11 1
17 13 6/5/11 1
18 13 4/1/11 0
19 13 10/15/10 0
20 13 10/13/10 1
21 13 7/15/10 1
22 14 11/30/12 0
23 14 7/1/12 0
24 14 4/1/12 1
25 14 11/15/11 0
26 14 6/15/11 1
27 14 6/5/11 0
I would like to add a column to the survey administration table (table A) showing the number of positive clinic tests (1 in the Test_result column, co could use a sum) for that participant in the 6 months prior to the survey being given. Any advice would be much appreciated!
