title: Using R to identify and group related observations based on both logical conditions and how closely in time they occur
body: <p>I have a dataset ("bids") that is composed of series of observations (~5 million) each of which represents a bid to purchase a product (in the simplified example below, either a book or a game). For each observation, I have (see example data below):</p>
  
  <ol>
  <li>Date that the bid was submitted</li>
  <li>Time that the bid was submitted</li>
  <li>Name of the product on which the bidder is bidding</li>
  <li><p>Name of the bidder</p>
  
  <pre><code>observation date        time        product bidder
  1           1/1/2016    9:00:00 AM  book    AB
  2           1/1/2016    9:01:00 AM  book    CD
  3           1/1/2016    9:02:00 AM  book    EF
  4           1/1/2016    9:03:00 AM  book    CD
  5           1/1/2016    9:00:00 AM  game    AB
  6           1/1/2016    9:01:00 AM  game    CD
  7           1/1/2016    9:02:00 AM  game    CD
  8           1/1/2016    9:07:00 AM  game    CD
  9           1/2/2016    9:00:00 AM  book    AB
  10          1/2/2016    9:06:00 AM  book    CD
  11          1/2/2016    9:02:00 AM  book    EF
  12          1/2/2016    9:03:00 AM  book    EF
  13          1/2/2016    9:00:00 AM  game    EF
  14          1/2/2016    8:59:00 AM  game    CD
  15          1/2/2016    9:00:00 AM  game    GH
  16          1/2/2016    9:01:00 AM  game    AB
  17          1/2/2016    10:00:00 AM game    AB
  18          1/2/2016    10:06:00 AM game    CD
  19          1/2/2016    10:06:00 AM game    EF
  20          1/2/2016    10:06:00 AM game    GH
  21          1/2/2016    3:00:00 PM  game    AB
  </code></pre></li>
  </ol>
  
  <p>In some cases, there is a single bid made by one bidder for a particular product and no other bids for that product that occur close in time (e.g., observation #21). In most cases, however, there are several bids from several bidders for the same product that are close together in time (e.g., observations 1-4 make up a group; observations 14-16 make up a group). To study these groups, I need to be able to group them and identify each group with a unique identifier. Ultimately, I also need to be able to count both the total number of bids in each group and the number of unique/distinct bidders in each group. That's something I can probably solve on my own if I figure out how to create the groups, but I mention it in case there is a simpler/more integrated approach to that.</p>
  
  <p>What I'm struggling with is the "close in time" parameter. If, "close in time" meant the same day, it's clear to me that I could use id in plyr and create a new column ("bidgrp") (or any of several other approaches). Something like:</p>
  
  <pre><code>bids$bidgrp &lt;- id(bids[c("date", "product")], drop = TRUE)
  </code></pre>
  
  <p>But, "close in time" actually means within 5 minutes. In other words, for example, observations 9, 11, and 12 are part of a group, but 10 - since it is more than 5 minutes after the earliest member of the group (9), is not part of the group. Part of the challenge is figuring how to establish what the first (earliest) member of the group is (I don't have a reliable indicator, so [this] (<a href="https://stackoverflow.com/questions/30978438/grouping-observations-based-on-first-row-value">Grouping observations based on first row value</a>) solution won't work, but that can probably be done by sorting the data before attempting any grouping (although here again, if there are smarter, more efficient ways to do this, I'd welcome them)</p>
  
  <p>From looking at other conditional grouping questions on SO and elsewhere, my instinct is to tackle this with a series of ifelse loop-like steps as follows:</p>
  
  <ol>
  <li>Sort the dataset first by date, then by product, then by time</li>
  <li>Assign a group id number of i to the first observation</li>
  <li>Look at the product being bid on in next observation; if it is a bid for a different product than the product in the prior observation, assign it a group id of i+1; if it is a bid for the same product, look at the date.</li>
  <li>If the date is different than the prior observation, assign it a group id of i+1; if it is the same date, look at the time</li>
  <li>If the time is more than 5 minutes after the prior observation, assign it a group id of i+1; if it is within 5 minutes of the <em>earliest observation in the group</em> (this is what makes the problem particularly tricky - not just a matter of looking at the last observation, but knowing which observation to key off when determining distance in time), assign it a group number of i and look at the next observation</li>
  </ol>
  
  <p>The result (for the sample data above), would identify 9 groups, as follows:</p>
  
  <pre><code>observation date    time        product bidder  grpid
  1       1/1/2016    9:00:00 AM  book    AB      1
  2       1/1/2016    9:01:00 AM  book    CD      1
  3       1/1/2016    9:02:00 AM  book    EF      1
  4       1/1/2016    9:03:00 AM  book    CD      1
  5       1/1/2016    9:00:00 AM  game    AB      2
  6       1/1/2016    9:01:00 AM  game    CD      2
  7       1/1/2016    9:02:00 AM  game    CD      2
  8       1/1/2016    9:07:00 AM  game    CD      3
  9       1/2/2016    9:00:00 AM  book    AB      4
  11      1/2/2016    9:02:00 AM  book    EF      4
  12      1/2/2016    9:03:00 AM  book    EF      4
  10      1/2/2016    9:06:00 AM  book    CD      5
  14      1/2/2016    8:59:00 AM  game    CD      6
  13      1/2/2016    9:00:00 AM  game    EF      6
  15      1/2/2016    9:00:00 AM  game    GH      6
  16      1/2/2016    9:01:00 AM  game    AB      6
  17      1/2/2016    10:00:00 AM game    AB      7
  18      1/2/2016    10:06:00 AM game    CD      8
  19      1/2/2016    10:06:00 AM game    EF      8
  20      1/2/2016    10:06:00 AM game    GH      8
  21      1/2/2016    3:00:00 PM  game    AB      9
  </code></pre>
  
  <p>And, I'll ultimately need to get to something like this:</p>
  
  <pre><code>grpid   bids    uniquebidders
  1       4       3
  2       3       2
  3       1       1
  4       3       2
  5       1       1
  6       4       4
  7       1       1
  8       3       3
  9       1       1
  </code></pre>
  
  <p>Apologies for the long question. I know several of the sub-issues here (working with time; loop-like operations) have been covered on SO (I've reviewed many of them), but it's the combination of these issues that makes this particularly challenging for me (and hopefully useful for others).</p>
  
  <p>Thanks in advance for any help you can offer. </p>