Apply function to a MultiIndex dataframe with pandas/python -

i have following dataframe wish apply date range calculations to. want select rows in date frame the date difference between samples unique persons (from sample_date) less 8 weeks , keep row oldest date (i.e. first sample).

here example dataset. actual dataset can exceed 200,000 records.

labno   name    sex dob         id     location  sample_date 1       john  m   12/07/1969  12345          12/05/2112 2       john b  m   10/01/1964  54321  b         6/12/2010 3       james   m   30/08/1958  87878          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  m   12/07/1969  12345          14/05/2012 7       peter   m   12/05/1935  33322          23/03/2011 8       jack    m   5/12/1921   65655  b         15/08/2011 9       jill    f   6/08/1986   65459          16/02/2012 10      julie   f   4/03/1992   41211  c         15/09/2011 11      angela  f   1/10/1977   12345          23/10/2006 12      mark  m   1/06/1955   56465  c         4/04/2011 13      mark  m   1/06/1955   45456  c         3/04/2011 14      mark b  m   9/12/1984   55544          13/09/2012 15      mark b  m   9/12/1984   55544          1/01/2012 

unique persons same name , dob. example john a, james, mark a, , mark b unique persons. mark has different id values.

i use r procedure , generate list of dataframes based on name/dob combination , sort each dataframe sample_date. use list apply function determine if difference in date between fist , last index within each dataframe return oldest if less 8 weeks recent date. takes forever.

i welcome few pointers how might attempt python/pandas. started making multiindex name/dob/id. structure looks want. need try applying of functions use in r select out rows need. have tried selecting df.xs() not getting far.

here dictionary of data can loaded pandas (albeit different column order).

{'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'}}

i think might looking is

def differ(df):     delta = df.sample_date.diff().abs()  # care magnitude     cond = delta.notnull() & (delta < np.timedelta64(8, 'w'))     return df[cond].max()  delta = df.groupby(['dob', 'name']).apply(differ) 

depending on whether or not want keep people don't have more 1 sample can call delta.dropna(how='all') remove them.

note think you'll need numpy >= 1.7 timedelta64 comparison work correctly, there whole host of problems timedelta64/datetime64 numpy < 1.7.