Pandas how to do group by properly over certain conditions

user_18272388 0
Viewed 35

I had an issue when trying to group by in pandas, my data is this table until "sum" series, my desired output is some kind of group by that delivers me the results with these series: desired_clientgroup and DesiredGroup_out_sum/avg/max. For example the number "104,23" is the sum over the clientgroup 1 (which I dont know how to generate this group 1 or even its sum).

df_index client_items price qty sum desired_clientgroup DesiredGroup_output_sum
1 1 10,9 2 21,8 1 104,23
2 2 8,5 5 42,5 1
3 3 5,75 3 17,25 1
4 4 2,88 1 2,88 1
5 5 9,9 2 19,8 1
6 1 2,2 4 8,8 2 32,92
7 2 3,55 3 10,65 2
8 3 4,49 3 13,47 2
9 1 8,2 2 16,4 3 44,79
10 2 9,19 2 18,38 3
11 3 6,67 1 6,67 3
12 4 3,34 1 3,34 3
13 1 15,99 3 47,97 4 162,65
14 2 19,9 5 99,5 4
15 3 7,59 2 15,18 4

Some thoughts over this?

pandas group-by python
1 Answers

IIUC, you could use:

# start groups on 1
mask = df['client_items'].eq(1)
df['clientgroup'] = mask.cumsum()

# get the sum per group
# assign result only on first group row
df.loc[mask, 'output_sum'] = (df.groupby('clientgroup')
                              ['sum'].transform('sum')
                              )

Output:

    df_index  client_items  price  qty    sum  clientgroup  output_sum
0          1             1  10.90    2  21.80            1      104.23
1          2             2   8.50    5  42.50            1         NaN
2          3             3   5.75    3  17.25            1         NaN
3          4             4   2.88    1   2.88            1         NaN
4          5             5   9.90    2  19.80            1         NaN
5          6             1   2.20    4   8.80            2       32.92
6          7             2   3.55    3  10.65            2         NaN
7          8             3   4.49    3  13.47            2         NaN
8          9             1   8.20    2  16.40            3       44.79
9         10             2   9.19    2  18.38            3         NaN
10        11             3   6.67    1   6.67            3         NaN
11        12             4   3.34    1   3.34            3         NaN
12        13             1  15.99    3  47.97            4      162.65
13        14             2  19.90    5  99.50            4         NaN
14        15             3   7.59    2  15.18            4         NaN
user_163434640
Related