CODEWITHSUNDEEP
pythonpandas
Cypress
Cypress

Reputation: 357

loop within groupby and also change the first row for each group

i need to find the first row of each group and set a value. then i need to calculate the rest rows based on values from the previous row. I know there are similar answers in stack overflow but i still can't really find the solution for it.

Here is what I have tried so far:

import numpy as np
import pandas as pd

d={'type':['a','b','a','b','a','b','a','b','a','b','a','b']}
test=pd.DataFrame(data=d)
test = test.groupby('type')

for group,df in test:
    # print(group,df)
    df.loc[0,'value']=800
    for i in range(1,len(df)):
        df.loc[i,'value']=df.loc[i-1,'value']*0.5

However, this doesn't return what i want.

type    value
a   800
a   400
a   200
a   100
a   50
b   800
b   400
b   200
b   100
b   50

Upvotes: 3

Views: 1094

Answers (2)

Ben.T
Ben.T

Reputation: 29635

you can use cumprod after a groupby as you always multiply the previous value by 0.5 and then with some adjustment to get the right result such as:

d={'type':['a','b','a','b','a','b','a','b','a','b','a','b']}
test=pd.DataFrame(data=d)
test['value'] = test.assign(value=0.5).groupby('type').cumprod()*800*2
print (test)
   type  value
0     a  800.0
1     b  800.0
2     a  400.0
3     b  400.0
4     a  200.0
5     b  200.0
6     a  100.0
7     b  100.0
8     a   50.0
9     b   50.0
10    a   25.0
11    b   25.0

Edit: thanks to @piRSquared idea, you can also use cumcount per group as the power of 0.5 multiply by the first value 800:

test['value'] = 800 * .5 ** test.groupby('type').cumcount()

that gives the same result than above

Upvotes: 2

jezrael
jezrael

Reputation: 862661

It is possible with custom function and iloc for select and set by positions, not by index labels:

def func(group):
    group.loc[group.index[0],'value']=800
    pos = group.columns.get_loc('value')
    for i in range(1,len(group)):
        group.iloc[i,pos]=group.iloc[i-1,pos]*0.5
    return group

test1 = test.sort_values('type').groupby('type').apply(func)
print (test1)
   type  value
0     a  800.0
2     a  400.0
4     a  200.0
6     a  100.0
8     a   50.0
10    a   25.0
1     b  800.0
3     b  400.0
5     b  200.0
7     b  100.0
9     b   50.0
11    b   25.0

Upvotes: 3

Related Questions