title: funnel analysis with pandas (large data set)
body: <p>I'm trying to do some rudimentary funnel analysis with a pandas dataframe. Meaning, I have a dataframe containing user <code>sessions</code> that are made up of a series of <code>events</code>. I would like to be able to group by <code>session</code>, identify which <code>sessions</code> contain a given <code>event ordering</code> (eventA followed by eventB), and then group by <code>date</code> and get a count of these over time. </p>
  
  <p>for example, given my dataframe:</p>
  
  <pre><code>sessions = ['a', 'a', 'a', 'b', 'b', 'b', 'b', 'c', 'c', 'd', 'd', 'd']
  events = ['dog', 'cat', 'tree', 'tree', 'dog', 'frog', 'cat', 'dog', 'cat', 'tree', 'cat', 'dog']
  d1 = datetime(2014,8,1)
  d2 = datetime(2014,8,2)
  d3 = datetime(2014,8,3)
  dates = [d1, d1, d1, d1, d1, d1, d1, d2, d2, d1, d1, d1]
  dic = {'sessions':sessions, 'events':events, 'dates':dates}
  df_tot = pd.DataFrame(dic)
  </code></pre>
  
  <p>producing:</p>
  
  <pre><code>    sessionDate  events  sessions
  0   2014-08-01   dog     a
  1   2014-08-01   cat     a
  2   2014-08-01   tree    a
  3   2014-08-01   tree    b
  4   2014-08-01   dog     b
  5   2014-08-01   frog    b
  6   2014-08-01   cat     b
  7   2014-08-02   dog     c
  8   2014-08-02   cat     c
  9   2014-08-01   tree    d
  10  2014-08-01   cat     d
  11  2014-08-01   dog     d
  </code></pre>
  
  <p>I would like to get the following for event ordering <code>dog</code> then <code>cat</code>:</p>
  
  <pre><code>             reachedFirstEvent   reachedSecondEvent  total
  2014-08-01   1                   2                   3
  2014-08-02   0                   1                   1
  </code></pre>
  
  <p>My second problem is, I have 3 million rows in my actual dataframe. So I built a hacked together solution. It works but is pretty slow. Any thoughts on how to do this or speed up my code?</p>
  
  <pre><code>def find_funnels_ex(dlist,event_list):
  
          m = -1
          for i in range(0,len(event_list)):
  
              j = np.where(dlist == event_list[i])[0] #get all indices where cat
              j = j[j&gt;=m] #select only indices greater than min dog index
              if j.size == 0:
                  return i
              else:
                  m = np.min(j)
  
          return i+1
  
  sessions = ['a', 'a', 'a', 'b', 'b', 'b', 'b', 'c', 'c', 'd', 'd', 'd']
  events = ['dog', 'cat', 'tree', 'tree', 'dog', 'frog', 'cat', 'dog', 'cat', 'tree', 'cat', 'dog']
  d1 = datetime(2014,8,1)
  d2 = datetime(2014,8,2)
  d3 = datetime(2014,8,3)
  dates = [d1, d1, d1, d1, d1, d1, d1, d2, d2, d1, d1, d1]
  dic = {'sessions':sessions, 'events':events, 'dates':dates}
  df_tot = pd.DataFrame(dic)
  
  
  #get only groups that have at least first event
  gb_tot = df_tot.groupby('sessions')
  df_filt = gb_tot.filter(lambda x: 'dog' in x['events'].values) #changes to dataframe
  
  #get funnel position for each session
  #returns a 1 if first event is reached, returns a 2 if second event is reached, etc
  gb_filt = df_filt.groupby('sessions')
  gb_funn = gb_filt.aggregate(lambda x: find_funnels_ex(
                                  x['events'].values, 
                                  ['dog','cat']
                                  )
                     ) 
  
  #join this to funnel to get date events funnel was started
  gb_filt = gb_filt.aggregate({'dates':np.min}) 
  gb_filt['funnel'] = gb_funn['events']
  df_funn = gb_filt.reset_index() #change back to dataframe
  
  #pivot to get columns of funnel position indicators
  df_piv = pd.pivot_table(df_funn,'funnel', cols='funnel', rows=['sessions','dates'], aggfunc=np.sum) #pivot
  df_piv = df_piv.reset_index() #reset
  
  #group by date and sum
  df_piv = df_piv.set_index('dates') #set index
  gb_piv = df_piv.groupby(lambda x: x) #groupby date
  gb_final = gb_piv.aggregate({1:np.sum,2:np.sum})
  
  #get totals
  gb_tot = df_tot.groupby('sessions')
  gb_tot = gb_tot.aggregate({'dates':np.min})
  gb_tot = gb_tot.set_index('dates') #set index
  gb_tot = gb_tot.groupby(lambda x: x).size() #groupby date
  gb_final['total'] = gb_tot
  
  
  gb_final[2] = gb_final.apply(lambda x: x[2]/2.0,axis=1)
  </code></pre>