Source_No Parent Type Amt
123 123 Tail 100
456 123 Coll 100
789 123 Coll 100
I want the code to create a new column a new column 'Tot_Amt' which will add the Amt of all Source_No, where Parent = Source_No and for other
Expected output.
Source No Parent Type Amt Tot_Amt
123 123 Parent 100 200
456 123 Child 100 0
789 123 Child 100 0
Here you go:
df["Tot_Amt"] = (df.groupby(["Parent"])["Amt"].sum() - df.groupby(["Source_No"])["Amt"].sum()).fillna(0).reset_index(drop=True)
One way to do this is groupby, aggregate, and then merge back based on your Parent = Source_No constraint
# group by parent all the non-parent rows (SourceNo != Parent)
grouped = df.loc[df.SourceNo != df.Parent].groupby(df.Parent)
# sum the Amt for each group
summed = grouped.Amt.agg(Tot_Amt='sum')
# merge with the original frame on SouceNo == Parent
df = df.merge(summed, left_on='SourceNo', right_on='Parent', how='left')
df.Tot_Amt.fillna(0, inplace=True)
Will give you this result:
SourceNo Parent Type Amt Tot_Amt
0 123 123 Parent 100 200.0
1 456 123 Child 100 0.0
2 789 123 Child 100 0.0
Source_Noa unique value for each row? Can two rows have the sameSource_No?