title: vectorize percentile value of column B of column A (for groups)
body: <p>For every pair of <code>src</code> and <code>dest</code> airport cities I want to return a percentile of column <code>a</code> given a value of column <code>b</code>. </p>
  
  <p>I can do this manually as such:</p>
  
  <p>example df with only 2 pairs of src/dest (I have thousands in my actual df):</p>
  
  <pre><code>dt  src dest    a   b
  0   2016-01-01  YYZ SFO 548.12  279.28
  1   2016-01-01  DFW PDX 111.35  -65.50
  2   2016-02-01  YYZ SFO 64.84   342.35
  3   2016-02-01  DFW PDX 63.81   61.64
  4   2016-03-01  YYZ SFO 614.29  262.83
  
  {'a': {0: 548.12,
    1: 111.34999999999999,
    2: 64.840000000000003,
    3: 63.810000000000002,
    4: 614.28999999999996,
    5: -207.49000000000001,
    6: 151.31999999999999,
    7: -56.43,
    8: 611.37,
    9: -296.62,
    10: 6417.5699999999997,
    11: -376.25999999999999,
    12: 465.12,
    13: -821.73000000000002,
    14: 1270.6700000000001,
    15: -1410.0899999999999,
    16: 1312.6600000000001,
    17: -326.25999999999999,
    18: 1683.3699999999999,
    19: -24.440000000000001,
    20: 583.60000000000002,
    21: -5.2400000000000002,
    22: 1122.74,
    23: 195.21000000000001,
    24: 97.040000000000006,
    25: 133.94},
   'b': {0: 279.27999999999997,
    1: -65.5,
    2: 342.35000000000002,
    3: 61.640000000000001,
    4: 262.82999999999998,
    5: 115.89,
    6: 268.63999999999999,
    7: 2.3500000000000001,
    8: 91.849999999999994,
    9: 62.119999999999997,
    10: 778.33000000000004,
    11: -142.78,
    12: 1675.53,
    13: -214.36000000000001,
    14: 983.80999999999995,
    15: -207.62,
    16: 632.13999999999999,
    17: -132.53,
    18: 422.36000000000001,
    19: 13.470000000000001,
    20: 642.73000000000002,
    21: -144.59999999999999,
    22: 213.15000000000001,
    23: -50.200000000000003,
    24: 338.27999999999997,
    25: -129.69},
   'dest': {0: 'SFO',
    1: 'PDX',
    2: 'SFO',
    3: 'PDX',
    4: 'SFO',
    5: 'PDX',
    6: 'SFO',
    7: 'PDX',
    8: 'SFO',
    9: 'PDX',
    10: 'SFO',
    11: 'PDX',
    12: 'SFO',
    13: 'PDX',
    14: 'SFO',
    15: 'PDX',
    16: 'SFO',
    17: 'PDX',
    18: 'SFO',
    19: 'PDX',
    20: 'SFO',
    21: 'PDX',
    22: 'SFO',
    23: 'PDX',
    24: 'SFO',
    25: 'PDX'},
   'dt': {0: Timestamp('2016-01-01 00:00:00'),
    1: Timestamp('2016-01-01 00:00:00'),
    2: Timestamp('2016-02-01 00:00:00'),
    3: Timestamp('2016-02-01 00:00:00'),
    4: Timestamp('2016-03-01 00:00:00'),
    5: Timestamp('2016-03-01 00:00:00'),
    6: Timestamp('2016-04-01 00:00:00'),
    7: Timestamp('2016-04-01 00:00:00'),
    8: Timestamp('2016-05-01 00:00:00'),
    9: Timestamp('2016-05-01 00:00:00'),
    10: Timestamp('2016-06-01 00:00:00'),
    11: Timestamp('2016-06-01 00:00:00'),
    12: Timestamp('2016-07-01 00:00:00'),
    13: Timestamp('2016-07-01 00:00:00'),
    14: Timestamp('2016-08-01 00:00:00'),
    15: Timestamp('2016-08-01 00:00:00'),
    16: Timestamp('2016-09-01 00:00:00'),
    17: Timestamp('2016-09-01 00:00:00'),
    18: Timestamp('2016-10-01 00:00:00'),
    19: Timestamp('2016-10-01 00:00:00'),
    20: Timestamp('2016-11-01 00:00:00'),
    21: Timestamp('2016-11-01 00:00:00'),
    22: Timestamp('2016-12-01 00:00:00'),
    23: Timestamp('2016-12-01 00:00:00'),
    24: Timestamp('2017-01-01 00:00:00'),
    25: Timestamp('2017-01-01 00:00:00')},
   'src': {0: 'YYZ',
    1: 'DFW',
    2: 'YYZ',
    3: 'DFW',
    4: 'YYZ',
    5: 'DFW',
    6: 'YYZ',
    7: 'DFW',
    8: 'YYZ',
    9: 'DFW',
    10: 'YYZ',
    11: 'DFW',
    12: 'YYZ',
    13: 'DFW',
    14: 'YYZ',
    15: 'DFW',
    16: 'YYZ',
    17: 'DFW',
    18: 'YYZ',
    19: 'DFW',
    20: 'YYZ',
    21: 'DFW',
    22: 'YYZ',
    23: 'DFW',
    24: 'YYZ',
    25: 'DFW'}}
  </code></pre>
  
  <p>I want the percentile per group of <code>src</code> and <code>dest</code> pairs. So there should only be 1 percentile value for each pair. I only want to perform the percentile given <code>b</code> where <code>date = 2017-01-01</code> for each <code>src</code> and <code>dest</code> pair over the entire column <code>a</code> for each pair. Make sense?</p>
  
  <p>I can do this manually for example for a specific pair <code>i.e. src=YYZ and dest=SFT</code>:</p>
  
  <pre><code>from scipy import stats
  import datetime as dt
  import pandas as pd
  
  p0 = dt.datetime(2017,1,1)
  
  # lets slice df for src=YYZ and dest = SFO
  x = df[(df.src =='YYZ') &amp;
  (df.dest =='SFO') &amp;
  (df.dt ==p0)].b.values[0]
  
  # given B, what percentile does it fall in for the entire column A for YYZ, SFO
  stats.percentileofscore(df['a'],x)
  61.53846153846154
  </code></pre>
  
  <p>In the above case, I did this manually for pairs YYZ and SFO. However, I have thousands of pairs in my df.  </p>
  
  <p>How do I <code>vectorize</code> this using <code>pandas features</code> rather than looping through every pair? </p>
  
  <p>There must be a way to use <code>groupby</code> and use <code>apply</code> over a function? </p>
  
  <p>My desired df should look something like:</p>
  
  <pre><code>    src dest  percentile
  0   YYZ SFO   61.54
  1   DFW PDX   23.07
  2   XXX YYY   blahblah1
  3   AAA BBB   blahblah2
  ...
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I implemented the following:</p>
  
  <pre><code>def b_percentile_a(df,x,y,b):
      z = df[(df['src'] == x ) &amp; (df['dest'] == y)].a
      r = stats.percentileofscore(z,b)
      return r
  
  b_vector_df = df[df.dt == p0]
  
  b_vector_df['p0_a_percentile_b'] = \
      b_vector_df.apply(lambda x: b_percentile_a(df,x.src,x.dest,x.b), axis=1)
  </code></pre>
  
  <p>It takes <code>5.16</code> seconds for <code>100</code> pairs. I have <code>55,000</code> pairs. So this will take <code>~50</code> minutes. I need to run this <code>36</code> times so its going to take <code>several days</code> of run time. </p>
  
  <p>There must be a faster approach? </p>