Calculating Pandas DataFrame Autocorrelation Along Each Column

Question

I want to calculate the delay length autocorrelation coefficients of one of the Pandas DataFrame columns. Excerpt from my data:

RF PC CD PN DN P year 1890 NaN NaN NaN NaN NaN NaN NaN 1891 -0.028470 -0.052632 0.042254 0.081818 -0.045541 0.047619 -0.016974 1892 -0.249084 0.000000 0.027027 0.067227 0.099404 0.045455 0.122337 1893 0.653659 0.000000 0.000000 0.039370 -0.135624 0.043478 -0.142062 

Along the year , I want to calculate the lag autocorrelation, one for each column ( RF , PC , etc.).

To calculate autocorrelation, I selected two time series for each column, whose start and end data differed by one year, and then calculated the correlation coefficients with numpy.corrcoef .

For example, I wrote:

numpy.corrcoef(data[['C']][1:-1],data[['C']][2:])

(the entire DataFrame is called data ).
However, the team, unfortunately, returned:

 array([[ nan, nan, nan, ..., nan, nan, nan], [ nan, nan, nan, ..., nan, nan, nan], [ nan, nan, nan, ..., nan, nan, nan], ..., [ nan, nan, nan, ..., nan, nan, nan], [ nan, nan, nan, ..., nan, nan, nan], [ nan, nan, nan, ..., nan, nan, nan]]) 

Can someone kindly advise me how to calculate autocorrelation?

Answer 1

you should use:

 numpy.corrcoef(df['C'][1:-1], df['C'][2:]) 

df[['C']] is a single-column data frame, and df['C'] is a series containing the values ​​in your column C.

Answer 2

This is a late answer, but for future users you can also use pandas.Series.autocorr (), which calculates the autocorrelation of lag-N (default = 1) in a series:

 df['C'].autocorr(lag=1) 

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.autocorr.html#pandas.Series.autocorr

Answer 3

.autocorr applies to Series, not DataFrames. You can use .apply to apply to a DataFrame:

 def df_autocorr(df, lag=1, axis=0): """Compute full-sample column-wise autocorrelation for a DataFrame.""" return df.apply(lambda col: col.autocorr(lag), axis=axis) d1 = DataFrame(np.random.randn(100, 6)) df_autocorr(d1) Out[32]: 0 0.141 1 -0.028 2 -0.031 3 0.114 4 -0.121 5 0.060 dtype: float64 

You can also calculate moving autocorrelation with the specified window as follows (this is what . Autocorr works under the hood):

 def df_rolling_autocorr(df, window, lag=1): """Compute rolling column-wise autocorrelation for a DataFrame.""" return (df.rolling(window=window) .corr(df.shift(lag))) # could .dropna() here df_rolling_autocorr(d1, window=21).dropna().head() Out[38]: 0 1 2 3 4 5 21 -0.173 -0.367 0.142 -0.044 -0.080 0.012 22 0.015 -0.341 0.250 -0.036 0.023 -0.012 23 0.038 -0.329 0.279 -0.026 0.075 -0.121 24 -0.025 -0.361 0.319 0.117 0.031 -0.120 25 0.119 -0.320 0.181 -0.011 0.038 -0.111