I have this table
uname sid usage
0 Ahmad a 5
1 Ahmad a 7
2 Ahmad a 10
3 Ahmad b 2
4 Mohamad c 6
5 Mohamad c 7
6 Mohamad c 9
I want to group by uname and side, and have usage column = group.max - group.min. But if group count is 1 return group max
the out put should be
uname sid usage
0 Ahmad a 5
1 Ahmad b 2
2 Mohamad c 3
First, use agg to grab min, max, and size of each group.
Then multiply min by size > 1. When it is, it will equal min, else 0. Then subtract that from max.
d1 = df.groupby(['uname', 'sid']).usage.agg(['min', 'max', 'size'])
d1['max'].sub(d1['min'].mul(d1['size'].gt(1))).reset_index(name='usage')
d1['max'].sub(d1['min'].mul(d1['size'].gt(1))).reset_index(name='usage')You can use groupby with apply difference max and min if length is more as 1 else max:
df = df.groupby(['uname','sid'])['usage']
.apply(lambda x: x.max()-x.min() if len(x) > 1 else x.max())
.reset_index()
print (df)
uname sid usage
0 Ahmad a 5
1 Ahmad b 2
2 Mohamad c 3
I think instead max you can use iloc too:
df = df.groupby(['uname','sid'])['usage']
.apply(lambda x: x.max()-x.min() if len(x) > 1 else x.iloc[0])
.reset_index()
print (df)
uname sid usage
0 Ahmad a 5
1 Ahmad b 2
2 Mohamad c 3
Another solution with Series.where, which test size:
g = df.groupby(['uname','sid'])['usage']
s = g.max()-g.min()
print (s)
uname sid
Ahmad a 5
b 0
Mohamad c 3
Name: usage, dtype: int64
print (g.size() == 1)
uname sid
Ahmad a False
b True
Mohamad c False
dtype: bool
print (s.where(g.size() != 1, g.max()).reset_index())
uname sid usage
0 Ahmad a 5
1 Ahmad b 2
2 Mohamad c 3