pandas - In python how would you create two columns that iterate into eachother?

Question

The question translate into this I have a simple dataframe lets call it df

id  k   t
2A  1   100
2A  2   100
2A  NaN 100
4C  3   80
4C  4   80
4C  1   80
3B  NaN 120
3B  7   120
3B  4   120

And I want to create a new column that is called t and t1. t1 is based on t and k and id. In this scenario I have only 3 Id's 2A, 4C and 3B. The final output will be.

id  k   t   t1
2A  1   100 99
2A  2   99  97
2A  NaN 97  97
4C  3   80  77
4C  4   77  73
4C  1   73  72
3B  NaN 120 120
3B  7   120 113
3B  4   113 109

So the idea is as follows

in Which t1 is simply

df['t1']=df['t']-df['k']

The conversion of values on t in the final df is the one that I don't know how to do because the first value of every id has to be based on the originals df["t"], but afterwards on df[t1] as the picture shows.

question from:https://stackoverflow.com/questions/65829898/in-python-how-would-you-create-two-columns-that-iterate-into-eachother

Answer 1

Let us try cumsum with groupby

df['t1'] = df.t-df.fillna(0).groupby('id').k.cumsum()
df['t'] = df['t1'] + df['k'].fillna(0)
df
Out[85]: 
   id    k      t     t1
0  2A  1.0  100.0   99.0
1  2A  2.0   99.0   97.0
2  2A  NaN   97.0   97.0
3  4C  3.0   80.0   77.0
4  4C  4.0   77.0   73.0
5  4C  1.0   73.0   72.0
6  3B  NaN  120.0  120.0
7  3B  7.0  120.0  113.0
8  3B  4.0  113.0  109.0