How to proceed with calculations on dataframe-CodePudding

I dont know how to proceed with a calculation on this database.

Database example:

Indicator   Market      Sales     Costs    Volume
Real        Internal    30512     -16577   12469
Real        External    23        -15      8
Real        Other       65        -38      25
... ... ... ... ... ... ... ... ... ... ... ...
Budget      Internal    0.0        0.0     0.0
Budget      External    3.5       -2.3     60.0
Budget      Other       6.2       -3.9     90.8

First I need to collapse "market" into 1 by adding sales, costs and volume. i.e:

Indicator   Market      Sales     Costs    Volume
Real        Total       30600     -16630   12502
... ... ... ... ... ... ... ... ... ... ... ...
Budget      Total       9.7       -6.2     150.8

Then I need to calculate the "Cost effect" with the following formula:

Cost effect: ((real costs/real volume)-(budget cost/budget volume)) x ((real volume budget volume)/2).

This would be: Cost effect: ((-16630/12502)-(-6.2/150.8))*(12502 150.8)/2 = -8155.2

I've tried all day but without results. Should I use pandas for this?

Any help would be greatly appreciated.

CodePudding user response：

This works

# aggregate Costs and Volumes by Indicator
aggregate = df.groupby('Indicator')[['Costs', 'Volume']].sum()
#              Costs   Volume
# Indicator                  
# Budget        -6.2    150.8
# Real      -16630.0  12502.0
# plug the values into the cost effect formula
cost_effect = (aggregate.loc['Real', 'Costs'] / aggregate.loc['Real', 'Volume'] - aggregate.loc['Budget', 'Costs'] / aggregate.loc['Budget', 'Volume']) * aggregate['Volume'].sum() / 2
# -8155.19213384214
# the latter outcome can be derived a little more concisely by using the difference between the ratios
cost_effect = (aggregate['Costs'] / aggregate['Volume']).diff().iat[-1] * aggregate['Volume'].sum() / 2
# -8155.19213384214