Pandas, Using generated values while iterating through rows within grouped data

Question

I'm pretty new to Pandas and programming in general but I've always been able to find the answer to any problem through google until now. Sorry about the not terribly descriptive question, hopefully someone can come up with something clearer.

I'm trying to group data together, perform functions on that data, update a column and then use the data from that column on the next group of data.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.random(9),columns=['A'])
df['B'] = [1,1,1,2,2,3,3,3,3]
df['C'] = np.nan
df['D'] = np.nan
df.loc[0:2,'C'] = 500

Giving me

    A           B   C       D
0   0.825828    1   500.0   NaN
1   0.218618    1   500.0   NaN
2   0.902476    1   500.0   NaN
3   0.452525    2   NaN     NaN
4   0.513505    2   NaN     NaN
5   0.089975    3   NaN     NaN
6   0.282479    3   NaN     NaN
7   0.774286    3   NaN     NaN
8   0.408501    3   NaN     NaN

The 500 in column C is the initial condition. I want to group the data by column B and perform the following function on the first group

def function1(row):
    return row['A']*row['C']/6

giving me

    A           B   C       D
0   0.825828    1   500.0   68.818971
1   0.218618    1   500.0   18.218145
2   0.902476    1   500.0   75.206313
3   0.452525    2   NaN     NaN
4   0.513505    2   NaN     NaN
5   0.089975    3   NaN     NaN
6   0.282479    3   NaN     NaN
7   0.774286    3   NaN     NaN
8   0.408501    3   NaN     NaN

I then want to sum the first three values in D and add them to the last value in C and making this value the group 2 value

    A           B   C           D
0   0.825828    1   500.000000  68.818971
1   0.218618    1   500.000000  18.218145
2   0.902476    1   500.000000  75.206313
3   0.452525    2   662.243429  NaN
4   0.513505    2   662.243429  NaN
5   0.089975    3   NaN         NaN
6   0.282479    3   NaN         NaN
7   0.774286    3   NaN         NaN
8   0.408501    3   NaN         NaN

I then perform function1 on group 2 and repeat until I end up with this

    A           B   C           D
0   0.825828    1   500.000000  68.818971
1   0.218618    1   500.000000  18.218145
2   0.902476    1   500.000000  75.206313
3   0.452525    2   662.243429  49.946896
4   0.513505    2   662.243429  56.677505
5   0.089975    3   768.867830  11.529874
6   0.282479    3   768.867830  36.198113
7   0.774286    3   768.867830  99.220591
8   0.408501    3   768.867830  52.347246

The dataframe will consist of hundreds of rows. I've been trying various groupby, apply combinations but I'm completely stumped.

Thanks

Answer 1

Here is a solution:

df['D'] = df['A'] * df['C']/6

for i in df['B'].unique()[1:]:
    df.loc[df['B']==i, 'C'] = df['D'].sum()
    df.loc[df['B']==i, 'D'] = df['A'] * df['C']/6