Assigning weight to a column using after pandas groupby

March 17, 2023

I would Like to add weight to a groupby dataframe:

for example I have:

df = pd.DataFrame({'Account': [1, 2, 3, 1, 2, 3, 3], 'Money': [4, 5, 6, 8, 9, 10, 11]})
df
   Account  Money
0        1      4
1        2      5
2        3      6
3        1      8
4        2      9
5        3     10
6        3     11

df.groupby(['Account']).apply(lambda x: x.head(2)).reset_index(drop=True)
   Account  Money
0        1      4
1        1      8
2        2      5
3        2      9
4        3      6
5        3     10

What i want to do is: adding a weights here with half value of the last row
Like this:

   Account  Money  weights
0        1      4     1
1        1      8     0.5
2        2      5     1
3        2      9     0.5
4        3      6     1
5        3     10     0.5
6        3     12     0.25

(I manually add this row for demostration)

>Solution :

If only order is important and need divide 1 by array 1,2,4,8,... by size of groups use GroupBy.cumcount with Series.rpow and divide from ride side by 1 in Series.rdiv:

df = df.sort_values('Account', ignore_index=True)

df['weights'] = df.groupby(['Account'])['Money'].cumcount().rpow(2).rdiv(1)
print (df)
   Account  Money  weights
0        1      4     1.00
1        1      8     0.50
2        2      5     1.00
3        2      9     0.50
4        3      6     1.00
5        3     10     0.50
6        3     11     0.25

If need divide by first values of Money per groups:

df = df.sort_values('Account', ignore_index=True)

df['weights'] = df.groupby(['Account'])['Money'].transform('first').div(df['Money'])
print (df)
   Account  Money   weights
0        1      4  1.000000
1        1      8  0.500000
2        2      5  1.000000
3        2      9  0.555556
4        3      6  1.000000
5        3     10  0.600000
6        3     12  0.500000