I have a database with more than 300 duplicates that look like this:
JavaScript
x
13
13
1
rate shipment_id original_cost2
8.14 500410339210 5.933
7.81 500410339221 5.934
8.53 500410339232 7.075
8.53 500410339243 14.316
2.76 500410345319 68.877
8
8.46 987506030619 7.369
8.46 987506030620 7.3610
7.32 987506030630 6.8011
27.82 997311250164181 144.4412
7.32 997355250064942 19.8313
I want that for each duplicate shipment_id only original_cost gets added together and rates remain as they are.
JavaScript
1
6
1
rate shipment_id original_cost2
3.06 926401748430 2.543
3.06 926401748430 19.604
16.34 926401748430 2.545
16.34 926401748430 19.606
like for these duplicates: it should look something like this:
JavaScript
1
6
1
rate shipment_id original_cost2
3.06 926401748430 22.143
3.06 926401748430 22.144
16.34 926401748430 22.145
16.34 926401748430 22.146
is there any way to do this?
Advertisement
Answer
Group by the duplicate values (['shipment_id', 'rate']) and use transform on the “original_cost” column to calculate the sum:
JavaScript
1
2
1
df['original_cost'] = df.groupby(['shipment_id', 'rate'])['original_cost'].transform('sum')2
Example input:
JavaScript
1
6
1
rate shipment_id original_cost2
0 3.06 926401748430 22.143
1 3.06 926401748430 22.144
2 16.34 926401748430 22.145
3 16.34 926401748430 22.146
Example output:
JavaScript
1
6
1
rate shipment_id original_cost2
0 3.06 926401748430 22.143
1 3.06 926401748430 22.144
2 16.34 926401748430 22.145
3 16.34 926401748430 22.146