title: Pandas count consecutive date observations within groupby object
body: <p>This is an example of the data frame i'm working with:</p>
  
  <pre><code>d = {
  'item_number':['bdsm1000', 'bdsm1000', 'bdsm1000', 'ZZRWB18','ZZRWB18', 'ZZRWB18', 'ZZRWB18', 'ZZHP1427BLK', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1427', 'ZZHP1414', 'ZZHP1414', 'ZZHP1414', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115WNTR', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE', 'WRM115SCFRE'],
  'Comp_ID':[2454, 2454, 2454, 1395, 1395, 1395, 1395, 3378, 1266941, 660867, 43978, 1266941, 660867, 43978, 1266941, 660867, 43978, 1266941, 660867, 43978, 43978, 43978, 43978, 1197347907, 70745, 4737, 1197347907, 4737, 1197347907, 70745, 4737, 1197347907, 70745, 4737, 1197347907, 4737, 1197487704, 1197347907, 70745, 23872, 4737, 1197347907, 4737, 1197487704, 1197347907, 23872, 4737, 1197487704, 1197347907, 70745],
  'date':['2016-11-22', '2016-11-20', '2016-11-19', '2016-11-22', '2016-11-20', '2016-11-19', '2016-11-18', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-20', '2016-11-20', '2016-11-20', '2016-11-19', '2016-11-19', '2016-11-19', '2016-11-18', '2016-11-18', '2016-11-18', '2016-11-22', '2016-11-20', '2016-11-19', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-21', '2016-11-21', '2016-11-20', '2016-11-20', '2016-11-20', '2016-11-19', '2016-11-19', '2016-11-19', '2016-11-18', '2016-11-18', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-22', '2016-11-21', '2016-11-21', '2016-11-20', '2016-11-20', '2016-11-20', '2016-11-20', '2016-11-19', '2016-11-19', '2016-11-19']}
  
  df = pd.DataFrame(data=d)
  df.date = pd.to_datetime(df.date)
  </code></pre>
  
  <p>I'd like to count consecutive observations starting from 2016-11-22 that there are grouped by Comp_ID and item_number.</p>
  
  <p>Essentially, what I am looking to do, is count how many days in a row there is an observation counting back from todays date for each Comp_ID and item_number. (this example was put together on the 22nd of Nov) Consecutive observations observed weeks/ days prior to today are not relevant. Only sequences like today... yesterday... the day before yesterday... and so on are relevant. </p>
  
  <p>I got this to work on a smaller sample, but it seems to be getting tripped up on a larger data-set. </p>
  
  <p>Here is the code for the smaller sample. I need to find the consecutive dates with observations across thousands of sellers/ items. For some reason, the below code did not work on the larger data set. </p>
  
  <pre><code>d = {'item_number':['KIN005','KIN005','KIN005','KIN005','KIN005','A789B','A789B','A789B','G123H','G123H','G123H'],
  'Comp_ID':['1395','1395','1395','1395','1395','7787','7787','7787','1395','1395','1395'],
  'date':['2016-11-22','2016-11-21','2016-11-20','2016-11-14','2016-11-13','2016-11-22','2016-11-21','2016-11-12','2016-11-22','2016-11-21','2016-11-08']}
  
  df = pd.DataFrame(data=d)
  df.date = pd.to_datetime(df.date)
  d = pd.Timedelta(1, 'D')
  
  df = df.sort_values(['item_number','date','Comp_ID'],ascending=False)
  
  g = df.groupby(['Comp_ID','item_number'])
  sequence = g['date'].apply(lambda x: x.diff().fillna(0).abs().le(d)).reset_index()
  sequence.set_index('index',inplace=True)
  test = df.join(sequence)
  test.columns = ['Comp_ID','date','item_number','consecutive']
  g = test.groupby(['Comp_ID','item_number'])
  g['consecutive'].apply(lambda x: x.idxmin() - x.idxmax() )
  </code></pre>
  
  <p>This gets the desired result for the smaller data-set:</p>
  
  <pre><code>Comp_ID  item_number
  1395     G123H          2
           KIN005         3
  7787     KIN005         2
  Name: consecutive, dtype: int64
  </code></pre>