0

Have got dataframe at store-product level as shown in sample below:

Store   Product     Space   Min     Max     Total_table     Carton_Size
11      Apple       0.25    0.0625  0.75            2           6
11      Orange      0.5     0.125   0.5             2           null
11      Tomato      0.75    0.0625  0.75            2           6
11      Potato      0.375   0.0625  0.75            2           6
11      Melon       0.125   0.0625  0.5             2           null

Scenario: All product here have space in terms of 1/8th. But if a product have carton_size other than null, then that particular product space has to be converted in terms of 1/(carton_size)th considering the Min(Space shouldn't be lesser than Min) and Max(Space shouldn't be greater than Max) values. Can get space from non-carton products but at the end, sum of 'Space' column should be equivalent/lesser than 'Total_table' value. Also, these 1/8th and 1/6th values are in relation to the 'Total_table', this total_table value is splitted as Space for each product.

Example: In above given dataframe, Three products have carton size, so we can take 1/8th space from the non-carton product selecting from top and split it as 1/24(means 1/24 + 1/24 + 1/24 = 1/8), which can be added to three carton products to make it 1/6, which forms the expected output shown below considering Min and Max values. If any of the product doesn't satisfy Min or Max condition - leave that product(eg., Tomato).

Roughly Expected Output:

Store   Product     Space   Min     Max     Total_table     Carton_Size  
11      Apple       0.292   0.0625  0.75            2           6
11      Orange      0.375   0.125   0.5             2           null
11      Tomato      0.75    0.0625  0.75            2           6
11      Potato      0.417   0.0625  0.75            2           6
11      Melon       0.125   0.0625  0.5             2           null

Need solution in Python. Thanks in Advance!

user12345
  • 499
  • 1
  • 5
  • 21
  • 1
    Can you please provide the dataframe in a form that can be read into python directly, and also provide some code that you have tried so far? – qdread Mar 31 '21 at 14:26
  • I think you are describing an optimization problem, but I did not understand the part about the carton being yes or no. – xicocaio Mar 31 '21 at 14:45
  • @xicocaio yes it's an optimization problem, each product has be allocated some space in table(which is 'Space' column). But Carton 'Yes' products has to be kept as such with carton in table. Here the carton size is 6, so have to convert older 1/8th space of carton product to 1/6. – user12345 Apr 02 '21 at 06:37
  • Are these values in the `Space` column the old ones (1/8th), or the ones you want to achieve? Can you give an example of what would be the values for an appropriate solution? Also, these **1/8th** and **1/6th** values that you mention are in relation to what, the `total_table` value such as in `1/6 * total_table`? You mention that the carton size is 6, and it is not clear how it relates to the other restrictions. Please clarify your question by following this [guide on writing good pandas questions](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – xicocaio Apr 02 '21 at 12:57
  • @xicocaio have edited the question as per your requirement. Looking forward for your help in this. Thank You! – user12345 Apr 05 '21 at 10:36
  • I might have a solution, but using a linear programming python package called `pulp` is that ok or you? Or do you need a solution using some kind of heuristic? – xicocaio Apr 05 '21 at 13:53
  • No need of pulp for now, second option would be better @xicocaio – user12345 Apr 05 '21 at 14:00
  • 1
    As your problem is a variation of the classical [knapsack problem](https://en.wikipedia.org/wiki/Knapsack_problem#:~:text=The%20knapsack%20problem%20is%20a,is%20as%20large%20as%20possible.), I suggest you try one of many implementations of [simple heuristic solutions](https://www.geeksforgeeks.org/python-program-for-dynamic-programming-set-10-0-1-knapsack-problem/). But I would advise you to use a linear programmin approach, given that for small problems it can give exact solutions. If you want the linear programming approach using `pulp` I can try to help you. – xicocaio Apr 05 '21 at 14:08
  • Thanks for your suggestion @xicocaio. Let us try pulp then! – user12345 Apr 05 '21 at 14:19
  • Also FYI @xicocaio, in above provided sample dataframe all 'carton_size' is given as 6, but it can also be different for some product, not always same for all product. Consider this also in your solution. Eg., it can be 4 for Potato. So if in case in future this value changes, your solution need to take over it. – user12345 Apr 05 '21 at 14:24
  • Ok, I am at work now, and as soon as i get to my personal computer I will place a solution. – xicocaio Apr 05 '21 at 14:44
  • Many Thanks for your timely help! Looking forward. – user12345 Apr 05 '21 at 14:49
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/230778/discussion-between-xicocaio-and-user12345). – xicocaio Apr 05 '21 at 22:20

0 Answers0