Multi tool use

Pythons Pandas, applying function on a GroupBy Object “indexes”

I am sure there is a better solution to my current problem in Pandas :

I have a Dataframe with columns like

df.columns = ['bar','foo',0,1,2]


I want to call a SQL database, where I filter with the tuple of bar and foo values, calculate something with the returned result using columns 0 and 1, and return the result to column 3.

For the moment I do this with a groupby, and didn't find anywhere the proper way to use the transform method after this.

groups = df.groupby('bar','foo')
for (bar,foo), group in groups:
db_results = read_db(bar,foo)
df[3].loc(groups.groups((bar,foo))) = f(db_results,df[0].loc(...),df[1].loc(...))


I believe this is a little bit ugly, right ? There must be something more with less redudency than the df.loc everywhere. But I don't how to access bar, foo using tranform method of Groupby objects.

Thanks for your help

1 Answer
1

I think need GroupBy.apply with custom function:

GroupBy.apply

def func(x):
db_results = read_db(x.name[0],x.name[1])
x[3] = f(db_results, x[0], x[1])
return x

df = df.groupby(['bar','foo']).apply(func)


Sample:

df = pd.DataFrame({0:list('abcdef'),
'foo':[4,5,4,5,5,4],
1:[7,8,9,4,2,3],
2:[1,3,5,7,1,0],
'bar':list('aaabbb')}, columns=['bar','foo',0,1,2])
print (df)
bar foo 0 1 2
0 a 4 a 7 1
1 a 5 b 8 3
2 a 4 c 9 5
3 b 5 d 4 7
4 b 5 e 2 1
5 b 4 f 3 0

def func(x):
print (x.name[0])
x[3] = (x[2] + x[1]) * x.name[1]
return x

df = df.groupby(['bar','foo']).apply(func)
print (df)
bar foo 0 1 2 3
0 a 4 a 7 1 32
1 a 5 b 8 3 55
2 a 4 c 9 5 56
3 b 5 d 4 7 55
4 b 5 e 2 1 15
5 b 4 f 3 0 12


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.