Pandas Dataframe Logic loop

Question

df0 = df.filter(['ShipmentID', 'AccessorialCode', 'Cost'])

    ShipmentID   AccessorialCode     Cost
0      141687945             LNH    68.65
2      141687945             LNH     7.05
3      141687951             LNH   331.13
5      141687951             LNH    20.97
7      141687951             LNH     7.05
8      141687955             LNH    67.45
9      141687955             LNH    31.75
11     141687955             LNH    47.26
13     141687955             LNH     6.91
14     141687964             LNH   203.13
15     141687964             LNH   229.07
18     141687964             LNH     8.94
19     141687969             LNH  1912.80
22     141687969             LNH    28.29
23     141687972             LNH    65.27
24     141687972             LNH    41.28

My goal is to write a logic loop that adds the costs of duplicate ShipmentID's and then create a new dataframe removing the duplicated accessorials.

df00= df0.loc[df0['AccessorialCode'] == 'LNH']
duplicates = df00[df00.duplicated(['ShipmentID'])]
# I then need to preform an operation to eliminate the duplicates and consolidate the cost

 The desired output should look like this:


    ShipmentID    AccessorialCode  Cost
0   141687945      LNH              75.7
2   141687951      LNH              395.15  
3   141687955      LNH              153.37

Answer 1

data = []
for shipment_id in set(df['ShipmentID'].values):
    row = {
        'ShipmentID':shipment_id,
        'AccessorialCode': 'LNH',
        'Cost': sum(df[df['ShipmentID'] == shipment_id]['Cost'].values),
    }
    data.append(row)

df0 = df(data)