Problem:In recent requirements development, there is such a groupProportion calculation and summation problem, group according to the field 'CPN', calculate the proportion of each PO Line Actual CT2R * line quantity, name it 'Weighted(QTY)CT2R', and then classify each line 'Weighted' according to the same 'CPN'(QTY)CT2R' value is summarized and summed to get the total 'Weighted(QTY)CT2R' value. The cell filled with yellow as shown in the figure below is the target value we need
The specific calculation logic is as follows:
Use Pandas code to achieve the above requirements as follows:
import pandas as pddf = pd.DataFrame([['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',10,90],['01-0989',200,50],['02-0437',20,80],['02-0437',20,80],['02-0437',20,80]],columns = ['cpn','po_line_qty','actual_ct2r'])# Group according to the field 'cpn', sum the values in the field 'po_line_qty' and name it totaltotal = df.groupby('cpn').agg({'po_line_qty':sum}).reset_index()# Rename field 'po_line_qty' to 'total_po_line_qty'total = total.rename(columns = {'po_line_qty':'total_po_line_qty'})# The df table and the total table are left-joined according to the field 'cpn', named new_resnew_res = pd.merge(df,total,how='left',on='cpn')def weighted_qty_ct2r(row):scale = row['po_line_qty'] / row['total_po_line_qty']weighted_qty_ct2r = scale * row['actual_ct2r']return weighted_qty_ct2r# Generate field 'weighted_qty_ct2r'new_res['weighted_qty_ct2r'] = new_res.apply(lambda row:weighted_qty_ct2r(row), axis=1)# Group according to the field 'cpn', sum the values in the field 'weighted_qty_ct2r' and name it df_resultdf_result = new_res.groupby('cpn').agg({'weighted_qty_ct2r':sum})df
total
new_res
df_result
