Calculate percentiles/quantiles for a timeseries with resample or groupby - pandas
<p>I have a time series of hourly values and I am trying to derive some basic statistics on a weekly/monthly basis.</p>

<p>If we use the following abstract dataframe, were each column is time-series:</p>

<pre><code>rng = pd.date_range('1/1/2016', periods=2400, freq='H')
df = pd.DataFrame(np.random.randn(len(rng), 4), columns=list('ABCD'), index=rng)
</code></pre>

<p><code>print df[:5]</code> returns:</p>

<pre><code>                            A         B         C         D
2016-01-01 00:00:00  1.521581  0.102335  0.796271  0.317046
2016-01-01 01:00:00 -0.369221 -0.179821 -1.340149 -0.347298
2016-01-01 02:00:00  0.750247  0.698579  0.440716  0.362159
2016-01-01 03:00:00 -0.465073  1.783315  1.165954  0.142973
2016-01-01 04:00:00  1.995332  1.230331 -0.135243  1.189431
</code></pre>

<p>I can call:</p>

<pre><code>r = df.resample('W-MON')
</code></pre>

<p>and then use: <code>r.min()</code>, <code>r.mean()</code>, <code>r.max()</code>, which all work fine. For instance <code>print r.min()[:5]</code> returns:</p>

<pre><code>                   A         B         C         D
2016-01-04 -2.676778 -2.450659 -2.401721 -3.209390
2016-01-11 -2.710066 -2.372032 -2.864887 -2.387026
2016-01-18 -2.984805 -2.527528 -3.414003 -2.616434
2016-01-25 -2.625299 -2.947864 -2.642569 -2.262959
2016-02-01 -2.100062 -2.568878 -3.008864 -2.315566
</code></pre>

<p>However, if I try to calculate <strong>percentiles</strong>, using the <strong>quantile</strong> formula, i.e. <code>r.quantile(0.95)</code>, I get one value for each column</p>

<pre><code>A    0.090502
B    0.136594
C    0.058720
D    0.125131
</code></pre>

<p>Is there a way to combine the grouping / resampling using quantiles as arguments?</p>

<p>Thanks</p>

<p>I think you can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.tseries.resample.Resampler.apply.html" rel="nofollow"><code>Resampler.apply</code></a>, because <code>Resampler.quantile</code> is <a href="http://pandas.pydata.org/pandas-docs/stable/api.html#resampling" rel="nofollow">not implemented</a> yet:</p>

<pre><code>np.random.seed(1234)
rng = pd.date_range('1/1/2016', periods=2400, freq='H')
df = pd.DataFrame(np.random.randn(len(rng), 4), columns=list('ABCD'), index=rng)
#print (df)

r = df.resample('W-MON')

print (r.apply(lambda x: x.quantile(0.95)))
                   A         B         C         D
2016-01-04  1.540236  1.925962  1.439512  1.606239
2016-01-11  1.727545  1.520913  1.596961  1.652290
2016-01-18  1.595396  1.669630  1.763577  1.933235
2016-01-25  1.500270  1.604542  1.648790  1.778329
2016-02-01  1.608245  1.791356  1.548159  1.786005
2016-02-08  1.531625  1.408163  1.300414  1.877863
2016-02-15  1.818673  1.613632  1.498623  1.524481
2016-02-22  1.557928  1.566523  1.974486  1.727555
2016-02-29  1.530757  1.529591  1.869422  1.433620
2016-03-07  1.651609  1.452537  1.585765  1.414499
2016-03-14  1.311807  1.717968  1.410036  1.903715
2016-03-21  1.529065  1.693964  1.784480  1.708263
2016-03-28  1.636786  1.405565  1.809235  1.802555
2016-04-04  1.768068  1.564308  1.552492  1.801424
2016-04-11  1.824578  1.794437  1.649749  1.564300
</code></pre>

<hr>

<p>With <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> is possible use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.quantile.html" rel="nofollow"><code>DataFrameGroupBy.quantile</code></a>:</p>

<pre><code>g = df.groupby([pd.TimeGrouper('W-MON')])

print (g.quantile(0.95))
                   A         B         C         D
2016-01-04  1.540236  1.925962  1.439512  1.606239
2016-01-11  1.727545  1.520913  1.596961  1.652290
2016-01-18  1.595396  1.669630  1.763577  1.933235
2016-01-25  1.500270  1.604542  1.648790  1.778329
2016-02-01  1.608245  1.791356  1.548159  1.786005
2016-02-08  1.531625  1.408163  1.300414  1.877863
2016-02-15  1.818673  1.613632  1.498623  1.524481
2016-02-22  1.557928  1.566523  1.974486  1.727555
2016-02-29  1.530757  1.529591  1.869422  1.433620
2016-03-07  1.651609  1.452537  1.585765  1.414499
2016-03-14  1.311807  1.717968  1.410036  1.903715
2016-03-21  1.529065  1.693964  1.784480  1.708263
2016-03-28  1.636786  1.405565  1.809235  1.802555
2016-04-04  1.768068  1.564308  1.552492  1.801424
2016-04-11  1.824578  1.794437  1.649749  1.564300
</code></pre>

python pandas group-by resampling quantile