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?