Take product of columns in dataframe with lags

Have following dataframe.

A = pd.Series([2, 3, 4, 5], index=[1, 2, 3, 4])
B = pd.Series([6, 7, 8, 9], index=[1, 2, 3, 4])
Aw = pd.Series([0.25, 0.3, 0.33, 0.36], index=[1, 2, 3, 4])
Bw = pd.Series([0.75, 0.7, 0.67, 0.65], index=[1, 2, 3, 4])
df = pd.DataFrame({'A': A, 'B': B, 'Aw': Aw, 'Bw', Bw})
df

Index A B Aw   Bw
1     2 6 0.25 0.75
2     3 7 0.30 0.70
3     4 8 0.33 0.67
4     5 9 0.36 0.64

What I would like to do is multiply 'A' and lag of 'Aw' and likewise 'B' with 'Bw'. The resulting dataframe will look like the following:

Index A B Aw   Bw   A_ctr B_ctr
1     2 6 NaN  NaN  NaN   NaN
2     3 7 0.25 0.75 0.75  5.25
3     4 8 0.3  0.7  1.2   5.6
4     5 9 0.33 0.64 1.65  5.76

Thank you in advance

1 answer

  • answered 2018-10-11 19:16 sacul

    To get your desired output, first shift Aw and Bw, then multiply them by A and B:

    df[['Aw','Bw']] = df[['Aw','Bw']].shift()
    
    df[['A_ctr','B_ctr']] = df[['A','B']].values*df[['Aw','Bw']]
    
       A  B    Aw    Bw  A_ctr  B_ctr
    1  2  6   NaN   NaN    NaN    NaN
    2  3  7  0.25  0.75   0.75   5.25
    3  4  8  0.30  0.70   1.20   5.60
    4  5  9  0.33  0.67   1.65   6.03