title: Fastest way to calculate average of datetime rows using pandas
body: <p>I have 122864 row of data. I am storing data in HDF5 file. Using pandas for data processing. For each unique id in record there is a timestamp associated indicating time when user opened an app. I want to get average duration between two hits of app. </p>
  
  <pre><code>1283    2015-04-01 08:07:44.131768
  1284    2015-04-01 08:08:02.752611
  1285    2015-04-01 08:08:02.793380
  1286    2015-04-01 08:07:53.910469
  1287    2015-04-01 08:08:03.305893
  1288    2015-04-01 08:07:44.843050
  1289    2015-04-01 08:07:54.767203
  1290    2015-04-01 08:08:03.965367
  1291    2015-04-01 08:07:45.924854
  1292    2015-04-01 08:07:55.408593
  1293    2015-04-01 08:07:46.365128
  
  class User(object):
  
      ''' 
      Properties and function related to each object.
  
      attributes:
  
          datetime: a list of hit timestamp for each user object
          deviceid: unique deviceid
      '''
  
      def __init__(self, User, device_id):
          self.datetime = pd.to_datetime(list(User['datetime']))
          self.deviceid = device_id
          self.avrgtime = 0.0
          avgtime.setdefault(self.deviceid, 1)
  
      def avg_duration(self):
  
          '''
          average duration b/w hits for each user.
          '''
  
          for i,time in enumerate(self.datetime[:-1]):
              self.avrgtime += abs(self.datetime[i+1] - time).total_seconds()
          avgtime[self.deviceid] = self.avrgtime/len(self.datetime)
          pp.pprint(avgtime)
              #avgtime[] = datetime.strptime(time, '%Y-%m-%d %H:%M:%S.%f')
  
          pass
  
  
  def eachdevice(gstore):
      count = 0
      for did in list(gstore['data'].drop_duplicates('device_id')['device_id']):
  
       auser = gstore.select('data', where="device_id == did")
       gamer = User(auser, did) 
       gamer.avg_duration()
       count+=1
       print count
  
  
  
  #main workshore
  if __name__ == '__main__':
  
      try:
          path = os.path.abspath(sys.argv[1])
          with pd.HDFStore('Gamer.h5') as gstore:
              eachdevice(gstore)            
  
      except IndexError:
          print('\nPass path of the HDF5 file to be analyized...EXITING\n') 
  </code></pre>
  
  <p>What I am doing till now is looping through each unique_id and using pandas dataframe select querying datetime for each unique id. This returns datetime object dataframe. I convert this to list and then loop to calculate average difference between two timestamps. 
  This approach takes lots of time. Is there any way to do this in using pandas?</p>
  
  <p>Please help.</p>
  
  <p>EDIT: after commenting out all the calculation part i run the code. I think this <strong>auser = gstore.select('data', where="device_id == did")</strong> is taking all the time. How to improve? any alternative or better way?
  %timeit result :1 loops, best of 3: 13.3 s per loop for 1000 iterations.</p>
  
  <p>Edit: 
  Sample data: </p>
  
  <pre><code>                           device_id                    datetime
  0   c4be7e55d98914647c51329edc2ab734  2015-03-30 22:00:05.922317
  1   05fed9f8e07c3cac457723286d36f621  2015-03-30 22:00:07.895672
  2   783faeed9fe35a3f45b521b3a6667a2d  2015-03-30 22:00:05.529631
  3   c2022ad838cec35bdb12fc3a6e2cf452  2015-03-30 21:59:59.043905
  4   a8a04268ee0c22b26af59e053390cf6f  2015-03-30 22:00:14.248542
  5   4e5ed16b44b9cd38c408859d1d241e2d  2015-03-30 22:00:02.391719
  6   c0bfd3f9046855ffaaec4d99c367fd8c  2015-03-30 22:00:18.649193
  7   95f1182c6e4d601ba0b20f5204168ecb  2015-03-30 22:00:13.629728
  8   a85caa7e0a4a7d57e6330c083daff326  2015-03-30 22:00:08.340469
  9   46cdbee963814cdb4e6a0ac0049b8fc6  2015-03-30 22:00:23.152820
  10  3c8bf70679cd9c6f18aa52d06e0e181d  2015-03-30 22:00:17.619251
  11  52bc4e3d9dc373d89ec31effe10e6f30  2015-03-30 22:00:11.591954
  12  3477eb25e26b6bff0bfc6c3ee59a5f40  2015-03-30 22:00:25.745083
  13  e7bf8ae864f2148831628a6f2e8e406e  2015-03-30 22:00:20.911568
  14  a15af8faffd655a3e80f85840bbf3c2a  2015-03-30 22:00:19.017887
  15  9d9f71f080c0cf478ec4117e78ff89ee  2015-03-30 22:00:28.435585
  16  1633d88738316e3602890499b1f778b1  2015-03-30 22:00:24.108234
  17  3362daf99f11541acbf45e70fdaf5f49  2015-03-30 22:00:24.512366
  18  96c3c005eaaaa8d6af3f2443ca8f73df  2015-03-30 22:00:29.713550
  19  002642b9ed495f84318fcb42557f53e1  2015-03-30 22:00:37.936647
  </code></pre>