title: Creating a new column in pandas quantile using Quantile function
body: <p>I want to create a column Quantile, for each date. Calculated the Quantile for each unique value Sales value. Ie Category always corresponds to the same number in sales for each particular date.</p>
  
  <p>I have dataframe which is indexed by date. There are many dates and multiple of the same dates. Example of the subset of df for 1 day:</p>
  
  <pre><code>          Category  Sales   Ratio 1 Ratio 2
  11/19/2016  Bar     300     0.46    0.96
  11/19/2016  Bar     300     0.56    0.78
  11/19/2016  Bar     300     0.43    0.96
  11/19/2016  Bar     300     0.47    0.94
  11/19/2016  Casino  550     0.92    0.12
  11/19/2016  Casino  550     0.43    0.74
  11/19/2016  Casino  550     0.98    0.65
  11/19/2016  Casino  550     0.76    0.67
  11/19/2016  Casino  550     0.79    0.80
  11/19/2016  Casino  550     0.90    0.91
  11/19/2016  Casino  550     0.89    0.31
  11/19/2016  Café    700     0.69    0.99
  11/19/2016  Café    700     0.07    0.18
  11/19/2016  Café    700     0.75    0.59
  11/19/2016  Café    700     0.07    0.64
  11/19/2016  Café    700     0.14    0.42
  11/19/2016  Café    700     0.30    0.67
  11/19/2016  Pub     250     0.64    0.09
  11/19/2016  Pub     250     0.93    0.37
  11/19/2016  Pub     250     0.69    0.42
  </code></pre>
  
  <p>I want a code which adds a new column called Quantile which calculates for each date the 0.5 quantile of unique Sales. Key thing to note is Sales is always the same for a category for a particular date (things change as dates change).</p>
  
  <p>Example of a solution: df['Quantile'] = df.Sales.groupby(df.index).transform(lambda x: x.quantile(q=0.5, axis=0, interpolation='midpoint'))</p>
  
  <p>However this would not suffice (even if it worked). For this example (for this one date), In the new column df['Quantile'], all values would be the same for a partcular date.</p>
  
  <p>For this date the calculation would use 300, 550, 700 and 250 for the quantile.</p>
  
  <p>Therefore the final df would look like this:</p>
  
  <pre><code>      Category  Sales   Ratio 1 Ratio 2     Quantile
  11/19/2016  Bar     300     0.46    0.96      425
  11/19/2016  Bar     300     0.56    0.78      425
  11/19/2016  Bar     300     0.43    0.96      425
  11/19/2016  Bar     300     0.47    0.94      425
  11/19/2016  Casino  550     0.92    0.12      425
  11/19/2016  Casino  550     0.43    0.74      425
  11/19/2016  Casino  550     0.98    0.65      425
  11/19/2016  Casino  550     0.76    0.67      425
  11/19/2016  Casino  550     0.79    0.80      425
  11/19/2016  Casino  550     0.90    0.91      425
  11/19/2016  Casino  550     0.89    0.31      425
  11/19/2016  Café    700     0.69    0.99      425
  11/19/2016  Café    700     0.07    0.18      425
  11/19/2016  Café    700     0.75    0.59      425
  11/19/2016  Café    700     0.07    0.64      425
  11/19/2016  Café    700     0.14    0.42      425
  11/19/2016  Café    700     0.30    0.67      425
  11/19/2016  Pub     250     0.64    0.09      425
  11/19/2016  Pub     250     0.93    0.37      425
  11/19/2016  Pub     250     0.69    0.42      425
  </code></pre>
  
  <p>If I was to do Quantile of all Sales for a particular date without looking at only one element of each category I would get something like 550 (which I do not want).</p>
  
  <p>Key thing is I would like the code to be simple, and reasonably fast (as date is quite big). Also interpolation has to be midpoint.</p>