0

I feel like there is an elegant Python solution for this problem that I've been trying to solve with VBA. Can someone please help?

How can I generate a list of numbers that meet the following criteria:

  1. Are all integers
  2. The total is first split between types.
  3. The numbers are further split into subtypes and optimized to meet the required percentages.
  4. The total sum does not exceed the specified Total.

Example of this problem:

  • You plan to build a maximum of 102 residential units.
  • Construction type: 40% of them will be Studios and 60% will be Townhouse construction type. (Could have more or fewer types)
  • There are two Priority lists for the future occupants for the apartments: Assisted and Unassisted
    • The unit allocation percentages in the Assisted(A) list are required to be fully met and the Unassisted(U) list is flexible. We'd like the Unassisted(U) list to be up to 20% of the Total units if possible, but we definitely need a minimum of 80% of the Total units to be Assisted(A) units.
  • The Assisted(A) units are a total of 102x80% = 81.6 units (already not an integer)
    • A minimum of 10% of the Assisted(A) list units must be for elderly individuals.
    • A minimum of 40% of the Assisted(A) list units must be for families.
    • The remaining 50% of the Assisted(A) list units are for aspiring data scientists.

The remaining 20% of the Total units are Unassisted(U) occupancy units.

So:

  • Total: 102 units.
  • Construction_type_categories=[.4, .6] (102 units split into Studios and Townhouses: 40% and 60%)
  • Assisted=[.1,.4,.5] (80%+ of the total units are Assisted, further categorized as elderly, family, other occupancy according to the percentages in the list)
  • Unassisted units are the remaining units that were not part of the 80%+ of the Assisted list. (up to 20% of total units)

Result:

[4,16,12,8,7,25,19,11]

Breakdown:

  • 4 Studio Units Assisted for elderly
  • 16 Studio Units Assisted for families
  • 12 Studio Units for other Assisted occupancy type
  • 8 Studio Units (Unassisted)
  • 7 Townhouse Units Assisted for elderly
  • 25 Townhouse Units Assisted for families
  • 19 Townhouse Units for other Assisted occupancy type
  • 11 Townhouse Units (Unassisted).

I thought of first generating a preliminary array of rounded numbers and then looping through and making adjustments. It looked so tedious I've started considering generating a large matrix of numbers using numpy and filtering by the outlined criteria.

It has been time consuming to generate and optimize the numbers manually so I appreciate any help with a better solution.

import math

def is_even(x):
    if x % 2 == 0:
        return True
    else:
        return False

total=102 
unassisted=.2
unassisted_unit_count= math.floor(total*unassisted)
assisted_unit_count=total- unassisted_unit_count

construction_type_categories=[.4, .6] #Must be even.
assisted_subcategories=[.1,.4,.5] #Last element here will be of least priority.


def Unit_Number_Getter(L):

    if L[1]=='total_constr_type_amounts':
        giventotal= total
    if L[1]=='assisted_constr_type_amounts':
        giventotal= assisted_unit_count

    #Spliting by construction type (preliminary).
    constr_type_amounts=[]
    for ct in construction_type_categories:
        constr_type_amounts.append(round(giventotal * ct))

    #Making the unit counts even for the total construction type amounts (need to).
    for p in constr_type_amounts:
        if not is_even(p):
            add_here=constr_type_amounts.index(p)
            for f in constr_type_amounts:
                if not is_even(f):
                    from_here= constr_type_amounts.index(f)
            constr_type_amounts[add_here] +=1
            constr_type_amounts[from_here] -=1
    assert sum(constr_type_amounts)==giventotal

    print L[1]
    print(constr_type_amounts)
    L[0]=constr_type_amounts


total_constr_type_amounts=0
assisted_constr_type_amounts=0

List_of_lists=[[total_constr_type_amounts,"total_constr_type_amounts"],[assisted_constr_type_amounts,"assisted_constr_type_amounts"]]

#Established the unit counts for each construction type (for Assisted and total units)
for L in List_of_lists:
    Unit_Number_Getter(L)


#Getting a list of the unit counts for each assisted subcategory in each constr type.
testlist=[]
for c in List_of_lists[1][0]:
    already_added=0
    for a in assisted_subcategories[:-1]:
        adding_now= math.ceil(c * a)
        testlist.append(adding_now)
        already_added+=adding_now
    #^Added the priority assisted units (all but the last element).   

    #Now will add the last of the assisted units.
    testlist.append(c-already_added)

    #Now will add he least prioritized unassisted units up to the max.
    Max_unassisted_units= List_of_lists[0][0][List_of_lists[1][0].index(c)]-c

    testlist.append(Max_unassisted_units)

    assert ((c+Max_unassisted_units)== List_of_lists[0][0][List_of_lists[1][0].index(c)])#all units present

print("Result: "+ "\n" + str(testlist))
Kdog
  • 503
  • 5
  • 20
  • 1
    You may get more answers if you provide [a **minimal** example](https://stackoverflow.com/help/mcve). – Alex Sep 26 '18 at 00:29
  • I think in general if your requirement are spaghetti like, your code will be spaghetti like too. Just roll your sleeves up and code 1 requirement after the other... (Also I doubt the `pandas` tag is relevant here...) – Julien Sep 26 '18 at 00:31
  • Welcome to StackOverflow. Please read and follow the posting guidelines in the help documentation, as suggested when you created this account. [On topic](http://stackoverflow.com/help/on-topic), [how to ask](http://stackoverflow.com/help/how-to-ask), and [... the perfect question](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) apply here. StackOverflow is not a design, coding, research, or tutorial resource. However, if you follow whatever resources you find on line, make an honest coding attempt, and run into a problem, you'd have a good example to post. – Prune Sep 26 '18 at 01:33
  • 3
    You're not yet at the coding stage: you're still at algorithmic design. Look carefully at the data flow for each stage, especially the *minimum* requirements: these are quantities you *must* round up. |||| Therefore, you have to take this in stages: (1) `ceil(102 * 0.8)` is how many **assisted** units you need; the others are unassisted. You already calculated this as 82. (2) You need `ceil(82 * 0.1)` elderly and `ceil(82 * 0.4)` family units; the rest of the 82 are for data scientists. (3) When you get each of those categories done, you can round off the 40-60 split. – Prune Sep 26 '18 at 01:41
  • 2
    See how this is working? Your job as a system designer and programmer is to properly stage these computations, parameterize the items that can change with different specifications, and to generalize repetitive steps into iteration loops. Handing you code is simply too broad for Stack Overflow; this warrants a tutorial in problem analysis and execution. – Prune Sep 26 '18 at 01:43
  • 1
    The term for this is **constrained random number generation**. In your case you have multiple constraints, and you're still designing the constraints. So, you haven't yet refined this to a coding question, and you haven't given an MCVE. – smci Sep 26 '18 at 01:47
  • 1
    Just because the final output must all be integers, doesn't mean all your intermediate representations must be integers. You could let them be whatever floats they turn out to be, then at the end calculate their lowest common denominator, and multiply all coeffts by the LCD, and convert to int. – smci Sep 26 '18 at 01:49
  • 1
    Once you can reduce your criteria to a set of linear equations/inequalities, there are **constrained random solvers** that you can use. – smci Sep 26 '18 at 01:52
  • 1
    For example: *We'd like the Unassisted(U) list to be up to 20% of the Total units if possible, but we definitely need a minimum of 80% of the Total units to be Assisted(A) units.* => `A = math.ceil(0.8 * Total) ; U = Total - A` – smci Sep 26 '18 at 02:02
  • 1
    Please look at the [existing questions on Python constraint solvers](https://stackoverflow.com/search?q=%5Bpython%5D+constraint+solver) and figure out which one is suitable to your case. People seem to use [tag:or-tools], [tag:PuLP], also [tag:scip]... All your constraints are linear, so you don't care about nonlinear. – smci Sep 26 '18 at 03:13
  • I really appreciate you guys taking the time to think about what I'm trying solve. I'm going to carefully go through your suggestions and see how I can make a methodical process to get to the answer. I feel like I'm going to once more end up in a place where I'll get a preliminary array and have to move units around to the underserved places from overserved places according to the results of several contradictory tests. This is where I was with VBA. I still wonder if it's better to solve it with brute force by generating arrays and filtering. Really appreciate your guidance! – Kdog Sep 26 '18 at 03:38
  • @Kdog: since you have a linear set of equations/inequalities, just start writing the equations, and assign variable names, where needed. Also **you** need to specify how rounding happens, when it happen, e.g. you can't perfectly divide 102 Total units into 40% Studios and 60% Townhouses, that would give 40.8, 61.2. So do you round-to-nearest? round-up in favor of Studios? – smci Sep 26 '18 at 03:55
  • I was wrong about the 'random' part, there's nothing random about this. It's simply solving linear systems of equations or inequalities ('linear programming'). – smci Sep 26 '18 at 04:05
  • @smci, Would you please give an example of the equations/inequalities that would be used as part of a lineal programming solution? I've researched it and found an existing package to help with that, but I don't yet understand how I can adapt the package to my problem (expressing equations, constraints). I want to understand that though, I realize that Linear programming could be a very powerful tool in my belt. I've tried to put together something using the algorithmic approach. Any comments are welcome. Looks like it can get messy with added tests/criteria. – Kdog Sep 26 '18 at 17:08
  • @Prune I've added an attempt at an MCVE above. What do you think? Thank you for the feedback. – Kdog Sep 26 '18 at 17:11
  • @Kdog: This is not yet an MCVE; it's a full program with no problem specification. What's wrong with your code? This is certainly not the minimal code that exhibits your trouble. Stack Overflow is for specific coding problems, not general tutorial assistance or evaluation of full applications. – Prune Sep 26 '18 at 17:16
  • 1
    From your comment to scsi, it appears that you need to work through a tutorial on linear programming. – Prune Sep 26 '18 at 17:16
  • Yes please work through a tutorial on linear programming. You need to understand what linear programming is before trying to code it or posting code questions using it. I already gave you an example of an inequality above: *"We'd like the Unassisted(U) list to be up to 20% of the Total units if possible, but we definitely need a minimum of 80% of the Total units to be Assisted(A) units."* => `A = math.ceil(0.8 * Total) ; U = Total - A` . Do you understand why those two are equivalent? why we used `>=` or `math.ceil()` in favor of Assisted units? – smci Sep 26 '18 at 20:43
  • 1
    Yes, I got the hint and used the equivalent: .floor() to get the unassisted units in my code. Thank you for putting LP on my radar. I'll definitely be using it to solve problems in the future. I think I'll settle on PULP to model the problems in Python. It looks like it's simple enough to define the function and constraints. Also found OpenSolver to be a good option if sticking to Excel/VBA. I thought it was interesting that you recognized that this is a problem that can be solved with linear equations. Will definitely read more about it. Thank you – Kdog Sep 27 '18 at 15:27

0 Answers0