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)
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