当前位置: 动力学知识库 > 问答 > 编程问答 >

r - Querying time lag in many-to-many table relationship

问题描述:

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!

分享给朋友:
您可能感兴趣的文章:
随机阅读: