cheese_received = pd.Dataframe(data=[[1, 30, 50], [3, 50, 20]], columns=['Cheese Quality Level', 'Cheese Amount UK', 'Cheese Amount French'])
cheese_sent = pd.Dataframe(data=[[2, 60, 30], [3, 10, 10]], columns=['Cheese Quality Level', 'Cheese Amount UK', 'Cheese Amount French'])
From the dataframes above for each UK and france I want to produce a dataframe that looks like the one below. However I can't create a system which does the following:The outflows and inflows are calculated based on a comparison system that compares from the worst quality to the best quality:
Level 3 sent vs Level 3 received
Level 3 sent vs Level 2 received
Level 3 sent vs Level 1 received
Level 2 sent vs Level 3 received
Level 2 sent vs Level 2 received
Level 2 sent vs Level 1 received
Level 1 sent vs Level 3 received
Level 1 sent vs Level 2 received
Level 1 sent vs Level 1 received
Then how flows are calculated is if quality of cheese is Level 3 vs Level 3 then no outflows or inflows. If the quality of cheese sent is better than the quality received we see an inflow and vice versa for an outflow. E.g. if Level 2 sent vs Level 3 received we see an inflow, and level 2 sent vs level 1 received produces an outflow. The size of the outflow or inflow is the notional multiplied by the multiplier for the cheese quality difference. Anything left over is simply the notional in that direction. (outflow for received, inflow for sent)
Level 1 vs Level 3 : Notional * 0.5
Level 1 vs Level 2: Notional * 0.25
Level 3 vs Level 2: Notional * 0.15
Level x vs Level x (where x=x): 0
So for example for UK cheese the output we want is:
|Lvl Sent|Level Received|Sent |Received|Outflow|Inflow |
|:------:|:------------:|:----:|:------:|:-----:|:-----:|
| 3 | 3 | 10 | 10 | 0 | 0 |
| 2 | 3 | 40 | 40 | 0 |40*0.15|
| 2 | 1 | 20 | 20 |20*0.25| 0 |
| 1 | 1 | 0 | 10 | 10 | 0 |
If anyone can help me implement an algorithm or a step in the right direction I would be very grateful. I am also looking for a function that expands to more than 3 levels?