我有以下DataFrame,我希望应用一些日期范围计算.我想在日期框架中选择行,其中唯一人的样本之间的日期差异(来自sample_date)小于8周,并保持行具有最早的日期(即第一个样本).
这是一个示例数据集.实际数据集可超过200,000条记录.
labno name sex dob id location sample_date
1 John A M 12/07/1969 12345 A 12/05/2112
2 John B M 10/01/1964 54321 B 6/12/2010
3 James M 30/08/1958 87878 A 30/04/2012
4 James M 30/08/1958 45454 B 29/04/2012
5 Peter M 12/05/1935 33322 C 15/07/2011
6 John A M 12/07/1969 12345 A 14/05/2012
7 Peter M 12/05/1935 33322 A 23/03/2011
8 Jack M 5/12/1921 65655 B 15/08/2011
9 Jill F 6/08/1986 65459 A 16/02/2012
10 Julie F 4/03/1992 41211 C 15/09/2011
11 Angela F 1/10/1977 12345 A 23/10/2006
12 Mark A M 1/06/1955 56465 C 4/04/2011
13 Mark A M 1/06/1955 45456 C 3/04/2011
14 Mark B M 9/12/1984 55544 A 13/09/2012
15 Mark B M 9/12/1984 55544 A 1/01/2012
独特的人是同名和dob.例如John A,James,Mark A和Mark B是独特的人.但是,Mark A具有不同的id值.
我通常使用R作为过程,并根据名称/ dob组合生成数据帧列表,并按sample_date对每个数据帧进行排序.然后,我将使用列表应用函数来确定每个数据帧中第一个和最后一个索引之间的日期差异,如果距离最近一个日期不到8周,则返回最旧的.这需要永远.
我会欢迎一些关于如何使用python / pandas尝试这一点的指示.我开始创建一个名为/ dob / id的MultiIndex.结构看起来像我想要的.我需要做的是尝试应用我在R中使用的一些函数来选择我需要的行.我尝试用df.xs()选择,但我没有走得太远.
这是一个可以轻松加载到pandas中的数据字典(尽管列顺序不同).
{‘dob’: {0: ’12/07/1969′, 1: ’10/01/1964′, 2: ’30/08/1958′, 3:
’30/08/1958′, 4: ’12/05/1935′, 5: ’12/07/1969′, 6: ’12/05/1935′,
7: ‘5/12/1921’, 8: ‘6/08/1986’, 9: ‘4/03/1992’, 10: ‘1/10/1977’,
11: ‘1/06/1955’, 12: ‘1/06/1955’, 13: ‘9/12/1984’, 14:
‘9/12/1984’}, ‘id’: {0: 12345, 1: 54321, 2: 87878, 3: 45454,
4: 33322, 5: 12345, 6: 33322, 7: 65655, 8: 65459, 9: 41211,
10: 12345, 11: 56465, 12: 45456, 13: 55544, 14: 55544},
‘labno’: {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7:
8, 8: 9, 9: 10, 10: 11, 11: 12, 12: 13, 13: 14, 14: 15},
‘location’: {0: ‘A’, 1: ‘B’, 2: ‘A’, 3: ‘B’, 4: ‘C’, 5: ‘A’,
6: ‘A’, 7: ‘B’, 8: ‘A’, 9: ‘C’, 10: ‘A’, 11: ‘C’, 12: ‘C’,
13: ‘A’, 14: ‘A’}, ‘name’: {0: ‘John A’, 1: ‘John B’, 2:
‘James’, 3: ‘James’, 4: ‘Peter’, 5: ‘John A’, 6: ‘Peter’, 7:
‘Jack’, 8: ‘Jill’, 9: ‘Julie’, 10: ‘Angela’, 11: ‘Mark A’,
12: ‘Mark A’, 13: ‘Mark B’, 14: ‘Mark B’}, ‘sample_date’: {0:
’12/05/2112′, 1: ‘6/12/2010′, 2: ’30/04/2012′, 3: ’29/04/2012′,
4: ’15/07/2011′, 5: ’14/05/2012′, 6: ’23/03/2011′, 7:
’15/08/2011′, 8: ’16/02/2012′, 9: ’15/09/2011′, 10:
’23/10/2006’, 11: ‘4/04/2011’, 12: ‘3/04/2011′, 13:
’13/09/2012’, 14: ‘1/01/2012’}, ‘sex’: {0: ‘M’, 1: ‘M’, 2: ‘M’,
3: ‘M’, 4: ‘M’, 5: ‘M’, 6: ‘M’, 7: ‘M’, 8: ‘F’, 9: ‘F’,
10: ‘F’, 11: ‘M’, 12: ‘M’, 13: ‘M’, 14: ‘M’}}