Effiecient way to multiply a pandas dataframe column at index n with another column in same dataframe at index n-k

Viewed 57

I have a following pandas dataframe

idx day col_1 col_2 col_3 col_4
 1   1    a    A     4     5
 2   1    b    B     5     8
 3   1    a    B     4     11
 4   2    a    A     3     8 
 5   2    b    B     3     6
 6   2    a    B     4     11
 7   3    b    B     5     1

Now leaving the rows with day=1, I want to multiply col_3 values of remaining days with col_4 values of day before, such that both rows have same col_1 and col_2 values and replace the value in col_3. For example, In the dataframe for row 4 (idx=4), I want to go back a before such that I have same values for col_1 and col_2, which will be row with idx=1. Now, I replace current value of col_3 in row 4 with (old value of col_3 in idx=4)*(col_4 value in idx=1), which is 3*5=15. I want to continue this to the end of the dataframe. So My new dataframe will look like this,

    idx day col_1 col_2 col_3 col_4
     1   1    a    A     4     5
     2   1    b    B     5     8
     3   1    a    B     4     11
     4   2    a    A     15    8 
     5   2    b    B     24    6
     6   2    a    B     44    11
     7   3    b    B     30    1

For every day we will have all the three combinations of col_1 and col_2. The way which I use to achieve this is using a for loop like this,

for i in range(3,data_frame.shape[0]):
    day_ = data_frame['day'].iloc[i]
    col1 = data_frame['col_1'].iloc[i]
    col2 = data_frame['col_2'].iloc[i]
    col3 = data_frame['col_3'].iloc[i]
    f = data_frame.loc[(data_frame['col_1']==col1)&(data_frame['col_2']==col2)&(data_frame['day']==day_-1),'col_4'].values[0]
    data_frame.loc[(data_frame['col_1']==col1)&(data_frame['col_2']==col2)&(data_frame['day']==day_),'col_3']=f*col3

This code is very inefficient because the dataframe I'm working with has thousands of rows and multiple columns that I need to match before performing the operation. Also in my case I should go back to 7 days instead of going back 1 day as shown in the example. I assume there should be an efficient way to do this using groupby and loc operations but I couldn't come up with it. Is there an efficient to do this?

1 Answers

Some logic with groupby and shift

df['new'] = df.groupby(['col_1','col_2'])['col_4'].shift().fillna(1).mul(df['col_3'])
0     4.0
1     5.0
2     4.0
3    15.0
4    24.0
5    44.0
6    30.0
dtype: float64
Related