Table Of Contents
- What’s New
- Installation
- Contributing to pandas
- Frequently Asked Questions (FAQ)
- Package overview
- 10 Minutes to pandas
- Tutorials
- Cookbook
- Intro to Data Structures
- Essential Basic Functionality
- Working with Text Data
- Options and Settings
- Indexing and Selecting Data
- MultiIndex / Advanced Indexing
- Computational tools
- Working with missing data
- Group By: split-apply-combine
- Merge, join, and concatenate
- Reshaping and Pivot Tables
- Time Series / Date functionality
- Time Deltas
- Categorical Data
- Visualization
- Style
- IO Tools (Text, CSV, HDF5, ...)
- Remote Data Access
- Enhancing Performance
- Sparse data structures
- Caveats and Gotchas
- rpy2 / R interface
- pandas Ecosystem
- Comparison with R / R libraries
- Comparison with SQL
- Comparison with SAS
- API Reference
- Internals
- Release Notes
Search
Enter search terms or a module, class or function name.
Computational tools¶
Statistical Functions¶
Percent Change¶
Series,DataFrame, andPanel all have a methodpct_change to compute thepercent change over a given number of periods (usingfill_method to fillNA/null valuesbefore computing the percent change).
In [1]:ser=pd.Series(np.random.randn(8))In [2]:ser.pct_change()Out[2]:0 NaN1 -1.6029762 4.3349383 -0.2474564 -2.0673455 -1.1429036 -1.6882147 -9.759729dtype: float64
In [3]:df=pd.DataFrame(np.random.randn(10,4))In [4]:df.pct_change(periods=3)Out[4]: 0 1 2 30 NaN NaN NaN NaN1 NaN NaN NaN NaN2 NaN NaN NaN NaN3 -0.218320 -1.054001 1.987147 -0.5101834 -0.439121 -1.816454 0.649715 -4.8228095 -0.127833 -3.042065 -5.866604 -1.7769776 -2.596833 -1.959538 -2.111697 -3.7989007 -0.117826 -2.169058 0.036094 -0.0676968 2.492606 -1.357320 -1.205802 -1.5586979 -1.012977 2.324558 -1.003744 -0.371806
Covariance¶
TheSeries object has a methodcov to compute covariance between series(excluding NA/null values).
In [5]:s1=pd.Series(np.random.randn(1000))In [6]:s2=pd.Series(np.random.randn(1000))In [7]:s1.cov(s2)Out[7]:0.00068010881743108746
Analogously,DataFrame has a methodcov to compute pairwise covariancesamong the series in the DataFrame, also excluding NA/null values.
Note
Assuming the missing data are missing at random this results in an estimatefor the covariance matrix which is unbiased. However, for many applicationsthis estimate may not be acceptable because the estimated covariance matrixis not guaranteed to be positive semi-definite. This could lead toestimated correlations having absolute values which are greater than one,and/or a non-invertible covariance matrix. SeeEstimation of covariancematricesfor more details.
In [8]:frame=pd.DataFrame(np.random.randn(1000,5),columns=['a','b','c','d','e'])In [9]:frame.cov()Out[9]: a b c d ea 1.000882 -0.003177 -0.002698 -0.006889 0.031912b -0.003177 1.024721 0.000191 0.009212 0.000857c -0.002698 0.000191 0.950735 -0.031743 -0.005087d -0.006889 0.009212 -0.031743 1.002983 -0.047952e 0.031912 0.000857 -0.005087 -0.047952 1.042487
DataFrame.cov also supports an optionalmin_periods keyword thatspecifies the required minimum number of observations for each column pairin order to have a valid result.
In [10]:frame=pd.DataFrame(np.random.randn(20,3),columns=['a','b','c'])In [11]:frame.ix[:5,'a']=np.nanIn [12]:frame.ix[5:10,'b']=np.nanIn [13]:frame.cov()Out[13]: a b ca 1.210090 -0.430629 0.018002b -0.430629 1.240960 0.347188c 0.018002 0.347188 1.301149In [14]:frame.cov(min_periods=12)Out[14]: a b ca 1.210090 NaN 0.018002b NaN 1.240960 0.347188c 0.018002 0.347188 1.301149
Correlation¶
Several methods for computing correlations are provided:
| Method name | Description |
|---|---|
pearson(default) | Standard correlation coefficient |
kendall | Kendall Tau correlation coefficient |
spearman | Spearman rank correlation coefficient |
All of these are currently computed using pairwise complete observations.
Note
Please see thecaveats associatedwith this method of calculating correlation matrices in thecovariance section.
In [15]:frame=pd.DataFrame(np.random.randn(1000,5),columns=['a','b','c','d','e'])In [16]:frame.ix[::2]=np.nan# Series with SeriesIn [17]:frame['a'].corr(frame['b'])Out[17]:0.013479040400098775In [18]:frame['a'].corr(frame['b'],method='spearman')Out[18]:-0.0072898851595406371# Pairwise correlation of DataFrame columnsIn [19]:frame.corr()Out[19]: a b c d ea 1.000000 0.013479 -0.049269 -0.042239 -0.028525b 0.013479 1.000000 -0.020433 -0.011139 0.005654c -0.049269 -0.020433 1.000000 0.018587 -0.054269d -0.042239 -0.011139 0.018587 1.000000 -0.017060e -0.028525 0.005654 -0.054269 -0.017060 1.000000
Note that non-numeric columns will be automatically excluded from thecorrelation calculation.
Likecov,corr also supports the optionalmin_periods keyword:
In [20]:frame=pd.DataFrame(np.random.randn(20,3),columns=['a','b','c'])In [21]:frame.ix[:5,'a']=np.nanIn [22]:frame.ix[5:10,'b']=np.nanIn [23]:frame.corr()Out[23]: a b ca 1.000000 -0.076520 0.160092b -0.076520 1.000000 0.135967c 0.160092 0.135967 1.000000In [24]:frame.corr(min_periods=12)Out[24]: a b ca 1.000000 NaN 0.160092b NaN 1.000000 0.135967c 0.160092 0.135967 1.000000
A related methodcorrwith is implemented on DataFrame to compute thecorrelation between like-labeled Series contained in different DataFrameobjects.
In [25]:index=['a','b','c','d','e']In [26]:columns=['one','two','three','four']In [27]:df1=pd.DataFrame(np.random.randn(5,4),index=index,columns=columns)In [28]:df2=pd.DataFrame(np.random.randn(4,4),index=index[:4],columns=columns)In [29]:df1.corrwith(df2)Out[29]:one -0.125501two -0.493244three 0.344056four 0.004183dtype: float64In [30]:df2.corrwith(df1,axis=1)Out[30]:a -0.675817b 0.458296c 0.190809d -0.186275e NaNdtype: float64
Data ranking¶
Therank method produces a data ranking with ties being assigned the meanof the ranks (by default) for the group:
In [31]:s=pd.Series(np.random.np.random.randn(5),index=list('abcde'))In [32]:s['d']=s['b']# so there's a tieIn [33]:s.rank()Out[33]:a 5.0b 2.5c 1.0d 2.5e 4.0dtype: float64
rank is also a DataFrame method and can rank either the rows (axis=0)or the columns (axis=1).NaN values are excluded from the ranking.
In [34]:df=pd.DataFrame(np.random.np.random.randn(10,6))In [35]:df[4]=df[2][:5]# some tiesIn [36]:dfOut[36]: 0 1 2 3 4 50 -0.904948 -1.163537 -1.457187 0.135463 -1.457187 0.2946501 -0.976288 -0.244652 -0.748406 -0.999601 -0.748406 -0.8008092 0.401965 1.460840 1.256057 1.308127 1.256057 0.8760043 0.205954 0.369552 -0.669304 0.038378 -0.669304 1.1402964 -0.477586 -0.730705 -1.129149 -0.601463 -1.129149 -0.2111965 -1.092970 -0.689246 0.908114 0.204848 NaN 0.4633476 0.376892 0.959292 0.095572 -0.593740 NaN -0.0691807 -1.002601 1.957794 -0.120708 0.094214 NaN -1.4674228 -0.547231 0.664402 -0.519424 -0.073254 NaN -1.2635449 -0.250277 -0.237428 -1.056443 0.419477 NaN 1.375064In [37]:df.rank(1)Out[37]: 0 1 2 3 4 50 4.0 3.0 1.5 5.0 1.5 6.01 2.0 6.0 4.5 1.0 4.5 3.02 1.0 6.0 3.5 5.0 3.5 2.03 4.0 5.0 1.5 3.0 1.5 6.04 5.0 3.0 1.5 4.0 1.5 6.05 1.0 2.0 5.0 3.0 NaN 4.06 4.0 5.0 3.0 1.0 NaN 2.07 2.0 5.0 3.0 4.0 NaN 1.08 2.0 5.0 3.0 4.0 NaN 1.09 2.0 3.0 1.0 4.0 NaN 5.0
rank optionally takes a parameterascending which by default is true;when false, data is reverse-ranked, with larger values assigned a smaller rank.
rank supports different tie-breaking methods, specified with themethodparameter:
average: average rank of tied groupmin: lowest rank in the groupmax: highest rank in the groupfirst: ranks assigned in the order they appear in the array
Window Functions¶
Warning
Prior to version 0.18.0,pd.rolling_*,pd.expanding_*, andpd.ewm* were module levelfunctions and are now deprecated. These are replaced by using theRolling,Expanding andEWM. objects and a corresponding method call.
The deprecation warning will show the new syntax, see an examplehereYou can view the previous documentationhere
For working with data, a number of windows functions are provided forcomputing commonwindow orrolling statistics. Among these are count, sum,mean, median, correlation, variance, covariance, standard deviation, skewness,and kurtosis.
Note
The API for window statistics is quite similar to the way one works withGroupBy objects, see the documentationhere
We work withrolling,expanding andexponentiallyweighted data through the correspondingobjects,Rolling,Expanding andEWM.
In [38]:s=pd.Series(np.random.randn(1000),index=pd.date_range('1/1/2000',periods=1000))In [39]:s=s.cumsum()In [40]:sOut[40]:2000-01-01 -0.2688242000-01-02 -1.7718552000-01-03 -0.8180032000-01-04 -0.6592442000-01-05 -1.9421332000-01-06 -1.8693912000-01-07 0.563674 ...2002-09-20 -68.2330542002-09-21 -66.7656872002-09-22 -67.4573232002-09-23 -69.2531822002-09-24 -70.2968182002-09-25 -70.8446742002-09-26 -72.475016Freq: D, dtype: float64
These are created from methods onSeries andDataFrame.
In [41]:r=s.rolling(window=60)In [42]:rOut[42]:Rolling[window=60,center=False,axis=0]
These object provide tab-completion of the avaible methods and properties.
In [14]:r.r.agg r.apply r.count r.exclusions r.max r.median r.name r.skew r.sumr.aggregate r.corr r.cov r.kurt r.mean r.min r.quantile r.std r.var
Generally these methods all have the same interface. They allaccept the following arguments:
window: size of moving windowmin_periods: threshold of non-null data points to require (otherwiseresult is NA)center: boolean, whether to set the labels at the center (default is False)
Warning
Thefreq andhow arguments were in the API prior to 0.18.0 changes. These are deprecated in the new API. You can simply resample the input prior to creating a window function.
For example, instead ofs.rolling(window=5,freq='D').max() to get the max value on a rolling 5 Day window, one could uses.resample('D').max().rolling(window=5).max(), which first resamples the data to daily data, then provides a rolling 5 day window.
We can then call methods on theserolling objects. These return like-indexed objects:
In [43]:r.mean()Out[43]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 NaN2000-01-06 NaN2000-01-07 NaN ...2002-09-20 -62.6941352002-09-21 -62.8121902002-09-22 -62.9149712002-09-23 -63.0618672002-09-24 -63.2138762002-09-25 -63.3750742002-09-26 -63.539734Freq: D, dtype: float64
In [44]:s.plot(style='k--')Out[44]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24e8c7190>In [45]:r.mean().plot(style='k')Out[45]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24e8c7190>
They can also be applied to DataFrame objects. This is really just syntacticsugar for applying the moving window operator to all of the DataFrame’s columns:
In [46]:df=pd.DataFrame(np.random.randn(1000,4), ....:index=pd.date_range('1/1/2000',periods=1000), ....:columns=['A','B','C','D']) ....:In [47]:df=df.cumsum()In [48]:df.rolling(window=60).sum().plot(subplots=True)Out[48]:array([<matplotlib.axes._subplots.AxesSubplot object at 0x7fd251886b10>, <matplotlib.axes._subplots.AxesSubplot object at 0x7fd24d6e0850>, <matplotlib.axes._subplots.AxesSubplot object at 0x7fd24df0bc90>, <matplotlib.axes._subplots.AxesSubplot object at 0x7fd24e65e1d0>], dtype=object)
Method Summary¶
We provide a number of the common statistical functions:
| Method | Description |
|---|---|
count() | Number of non-null observations |
sum() | Sum of values |
mean() | Mean of values |
median() | Arithmetic median of values |
min() | Minimum |
max() | Maximum |
std() | Bessel-corrected sample standard deviation |
var() | Unbiased variance |
skew() | Sample skewness (3rd moment) |
kurt() | Sample kurtosis (4th moment) |
quantile() | Sample quantile (value at %) |
apply() | Generic apply |
cov() | Unbiased covariance (binary) |
corr() | Correlation (binary) |
Theapply() function takes an extrafunc argument and performsgeneric rolling computations. Thefunc argument should be a single functionthat produces a single value from an ndarray input. Suppose we wanted tocompute the mean absolute deviation on a rolling basis:
In [49]:mad=lambdax:np.fabs(x-x.mean()).mean()In [50]:s.rolling(window=60).apply(mad).plot(style='k')Out[50]:<matplotlib.axes._subplots.AxesSubplotat0x7fd269f63c50>
Rolling Windows¶
Passingwin_type to.rolling generates a generic rolling window computation, that is weighted according thewin_type.The following methods are available:
| Method | Description |
|---|---|
sum() | Sum of values |
mean() | Mean of values |
The weights used in the window are specified by thewin_type keyword. The list of recognized types are:
boxcartriangblackmanhammingbartlettparzenbohmanblackmanharrisnuttallbarthannkaiser(needs beta)gaussian(needs std)general_gaussian(needs power, width)slepian(needs width).
In [51]:ser=pd.Series(np.random.randn(10),index=pd.date_range('1/1/2000',periods=10))In [52]:ser.rolling(window=5,win_type='triang').mean()Out[52]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 -1.0378702000-01-06 -0.7677052000-01-07 -0.3831972000-01-08 -0.3955132000-01-09 -0.5584402000-01-10 -0.672416Freq: D, dtype: float64
Note that theboxcar window is equivalent tomean().
In [53]:ser.rolling(window=5,win_type='boxcar').mean()Out[53]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 -0.8411642000-01-06 -0.7799482000-01-07 -0.5654872000-01-08 -0.5028152000-01-09 -0.5537552000-01-10 -0.472211Freq: D, dtype: float64In [54]:ser.rolling(window=5).mean()Out[54]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 -0.8411642000-01-06 -0.7799482000-01-07 -0.5654872000-01-08 -0.5028152000-01-09 -0.5537552000-01-10 -0.472211Freq: D, dtype: float64
For some windowing functions, additional parameters must be specified:
In [55]:ser.rolling(window=5,win_type='gaussian').mean(std=0.1)Out[55]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 -1.3099892000-01-06 -1.1530002000-01-07 0.6063822000-01-08 -0.6811012000-01-09 -0.2897242000-01-10 -0.996632Freq: D, dtype: float64
Note
For.sum() with awin_type, there is no normalization done to theweights for the window. Passing custom weights of[1,1,1] will yield a differentresult than passing weights of[2,2,2], for example. When passing awin_type instead of explicitly specifying the weights, the weights arealready normalized so that the largest weight is 1.
In contrast, the nature of the.mean() calculation issuch that the weights are normalized with respect to each other. Weightsof[1,1,1] and[2,2,2] yield the same result.
Time-aware Rolling¶
New in version 0.19.0.
New in version 0.19.0 are the ability to pass an offset (or convertible) to a.rolling() method and have it producevariable sized windows based on the passed time window. For each time point, this includes all preceding values occurringwithin the indicated time delta.
This can be particularly useful for a non-regular time frequency index.
In [56]:dft=pd.DataFrame({'B':[0,1,2,np.nan,4]}, ....:index=pd.date_range('20130101 09:00:00',periods=5,freq='s')) ....:In [57]:dftOut[57]: B2013-01-01 09:00:00 0.02013-01-01 09:00:01 1.02013-01-01 09:00:02 2.02013-01-01 09:00:03 NaN2013-01-01 09:00:04 4.0
This is a regular frequency index. Using an integer window parameter works to roll along the window frequency.
In [58]:dft.rolling(2).sum()Out[58]: B2013-01-01 09:00:00 NaN2013-01-01 09:00:01 1.02013-01-01 09:00:02 3.02013-01-01 09:00:03 NaN2013-01-01 09:00:04 NaNIn [59]:dft.rolling(2,min_periods=1).sum()Out[59]: B2013-01-01 09:00:00 0.02013-01-01 09:00:01 1.02013-01-01 09:00:02 3.02013-01-01 09:00:03 2.02013-01-01 09:00:04 4.0
Specifying an offset allows a more intuitive specification of the rolling frequency.
In [60]:dft.rolling('2s').sum()Out[60]: B2013-01-01 09:00:00 0.02013-01-01 09:00:01 1.02013-01-01 09:00:02 3.02013-01-01 09:00:03 2.02013-01-01 09:00:04 4.0
Using a non-regular, but still monotonic index, rolling with an integer window does not impart any special calculation.
In [61]:dft=pd.DataFrame({'B':[0,1,2,np.nan,4]}, ....:index=pd.Index([pd.Timestamp('20130101 09:00:00'), ....:pd.Timestamp('20130101 09:00:02'), ....:pd.Timestamp('20130101 09:00:03'), ....:pd.Timestamp('20130101 09:00:05'), ....:pd.Timestamp('20130101 09:00:06')], ....:name='foo')) ....:In [62]:dftOut[62]: Bfoo2013-01-01 09:00:00 0.02013-01-01 09:00:02 1.02013-01-01 09:00:03 2.02013-01-01 09:00:05 NaN2013-01-01 09:00:06 4.0In [63]:dft.rolling(2).sum()Out[63]: Bfoo2013-01-01 09:00:00 NaN2013-01-01 09:00:02 1.02013-01-01 09:00:03 3.02013-01-01 09:00:05 NaN2013-01-01 09:00:06 NaN
Using the time-specification generates variable windows for this sparse data.
In [64]:dft.rolling('2s').sum()Out[64]: Bfoo2013-01-01 09:00:00 0.02013-01-01 09:00:02 1.02013-01-01 09:00:03 3.02013-01-01 09:00:05 NaN2013-01-01 09:00:06 4.0
Furthermore, we now allow an optionalon parameter to specify a column (rather than thedefault of the index) in a DataFrame.
In [65]:dft=dft.reset_index()In [66]:dftOut[66]: foo B0 2013-01-01 09:00:00 0.01 2013-01-01 09:00:02 1.02 2013-01-01 09:00:03 2.03 2013-01-01 09:00:05 NaN4 2013-01-01 09:00:06 4.0In [67]:dft.rolling('2s',on='foo').sum()Out[67]: foo B0 2013-01-01 09:00:00 0.01 2013-01-01 09:00:02 1.02 2013-01-01 09:00:03 3.03 2013-01-01 09:00:05 NaN4 2013-01-01 09:00:06 4.0
Time-aware Rolling vs. Resampling¶
Using.rolling() with a time-based index is quite similar toresampling. Theyboth operate and perform reductive operations on time-indexed pandas objects.
When using.rolling() with an offset. The offset is a time-delta. Take a backwards-in-time looking window, andaggregate all of the values in that window (including the end-point, but not the start-point). This is the new valueat that point in the result. These are variable sized windows in time-space for each point of the input. You will geta same sized result as the input.
When using.resample() with an offset. Construct a new index that is the frequency of the offset. For each frequencybin, aggregate points from the input within a backwards-in-time looking window that fall in that bin. The result of thisaggregation is the output for that frequency point. The windows are fixed size size in the frequency space. Your resultwill have the shape of a regular frequency between the min and the max of the original input object.
To summarize,.rolling() is a time-based window operation, while.resample() is a frequency-based window operation.
Centering Windows¶
By default the labels are set to the right edge of the window, but acenter keyword is available so the labels can be set at the center.
In [68]:ser.rolling(window=5).mean()Out[68]:2000-01-01 NaN2000-01-02 NaN2000-01-03 NaN2000-01-04 NaN2000-01-05 -0.8411642000-01-06 -0.7799482000-01-07 -0.5654872000-01-08 -0.5028152000-01-09 -0.5537552000-01-10 -0.472211Freq: D, dtype: float64In [69]:ser.rolling(window=5,center=True).mean()Out[69]:2000-01-01 NaN2000-01-02 NaN2000-01-03 -0.8411642000-01-04 -0.7799482000-01-05 -0.5654872000-01-06 -0.5028152000-01-07 -0.5537552000-01-08 -0.4722112000-01-09 NaN2000-01-10 NaNFreq: D, dtype: float64
Binary Window Functions¶
cov() andcorr() can compute moving window statistics abouttwoSeries or any combination ofDataFrame/Series orDataFrame/DataFrame. Here is the behavior in each case:
- two
Series: compute the statistic for the pairing. DataFrame/Series: compute the statistics for each column of the DataFramewith the passed Series, thus returning a DataFrame.DataFrame/DataFrame: by default compute the statistic for matching columnnames, returning a DataFrame. If the keyword argumentpairwise=Trueispassed then computes the statistic for each pair of columns, returning aPanelwhoseitemsare the dates in question (seethe next section).
For example:
In [70]:df2=df[:20]In [71]:df2.rolling(window=5).corr(df2['B'])Out[71]: A B C D2000-01-01 NaN NaN NaN NaN2000-01-02 NaN NaN NaN NaN2000-01-03 NaN NaN NaN NaN2000-01-04 NaN NaN NaN NaN2000-01-05 -0.262853 1.0 0.334449 0.1933802000-01-06 -0.083745 1.0 -0.521587 -0.5561262000-01-07 -0.292940 1.0 -0.658532 -0.458128... ... ... ... ...2000-01-14 0.519499 1.0 -0.687277 0.1928222000-01-15 0.048982 1.0 0.167669 -0.0614632000-01-16 0.217190 1.0 0.167564 -0.3260342000-01-17 0.641180 1.0 -0.164780 -0.1114872000-01-18 0.130422 1.0 0.322833 0.6323832000-01-19 0.317278 1.0 0.384528 0.8136562000-01-20 0.293598 1.0 0.159538 0.742381[20 rows x 4 columns]
Computing rolling pairwise covariances and correlations¶
In financial data analysis and other fields it’s common to compute covarianceand correlation matrices for a collection of time series. Often one is alsointerested in moving-window covariance and correlation matrices. This can bedone by passing thepairwise keyword argument, which in the case ofDataFrame inputs will yield aPanel whoseitems are the dates inquestion. In the case of a single DataFrame argument thepairwise argumentcan even be omitted:
Note
Missing values are ignored and each entry is computed using the pairwisecomplete observations. Please see thecovariance section forcaveats associated with this method ofcalculating covariance and correlation matrices.
In [72]:covs=df[['B','C','D']].rolling(window=50).cov(df[['A','B','C']],pairwise=True)In [73]:covs[df.index[-50]]Out[73]: A B CB 2.667506 1.671711 1.938634C 8.513843 1.938634 10.556436D -7.714737 -1.434529 -7.082653
In [74]:correls=df.rolling(window=50).corr()In [75]:correls[df.index[-50]]Out[75]: A B C DA 1.000000 0.604221 0.767429 -0.776170B 0.604221 1.000000 0.461484 -0.381148C 0.767429 0.461484 1.000000 -0.748863D -0.776170 -0.381148 -0.748863 1.000000
You can efficiently retrieve the time series of correlations between twocolumns using.loc indexing:
In [76]:correls.loc[:,'A','C'].plot()Out[76]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24ba82150>
Aggregation¶
Once theRolling,Expanding orEWM objects have been created, several methods are available toperform multiple computations on the data. This is very similar to a.groupby(...).agg seenhere.
In [77]:dfa=pd.DataFrame(np.random.randn(1000,3), ....:index=pd.date_range('1/1/2000',periods=1000), ....:columns=['A','B','C']) ....:In [78]:r=dfa.rolling(window=60,min_periods=1)In [79]:rOut[79]:Rolling[window=60,min_periods=1,center=False,axis=0]
We can aggregate by passing a function to the entire DataFrame, or select a Series (or multiple Series) via standard getitem.
In [80]:r.aggregate(np.sum)Out[80]: A B C2000-01-01 0.314226 -0.001675 0.0718232000-01-02 1.206791 0.678918 -0.2678172000-01-03 1.421701 0.600508 -0.4454822000-01-04 1.912539 -0.759594 1.1469742000-01-05 2.919639 -0.061759 -0.7436172000-01-06 2.665637 1.298392 -0.8035292000-01-07 2.513985 1.923089 -1.928308... ... ... ...2002-09-20 1.447669 -12.360302 2.7343812002-09-21 1.871783 -13.896542 3.0861022002-09-22 2.540658 -12.594402 3.1625422002-09-23 2.974674 -12.727703 3.8610052002-09-24 1.391366 -13.584590 3.7906832002-09-25 2.027313 -15.083214 3.3778962002-09-26 1.290363 -13.569459 3.809884[1000 rows x 3 columns]In [81]:r['A'].aggregate(np.sum)Out[81]:2000-01-01 0.3142262000-01-02 1.2067912000-01-03 1.4217012000-01-04 1.9125392000-01-05 2.9196392000-01-06 2.6656372000-01-07 2.513985 ...2002-09-20 1.4476692002-09-21 1.8717832002-09-22 2.5406582002-09-23 2.9746742002-09-24 1.3913662002-09-25 2.0273132002-09-26 1.290363Freq: D, Name: A, dtype: float64In [82]:r[['A','B']].aggregate(np.sum)Out[82]: A B2000-01-01 0.314226 -0.0016752000-01-02 1.206791 0.6789182000-01-03 1.421701 0.6005082000-01-04 1.912539 -0.7595942000-01-05 2.919639 -0.0617592000-01-06 2.665637 1.2983922000-01-07 2.513985 1.923089... ... ...2002-09-20 1.447669 -12.3603022002-09-21 1.871783 -13.8965422002-09-22 2.540658 -12.5944022002-09-23 2.974674 -12.7277032002-09-24 1.391366 -13.5845902002-09-25 2.027313 -15.0832142002-09-26 1.290363 -13.569459[1000 rows x 2 columns]
As you can see, the result of the aggregation will have the selected columns, or allcolumns if none are selected.
Applying multiple functions at once¶
With windowed Series you can also pass a list or dict of functions to doaggregation with, outputting a DataFrame:
In [83]:r['A'].agg([np.sum,np.mean,np.std])Out[83]: sum mean std2000-01-01 0.314226 0.314226 NaN2000-01-02 1.206791 0.603396 0.4089482000-01-03 1.421701 0.473900 0.3659592000-01-04 1.912539 0.478135 0.2989252000-01-05 2.919639 0.583928 0.3506822000-01-06 2.665637 0.444273 0.4641152000-01-07 2.513985 0.359141 0.479828... ... ... ...2002-09-20 1.447669 0.024128 1.0348272002-09-21 1.871783 0.031196 1.0314172002-09-22 2.540658 0.042344 1.0263412002-09-23 2.974674 0.049578 1.0300212002-09-24 1.391366 0.023189 1.0247932002-09-25 2.027313 0.033789 1.0220992002-09-26 1.290363 0.021506 1.024751[1000 rows x 3 columns]
If a dict is passed, the keys will be used to name the columns. Otherwise thefunction’s name (stored in the function object) will be used.
In [84]:r['A'].agg({'result1':np.sum, ....:'result2':np.mean}) ....:Out[84]: result2 result12000-01-01 0.314226 0.3142262000-01-02 0.603396 1.2067912000-01-03 0.473900 1.4217012000-01-04 0.478135 1.9125392000-01-05 0.583928 2.9196392000-01-06 0.444273 2.6656372000-01-07 0.359141 2.513985... ... ...2002-09-20 0.024128 1.4476692002-09-21 0.031196 1.8717832002-09-22 0.042344 2.5406582002-09-23 0.049578 2.9746742002-09-24 0.023189 1.3913662002-09-25 0.033789 2.0273132002-09-26 0.021506 1.290363[1000 rows x 2 columns]
On a widowed DataFrame, you can pass a list of functions to apply to eachcolumn, which produces an aggregated result with a hierarchical index:
In [85]:r.agg([np.sum,np.mean])Out[85]: A B C sum mean sum mean sum mean2000-01-01 0.314226 0.314226 -0.001675 -0.001675 0.071823 0.0718232000-01-02 1.206791 0.603396 0.678918 0.339459 -0.267817 -0.1339082000-01-03 1.421701 0.473900 0.600508 0.200169 -0.445482 -0.1484942000-01-04 1.912539 0.478135 -0.759594 -0.189899 1.146974 0.2867442000-01-05 2.919639 0.583928 -0.061759 -0.012352 -0.743617 -0.1487232000-01-06 2.665637 0.444273 1.298392 0.216399 -0.803529 -0.1339212000-01-07 2.513985 0.359141 1.923089 0.274727 -1.928308 -0.275473... ... ... ... ... ... ...2002-09-20 1.447669 0.024128 -12.360302 -0.206005 2.734381 0.0455732002-09-21 1.871783 0.031196 -13.896542 -0.231609 3.086102 0.0514352002-09-22 2.540658 0.042344 -12.594402 -0.209907 3.162542 0.0527092002-09-23 2.974674 0.049578 -12.727703 -0.212128 3.861005 0.0643502002-09-24 1.391366 0.023189 -13.584590 -0.226410 3.790683 0.0631782002-09-25 2.027313 0.033789 -15.083214 -0.251387 3.377896 0.0562982002-09-26 1.290363 0.021506 -13.569459 -0.226158 3.809884 0.063498[1000 rows x 6 columns]
Passing a dict of functions has different behavior by default, see the nextsection.
Applying different functions to DataFrame columns¶
By passing a dict toaggregate you can apply a different aggregation to thecolumns of a DataFrame:
In [86]:r.agg({'A':np.sum, ....:'B':lambdax:np.std(x,ddof=1)}) ....:Out[86]: A B2000-01-01 0.314226 NaN2000-01-02 1.206791 0.4824372000-01-03 1.421701 0.4178252000-01-04 1.912539 0.8514682000-01-05 2.919639 0.8374742000-01-06 2.665637 0.9354412000-01-07 2.513985 0.867770... ... ...2002-09-20 1.447669 1.0842592002-09-21 1.871783 1.0883682002-09-22 2.540658 1.0847072002-09-23 2.974674 1.0849362002-09-24 1.391366 1.0792682002-09-25 2.027313 1.0913342002-09-26 1.290363 1.060255[1000 rows x 2 columns]
The function names can also be strings. In order for a string to be valid itmust be implemented on the windowed object
In [87]:r.agg({'A':'sum','B':'std'})Out[87]: A B2000-01-01 0.314226 NaN2000-01-02 1.206791 0.4824372000-01-03 1.421701 0.4178252000-01-04 1.912539 0.8514682000-01-05 2.919639 0.8374742000-01-06 2.665637 0.9354412000-01-07 2.513985 0.867770... ... ...2002-09-20 1.447669 1.0842592002-09-21 1.871783 1.0883682002-09-22 2.540658 1.0847072002-09-23 2.974674 1.0849362002-09-24 1.391366 1.0792682002-09-25 2.027313 1.0913342002-09-26 1.290363 1.060255[1000 rows x 2 columns]
Furthermore you can pass a nested dict to indicate different aggregations on different columns.
In [88]:r.agg({'A':['sum','std'],'B':['mean','std']})Out[88]: A B sum std mean std2000-01-01 0.314226 NaN -0.001675 NaN2000-01-02 1.206791 0.408948 0.339459 0.4824372000-01-03 1.421701 0.365959 0.200169 0.4178252000-01-04 1.912539 0.298925 -0.189899 0.8514682000-01-05 2.919639 0.350682 -0.012352 0.8374742000-01-06 2.665637 0.464115 0.216399 0.9354412000-01-07 2.513985 0.479828 0.274727 0.867770... ... ... ... ...2002-09-20 1.447669 1.034827 -0.206005 1.0842592002-09-21 1.871783 1.031417 -0.231609 1.0883682002-09-22 2.540658 1.026341 -0.209907 1.0847072002-09-23 2.974674 1.030021 -0.212128 1.0849362002-09-24 1.391366 1.024793 -0.226410 1.0792682002-09-25 2.027313 1.022099 -0.251387 1.0913342002-09-26 1.290363 1.024751 -0.226158 1.060255[1000 rows x 4 columns]
Expanding Windows¶
A common alternative to rolling statistics is to use anexpanding window,which yields the value of the statistic with all the data available up to thatpoint in time.
These follow a similar interface to.rolling, with the.expanding methodreturning anExpanding object.
As these calculations are a special case of rolling statistics,they are implemented in pandas such that the following two calls are equivalent:
In [89]:df.rolling(window=len(df),min_periods=1).mean()[:5]Out[89]: A B C D2000-01-01 -1.388345 3.317290 0.344542 -0.0369682000-01-02 -1.123132 3.622300 1.675867 0.5953002000-01-03 -0.628502 3.626503 2.455240 1.0601582000-01-04 -0.768740 3.888917 2.451354 1.2818742000-01-05 -0.824034 4.108035 2.556112 1.140723In [90]:df.expanding(min_periods=1).mean()[:5]Out[90]: A B C D2000-01-01 -1.388345 3.317290 0.344542 -0.0369682000-01-02 -1.123132 3.622300 1.675867 0.5953002000-01-03 -0.628502 3.626503 2.455240 1.0601582000-01-04 -0.768740 3.888917 2.451354 1.2818742000-01-05 -0.824034 4.108035 2.556112 1.140723
These have a similar set of methods to.rolling methods.
Method Summary¶
| Function | Description |
|---|---|
count() | Number of non-null observations |
sum() | Sum of values |
mean() | Mean of values |
median() | Arithmetic median of values |
min() | Minimum |
max() | Maximum |
std() | Unbiased standard deviation |
var() | Unbiased variance |
skew() | Unbiased skewness (3rd moment) |
kurt() | Unbiased kurtosis (4th moment) |
quantile() | Sample quantile (value at %) |
apply() | Generic apply |
cov() | Unbiased covariance (binary) |
corr() | Correlation (binary) |
Aside from not having awindow parameter, these functions have the sameinterfaces as their.rolling counterparts. Like above, the parameters theyall accept are:
min_periods: threshold of non-null data points to require. Defaults tominimum needed to compute statistic. NoNaNswill be output oncemin_periodsnon-null data points have been seen.center: boolean, whether to set the labels at the center (default is False)
Note
The output of the.rolling and.expanding methods do not return aNaN if there are at leastmin_periods non-null values in the currentwindow. This differs fromcumsum,cumprod,cummax, andcummin, which returnNaN in the output wherever aNaN isencountered in the input.
An expanding window statistic will be more stable (and less responsive) thanits rolling window counterpart as the increasing window size decreases therelative impact of an individual data point. As an example, here is themean() output for the previous time series dataset:
In [91]:s.plot(style='k--')Out[91]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24c1d97d0>In [92]:s.expanding().mean().plot(style='k')Out[92]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24c1d97d0>
Exponentially Weighted Windows¶
A related set of functions are exponentially weighted versions of several ofthe above statistics. A similar interface to.rolling and.expanding is accessedthru the.ewm method to receive anEWM object.A number of expanding EW (exponentially weighted)methods are provided:
| Function | Description |
|---|---|
mean() | EW moving average |
var() | EW moving variance |
std() | EW moving standard deviation |
corr() | EW moving correlation |
cov() | EW moving covariance |
In general, a weighted moving average is calculated as
y_t = \frac{\sum_{i=0}^t w_i x_{t-i}}{\sum_{i=0}^t w_i},
wherex_t is the input andy_t is the result.
The EW functions support two variants of exponential weights.The default,adjust=True, uses the weightsw_i = (1 - \alpha)^iwhich gives
y_t = \frac{x_t + (1 - \alpha)x_{t-1} + (1 - \alpha)^2 x_{t-2} + ...+ (1 - \alpha)^t x_{0}}{1 + (1 - \alpha) + (1 - \alpha)^2 + ...+ (1 - \alpha)^t}
Whenadjust=False is specified, moving averages are calculated as
y_0 &= x_0 \\y_t &= (1 - \alpha) y_{t-1} + \alpha x_t,
which is equivalent to using weights
w_i = \begin{cases} \alpha (1 - \alpha)^i & \text{if } i < t \\ (1 - \alpha)^i & \text{if } i = t.\end{cases}
Note
These equations are sometimes written in terms of\alpha' = 1 - \alpha, e.g.
y_t = \alpha' y_{t-1} + (1 - \alpha') x_t.
The difference between the above two variants arises because we aredealing with series which have finite history. Consider a series of infinitehistory:
y_t = \frac{x_t + (1 - \alpha)x_{t-1} + (1 - \alpha)^2 x_{t-2} + ...}{1 + (1 - \alpha) + (1 - \alpha)^2 + ...}
Noting that the denominator is a geometric series with initial term equal to 1and a ratio of1 - \alpha we have
y_t &= \frac{x_t + (1 - \alpha)x_{t-1} + (1 - \alpha)^2 x_{t-2} + ...}{\frac{1}{1 - (1 - \alpha)}}\\&= [x_t + (1 - \alpha)x_{t-1} + (1 - \alpha)^2 x_{t-2} + ...] \alpha \\&= \alpha x_t + [(1-\alpha)x_{t-1} + (1 - \alpha)^2 x_{t-2} + ...]\alpha \\&= \alpha x_t + (1 - \alpha)[x_{t-1} + (1 - \alpha) x_{t-2} + ...]\alpha\\&= \alpha x_t + (1 - \alpha) y_{t-1}
which shows the equivalence of the above two variants for infinite series.Whenadjust=True we havey_0 = x_0 and from the lastrepresentation above we havey_t = \alpha x_t + (1 - \alpha) y_{t-1},therefore there is an assumption thatx_0 is not an ordinary valuebut rather an exponentially weighted moment of the infinite series up to thatpoint.
One must have0 < \alpha \leq 1, and while since version 0.18.0it has been possible to pass\alpha directly, it’s often easierto think about either thespan,center of mass (com) orhalf-lifeof an EW moment:
\alpha = \begin{cases} \frac{2}{s + 1}, & \text{for span}\ s \geq 1\\ \frac{1}{1 + c}, & \text{for center of mass}\ c \geq 0\\ 1 - \exp^{\frac{\log 0.5}{h}}, & \text{for half-life}\ h > 0 \end{cases}
One must specify precisely one ofspan,center of mass,half-lifeandalpha to the EW functions:
- Span corresponds to what is commonly called an “N-day EW moving average”.
- Center of mass has a more physical interpretation and can be thought ofin terms of span:c = (s - 1) / 2.
- Half-life is the period of time for the exponential weight to reduce toone half.
- Alpha specifies the smoothing factor directly.
Here is an example for a univariate time series:
In [93]:s.plot(style='k--')Out[93]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24ba46c10>In [94]:s.ewm(span=20).mean().plot(style='k')Out[94]:<matplotlib.axes._subplots.AxesSubplotat0x7fd24ba46c10>
EWM has amin_periods argument, which has the samemeaning it does for all the.expanding and.rolling methods:no output values will be set until at leastmin_periods non-null valuesare encountered in the (expanding) window.(This is a change from versions prior to 0.15.0, in which themin_periodsargument affected only themin_periods consecutive entries starting at thefirst non-null value.)
EWM also has anignore_na argument, which deterines howintermediate null values affect the calculation of the weights.Whenignore_na=False (the default), weights are calculated based on absolutepositions, so that intermediate null values affect the result.Whenignore_na=True (which reproduces the behavior in versions prior to 0.15.0),weights are calculated by ignoring intermediate null values.For example, assumingadjust=True, ifignore_na=False, the weightedaverage of3,NaN,5 would be calculated as
\frac{(1-\alpha)^2 \cdot 3 + 1 \cdot 5}{(1-\alpha)^2 + 1}
Whereas ifignore_na=True, the weighted average would be calculated as
\frac{(1-\alpha) \cdot 3 + 1 \cdot 5}{(1-\alpha) + 1}.
Thevar(),std(), andcov() functions have abias argument,specifying whether the result should contain biased or unbiased statistics.For example, ifbias=True,ewmvar(x) is calculated asewmvar(x)=ewma(x**2)-ewma(x)**2;whereas ifbias=False (the default), the biased variance statisticsare scaled by debiasing factors
\frac{\left(\sum_{i=0}^t w_i\right)^2}{\left(\sum_{i=0}^t w_i\right)^2 - \sum_{i=0}^t w_i^2}.
(Forw_i = 1, this reduces to the usualN / (N - 1) factor,withN = t + 1.)SeeWeighted Sample Variancefor further details.