0

Can someone help me? I want to run correlation with fixed Series in df and rolling DataFrame in df1. Desired result should be all 1 from index 1 to 6 but I got NaN.

df = pd.Series([2,3,4])
df1 = pd.DataFrame({'A':[1,2,3,4,5,6,7],
                   'B':[10,20,30,40,50,60,70],
                   'C':[100,200,300,400,500,600,700]})

def x(y):
    return y.corr(df)

df2 = df1.rolling(window=len(df),min_periods=1).apply(x)

[out] =

    A   B   C
0   NaN NaN NaN
1   1.0 1.0 1.0
2   1.0 1.0 1.0
3   1.0 1.0 1.0
4   NaN NaN NaN
5   NaN NaN NaN
6   NaN NaN NaN

Desired result:

    A   B   C
0   NaN NaN NaN
1   1.0 1.0 1.0
2   1.0 1.0 1.0
3   1.0 1.0 1.0
4   1.0 1.0 1.0
5   1.0 1.0 1.0
6   1.0 1.0 1.0
stvlam22
  • 63
  • 7
  • I think you might want [expanding.apply](https://pandas.pydata.org/docs/reference/api/pandas.core.window.expanding.Expanding.apply.html) `df2 = df1.expanding().apply(x)` (or `df2 = df1.expanding(min_periods=2).apply(x)` to avoid a RuntimeWarning) but it's a bit hard to tell with such generic expected output. – Henry Ecker Oct 13 '22 at 04:06
  • Thanks Henry for the feedback, it is helpful, but i found that the NaN values in index 4,5,6 is a copy from previous row. – stvlam22 Oct 13 '22 at 06:32
  • for instance, I need the operation in: index 4, column A = correlation between df[2,3,4] and df1['A'][3,4,5] index 5,col A = correlation between df[2,3,4] and df1['A'][4,5,6]. index 6,col A =correlation between df[2,3,4] and df1['A'][5,6,7] and do the same operation with other columns – stvlam22 Oct 13 '22 at 06:47
  • i found some reference here https://stackoverflow.com/questions/64913597/how-to-get-correlation-for-a-rolling-pandas-series-and-a-fixed-series but it use pearsonr which does not deal with min_periods – stvlam22 Oct 13 '22 at 06:54

1 Answers1

0

Create same index in y and df, here is default index in df, so is necessary Series.reset_index with drop=True:

def x(y):
    return y.reset_index(drop=True).corr(df)

df2 = df1.rolling(window=len(df),min_periods=1).apply(x)
print (df2)
     A    B    C
0  NaN  NaN  NaN
1  1.0  1.0  1.0
2  1.0  1.0  1.0
3  1.0  1.0  1.0
4  1.0  1.0  1.0
5  1.0  1.0  1.0
6  1.0  1.0  1.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252