How to efficiently calculate the share of an aggregated column

I have the following DataFrame and want to calulate the "share".

    import pandas as pd

    d = {"col1":["A", "A", "A", "B", "B", "B"], "col2":["start_amount", "mid_amount", "end_amount", "start_amount", "mid_amount", "end_amount"], "amount":[0, 2, 8, 1, 2, 3]}
    df_test = pd.DataFrame(d)
    
    df_test["share"] = 0
    for i in range(len(df_test)):
        df_test.loc[i, "share"] = df_test.loc[i, "amount"] / df_test.loc[(df_test["col1"] == df_test.loc[i, "col1"]) & (df_test["col2"] == "end_amount"), "amount"].values

This works but is far from efficient. Is there a better way to do my calculation?

You can use groupby and transform to get the end amount for each value in 'col1':

df_test["share"] = df_test["amount"] / df_test.groupby("col1")["amount"].transform("last")

  col1          col2  amount     share
0    A  start_amount       0  0.000000
1    A    mid_amount       2  0.250000
2    A    end_amount       8  1.000000
3    B  start_amount       1  0.333333
4    B    mid_amount       2  0.666667
5    B    end_amount       3  1.000000

This is equivalent to selecting the rows with "end_amount", then performing a map per "col1" to then divide "amount":

s = df_test.loc[df_test['col2'].eq('end_amount')].set_index('col1')['amount']
df_test['share'] = df_test['amount']/df_test['col1'].map(s)

Output:

  col1          col2  amount     share
0    A  start_amount       0  0.000000
1    A    mid_amount       2  0.250000
2    A    end_amount       8  1.000000
3    B  start_amount       1  0.333333
4    B    mid_amount       2  0.666667
5    B    end_amount       3  1.000000

If you want the share, you need to compute the total sum of each group, not the maximum. But it depends on your need.

Assuming that the share is related to the total sum of each group, put the total amount for the group in a new column and compute the division.

If you want the maximum for each group, change .transform("sum") to .transform("max")

d = {"col1": ["A", "A", "A", "B", "B", "B"], "amount": [0, 2, 8, 1, 2, 3]}
df_test = pd.DataFrame(d)

df_test['total_amount'] = df_test.groupby('col1')['amount'].transform('sum')

df_test['share'] = df_test['amount'] / df_test['total_amount']

print(df_test)

I would probably use a groupby to find the max amount for each group, merge the max amount onto the original data by the group string, then divide to get share.

Assuming the max value of each group is the ending_amount:

df_test = pd.DataFrame(d)

df_test = df_test.merge(
    df_test.groupby('col1')['amount'].max().rename('max_amount'), #Take the max for each group, and rename the resulting series
    left_on=['col1'],right_index=True,how='left' #Merge Logic
)

df_test['share'] = df_test['amount']/df_test['max_amount']

Alternatively, slicing for the end_amount, merging that back onto the original data with a renamed value column, then dividing to obtain share:

df_test = pd.DataFrame(d)

df_test = df_test.merge(
    df_test[df_test['col2']=='end_amount'][['col1','amount']].rename({'amount':'max_amount'},axis=1), #Take the end_amount for each 'col1', and rename the resulting column
    left_on=['col1'],right_on=['col1'],how='left' #Merge Logic
)

df_test['share'] = df_test['amount']/df_test['max_amount']

The result of both techniques:

  col1          col2  amount  max_amount     share
0    A  start_amount       0           8  0.000000
1    A    mid_amount       2           8  0.250000
2    A    end_amount       8           8  1.000000
3    B  start_amount       1           3  0.333333
4    B    mid_amount       2           3  0.666667
5    B    end_amount       3           3  1.000000

Edit: I do think Skulden's answer is the best one, albeit he uses sum in his example. With the max assumption:

df_test = pd.DataFrame(d)
df_test['max_amount']=df_test.groupby('col1')['amount'].transform('max')
df_test['share'] = df_test['amount']/df_test['max_amount']

import pandas as pd
import numpy as np

# Create the DataFrame
d = {
    "col1": ["A", "A", "A", "B", "B", "B"], 
    "col2": ["start_amount", "mid_amount", "end_amount", "start_amount", "mid_amount", "end_amount"], 
    "amount": [0, 2, 8, 1, 2, 3]
}
df = pd.DataFrame(d)

# Calculate the end_amount for each group
end_amounts = df[df['col2'] == 'end_amount'].set_index('col1')['amount']
'''
col1
A    8
B    3
Name: amount, dtype: int64
'''
# Map end_amounts to the original DataFrame
df['end_amounts'] = df['col1'].map(end_amounts)

# Calculate shares
df['share'] = df['amount'] / df['end_amounts']

print(df)

'''
  col1          col2  amount  end_amounts     share
0    A  start_amount       0            8  0.000000
1    A    mid_amount       2            8  0.250000
2    A    end_amount       8            8  1.000000
3    B  start_amount       1            3  0.333333
4    B    mid_amount       2            3  0.666667
5    B    end_amount       3            3  1.000000

'''

Method 2 : Numpy solution

import pandas as pd
import numpy as np

d = {
    "col1": ["A", "A", "A", "B", "B", "B"], 
    "col2": ["start_amount", "mid_amount", "end_amount", "start_amount", "mid_amount", "end_amount"], 
    "amount": [0, 2, 8, 1, 2, 3]
}
df = pd.DataFrame(d)

# Convert to NumPy arrays for efficiency
col1 = df['col1'].values
col2 = df['col2'].values
amount = df['amount'].values

# Find unique groups and their end_amounts
unique_groups, end_amounts_idx = np.unique(col1, return_index=True)

end_amounts_dict = { 
    ug: amount[(col1 == ug) & (col2 == "end_amount")][0]
    for ug in unique_groups
}
print(end_amounts_dict)#{'A': 8, 'B': 3}

# Convert the dictionary to a NumPy array for efficient indexing
end_amounts = np.array([end_amounts_dict[element] for element in col1])
print(end_amounts)#[8 8 8 3 3 3]

# Calculate shares using vectorized operations
shares = amount / end_amounts

# Add the computed shares back to the DataFrame
df['share'] = shares

print(df)
'''
  col1          col2  amount     share
0    A  start_amount       0  0.000000
1    A    mid_amount       2  0.250000
2    A    end_amount       8  1.000000
3    B  start_amount       1  0.333333
4    B    mid_amount       2  0.666667
5    B    end_amount       3  1.000000
'''