python - delete rows and and assign their value to other rows

user_11886203 0
Viewed 83

In python I have a database with this structure:

python

Group Subgroup Value
A A_1 10
A A_2 15
A A_X 30
B B_1 10
B B_2 15
B B_3 20
B B_X 60
C C_1 1
C C_X 4
C C_2 1
C C_3 1
C C_4 1

The number of subgroups varies depending on the group to which it belongs.

I need to get the following:

python

Group Subgroup Value
A A_1 25
A A_2 30
B B_1 30
B B_2 35
B B_3 40
C C_1 2
C C_2 2
C C_3 2
C C_4 2

Basically I have to remove the subgroups ending with "X" and distribute the value equally to the other subgroups (within the same group).

Do you have any idea how I can do this, please?

pandas dataframe python database
1 Answers

One way to solve:

df = df.sort_values(['Group','Subgroup'])
def transform(x):
    x['Value'] = x['Value'] + x.iloc[-1]['Value'] / (len(x) - 1)
    return x.iloc[:-1]
df = df.groupby('Group').apply(transform).reset_index(drop=True)

Output:

  Group Subgroup  Value
0     A      A_1   25.0
1     A      A_2   30.0
2     B      B_1   30.0
3     B      B_2   35.0
4     B      B_3   40.0
5     C      C_1    2.0
6     C      C_2    2.0
7     C      C_3    2.0
8     C      C_4    2.0
user_154380330
Related