0

Trying my best to explain what I am trying to achieve here. I have 2 series (Annual Volume & Annual Capacity) which I need to compare to allocate a value in the column Allocation & then sum it cumulatively inside a group (L1 / L2 in Lane Column) until the Cum Allocation column matches **Annual Volume **Column.

The table looks like this:

Lane Carrier SCAC Annual Volume Annual Capacity Allocation Cum Allocation
25972 L1 BGME 4917.0 2408.0 2408 2408
25973 L1 SCNN 4917.0 3380.0 2459 4867
25974 L1 XPOL 4917.0 4940.0 50 4940
25975 L1 SJRG 4917.0 156.0
25976 L1 MTRK 4917.0 4940.0
26055 L2 JNJR 5604.0 260.0 260 260
26056 L2 WOSQ 5604.0 1300.0 1300 1560
26057 L2 BGME 5604.0 2704.0 2704 4264
26058 L2 ITSB 5604.0 2080.0 1340 5604
26059 L2 UCSB 5604.0 4368.0

The rule of allocation is as such:

Allocation = min(Annual Volume * 0.5, Annual Capacity) as long as Cum Allocation < Annual Volume
Allocation = (Annual Volume - Cum Allocation.shift(1)) so that Cum Allocation == Annual Volume
Allocation = 0 when Cum Allocation > Annual Volume

Cum Allocation = Cum Allocation.shift(1) + Allocation (initial Cum Allocation = Allocation)

I could do it through 2 functions - not quite efficient and the database is very large making it extremely slow & I need to keep changing allocation factor (0.5 above), making it quite painful.

I just have a feeling this can be handled through pandas standard functions itself. Any ideas would be helpful.

Improve this question
4
  • Please provide a sample of your expected output. Commented Jan 7, 2023 at 8:34
  • Expected output are the last 2 columns - Allocation & Cum Allocation. Input are 3rd & 4th columns Annual Volume & Annual Capacity Commented Jan 7, 2023 at 14:27
  • Two questions: (1) on the second row, why is the Cum Allocation > Annual Capacity? Should Allocation = 972 and Cum Allocation = 3380.0? (2) for the scond to last row, why is Cum Allocation = 5604? How is that number calculated? Commented Jan 7, 2023 at 23:45
  • Annual capacity is that of the carrier & Annual Volume is of the lane. We are allocating carrier's capacity to each lane so that the Cumulative Allocation can't exceed Annual Volume. Individual Allocation can't be more than 50% of lane's Annual Volume to a carrier i.e. min(50%*Annual Volume, Annual Capacity). Let me know if I could explain. Commented Jan 8, 2023 at 4:49
Related questions
0
Trouble installing simulation package in Google Colab
1
How to install coco dataset in colab?
111
How do I install a library permanently in Colab?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.