Jun-28-2019, 11:46 PM

Hi there! I am super new to Python, and by super new I mean I downloaded WinPython and installed Pulp this afternoon.

I have a relatively simple (at least I think it's simple) problem that I need to solve. Normally I would use Excel solver, but the size of the data set I'm working with at the moment exceeds Solver's limits so after a bit of Googling I decided to try Python. The issue with that is that while I understand and can recreate the simple scipy and pulp examples listed around the internet, I cannot find a more complex example that would help me setup my specific problem, which is why I'm hoping someone here can help me!

Sample CSV file is available here. Data has been anonymized.

My objective: minimize the total cost of resources by selecting the optimal resource for the bin and task while meeting the constraints

Constraints: the number of resources used within a given month cannot exceed what is available in a given month

Excel explanation:

Choose the best resource cost (one choice per row of columns K through P * column S) for each row while meeting resource availability constraint (columns W and X)

So, if Rank1_Cost Resource was selected for the task in row 2 (E1) the total cost would be 1596.662 (L2*S2) for this task,

and the required resources for resource E would be 0.34 (T2), and because this exceeds resource E's constraint of 0 (X6), the best resource for this row would be A, B, or C; however, all rows would need to be evaluated to determine which is the best choice for this record.

Explanation of columns:

Month - time period

Sched_Resource - current scheduled resource type & variant

Bin - Bin

Current_Resource - current scheduled resource type

Rank1_Resource - lowest cost resource type for bin

Rank2_Resource - 2nd lowest cost resource type for bin

Rank3_Resource - 3rd lowest cost resource type for bin

Rank4_Resource - 4th lowest cost resource type for bin

Rank5_Resource - 5th lowest cost resource type for bin

Rank6_Resource - 6th lowest cost resource type for bin

CurrentCost - Current expected cost

Rank1_Cost - Cost of Rank1 Resource

Rank2_Cost - Cost of Rank2 Resource

Rank3_Cost - Cost of Rank3 Resource

Rank4_Cost - Cost of Rank4 Resource

Rank5_Cost - Cost of Rank 5 Resource

Rank6_Cost - Cost of Rank 6 Resource

Resource_Class - Class of resource (one value in sample file but there are multiple values here)

Ops - number of times resource will be used

Required_Resources - number of resources required to complete the task for the time period

Hopefully all of this is a little bit more clear than mud! Hoping someone can weed through this and help me out! I am very appreciative of any time any of you can lend!

Thanks again!

I have a relatively simple (at least I think it's simple) problem that I need to solve. Normally I would use Excel solver, but the size of the data set I'm working with at the moment exceeds Solver's limits so after a bit of Googling I decided to try Python. The issue with that is that while I understand and can recreate the simple scipy and pulp examples listed around the internet, I cannot find a more complex example that would help me setup my specific problem, which is why I'm hoping someone here can help me!

Sample CSV file is available here. Data has been anonymized.

My objective: minimize the total cost of resources by selecting the optimal resource for the bin and task while meeting the constraints

Constraints: the number of resources used within a given month cannot exceed what is available in a given month

Excel explanation:

Choose the best resource cost (one choice per row of columns K through P * column S) for each row while meeting resource availability constraint (columns W and X)

So, if Rank1_Cost Resource was selected for the task in row 2 (E1) the total cost would be 1596.662 (L2*S2) for this task,

and the required resources for resource E would be 0.34 (T2), and because this exceeds resource E's constraint of 0 (X6), the best resource for this row would be A, B, or C; however, all rows would need to be evaluated to determine which is the best choice for this record.

Explanation of columns:

Month - time period

Sched_Resource - current scheduled resource type & variant

Bin - Bin

Current_Resource - current scheduled resource type

Rank1_Resource - lowest cost resource type for bin

Rank2_Resource - 2nd lowest cost resource type for bin

Rank3_Resource - 3rd lowest cost resource type for bin

Rank4_Resource - 4th lowest cost resource type for bin

Rank5_Resource - 5th lowest cost resource type for bin

Rank6_Resource - 6th lowest cost resource type for bin

CurrentCost - Current expected cost

Rank1_Cost - Cost of Rank1 Resource

Rank2_Cost - Cost of Rank2 Resource

Rank3_Cost - Cost of Rank3 Resource

Rank4_Cost - Cost of Rank4 Resource

Rank5_Cost - Cost of Rank 5 Resource

Rank6_Cost - Cost of Rank 6 Resource

Resource_Class - Class of resource (one value in sample file but there are multiple values here)

Ops - number of times resource will be used

Required_Resources - number of resources required to complete the task for the time period

Hopefully all of this is a little bit more clear than mud! Hoping someone can weed through this and help me out! I am very appreciative of any time any of you can lend!

Thanks again!