How do I create a group of lists from imported data from access?

Question

I have read several posts and nothing is helping me with this. I found info about every possible combination, but what I found made combinations of all the values in each list and not the list itself. (Here is a link to my previous question about importing access files for more examples of what I am doing: How do I import an .accdb file into Python and use the data?).

Lets say I have an imported list from Access.

CreatureID  Name      Atk     Def     HP      Type       BattlePower
----------  --------  ------  ------  -----   ---------  -----------
         1  Snake     1000    500     200     Reptile    20
         2  Mouse     500     200     100     Mammal     20
         3  Turtle    200     2000    2000    Amphibian  40
         4  Cat       1500    700     400     Mammal     20
         5  Dog       1700    800     600     Mammal     40
         6  Bird      100     100     200     Bird       20
         7  Badger    2000    1500    1000    Mammal     40
         8  Squirrel  300     200     200     Mammal     20

I have been trying to figure out how to store each creatures info as a group of lists using the imported access data.

    creatureList = [ [1, 'Snake', 1000, 500], [2, 'Mouse', 500, 200],
                 [3, 'Turtle', 200, 2000], [4, 'Cat', 1500, 700],
                 [5, 'Dog', 1700, 800], [6, 'Bird', 100, 100],
                 [7, 'Badger', 2000, 1500] ]

After I have the list the tricky part is I need to create groups of 5 of all the possible creature combinations. (Previous step may not be needed to complete this but was not sure).

group1List = [ [1, 'Snake', 1000, 500], [2, 'Mouse', 500, 200],
               [3, 'Turtle', 200, 2000], [4, 'Cat', 1500, 700],
               [5, 'Dog', 1700, 800] ]
group2List = [ [1, 'Snake', 1000, 500], [2, 'Mouse', 500, 200],
               [3, 'Turtle', 200, 2000], [4, 'Cat', 1500, 700],
               [6, 'Bird', 100, 100] ]
group3List = [ [1, 'Snake', 1000, 500], [2, 'Mouse', 500, 200],
               [3, 'Turtle', 200, 2000], [4, 'Cat', 1500, 700],
               [7, 'Badger', 2000, 1500]]
group4List = [ [2, 'Mouse', 500, 200], [3, 'Turtle', 200, 2000],
               [4, 'Cat', 1500, 700], [5, 'Dog', 1700, 800],
               [6, 'Bird', 100, 100] ]
group5List = [ [2, 'Mouse', 500, 200], [3, 'Turtle', 200, 2000],
               [4, 'Cat', 1500, 700], [5, 'Dog', 1700, 800],
               [7, 'Badger', 2000, 1500] ]

The next couple steps I want to be able to add up the total atk power and def power of each group and be able to compare them against each other to find the group with the highest total stats.

group1total = [4900, 4200]
group2total = [3300, 3500]
group3total = [5200, 4900]

Edit

Was trying to make it harder than it needed to be. This is the working code.

LIST = []
import pypyodbc
import itertools
import operator
pypyodbc.lowercase = False
conn = pypyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\Users\Ju\Desktop\Dark Summoner.accdb;")
cur = conn.cursor()
cur.execute("SELECT Number, ID, Name, Atk, Def, HP, BP, Species, Special FROM Impulse_AA");
while True:
    row = cur.fetchone()
    if row is None:
        break
    listadd = [row]
    LIST = LIST+ listadd
GROUPS = max(((sum(map(lambda x:x[4]+x[5]+x[6],c)),c) for c in
    itertools.combinations(LIST,5)),key=operator.itemgetter(0))[1]

I am trying to store the list of possible teams for other calculations later. Not sure if storing the info is the best way to go or if re-calculating every time is better.

But as of right now the program runs and never finishes (or at least not after 5-10 min). There are 189 creatures in my current database and that is 1,905,305,787 combinations.

Edit

The reason for making all possible groups is there are additional factors to take into account. I have updated the creatureList to reflect a couple of the factors to give an example.

BattlePower bonus = 10% atk and 10% def, all creatures have the same battle power 
Type        bonus = 15% atk and 15% def, all creatures have the same type

There are 32 different combo bonuses to check after teams are created. I figured it would be less taxing to create the list then check it for the group bonuses than to check a group and add bonuses as it goes and compare each set to see which is best. Also once the list is created and has stored all max stats then I will try to export it as a Microsoft Access document so that I only have to run the calculations with new creatures as they come out.

Answer 1

As a one-liner:

>>> max(((sum(map(lambda x:x[2]+x[3],c)),c) for c in
        itertools.combinations(creatureList,5)),key=operator.itemgetter(0))[1]
([1, 'Snake', 1000, 500],
 [3, 'Turtle', 200, 2000],
 [4, 'Cat', 1500, 700],
 [5, 'Dog', 1700, 800],
 [7, 'Badger', 2000, 1500])

itertools.combinations(creatureList,5) returns an iterator to all the 5-element combinations of creatures.

(sum(map(lambda x:x[2]+x[3],c)),c) is a tuple of the sum of attack/defence values of a given 5-element combination and the combination itself.

max(...,key=...) returns the tuple with the maximum attack/defence summed value.

max(...)[1] extracts the corresponding combination.

Update

Based on the edit to the question and the number of creatures (189), there is a better way to state the problem which leads to a simpler solution. You are effectively summing attack and defence values for every creature and then looking for the largest sum of these values for a combination of 5 creatures. The largest sum will correspond to the 5 creatures with the highest individual attack/defence sum (since a sum is associative and commutative).

So, you can obtain the result you are looking by creating a list of creatures sorted by attack/defence sum and then pick the 5 creatures at the end of the list (which is considerably faster than trying every possible combination):

>>> [x for x in sorted(creatureList,key=lambda x: x[2]+x[3])][-5:]
[[1, 'Snake', 1000, 500],
 [3, 'Turtle', 200, 2000],
 [4, 'Cat', 1500, 700],
 [5, 'Dog', 1700, 800],
 [7, 'Badger', 2000, 1500]]

The problem with this solution is that it does not take into account creatures whose attack/defence sums are equal. It will only give a single solution when there are possibly multiple solutions. The following code is one way to deal with that:

result = list()  # the desired list of 5 creature groups
base   = list()  # the prefix of all 5 creature groups
length = 5       # the number of creatures remaining to create a 5 creature list
for group in reversed([[x for x in g] 
              for k,g in itertools.groupby(
                  sorted(map(lambda x: x + [x[2]+x[3]],creatureList),
                         key=operator.itemgetter(4)),
                  key=operator.itemgetter(4))]):
    grouplen = len(group)
    if grouplen <= length:
        base.extend(group)
        length -= grouplen
    elif length:
        for c in itertools.combinations(group,length):
            result.append(base + list(c))
        break

Given the creatures:

[ [1, 'Snake', 1000, 500],
  [2, 'Mouse', 500, 200],
  [3, 'Turtle', 200, 2000],
  [4, 'Cat', 1500, 700],
  [5, 'Dog', 1700, 800],
  [6, 'Bird', 100, 100],
  [7, 'Badger', 2000, 1500],
  [8, 'XXX', 500, 1000] ]

The result is:

 [ [ [7, 'Badger', 2000, 1500, 3500],
     [5, 'Dog', 1700, 800, 2500],
     [3, 'Turtle', 200, 2000, 2200],
     [4, 'Cat', 1500, 700, 2200],
     [1, 'Snake', 1000, 500, 1500] ],
   [ [7, 'Badger', 2000, 1500, 3500],
     [5, 'Dog', 1700, 800, 2500],
     [3, 'Turtle', 200, 2000, 2200],
     [4, 'Cat', 1500, 700, 2200],
     [8, 'XXX', 500, 1000, 1500] ] ]

The additional field is the attack/defence sum. It is easy to get rid of, if need be. But that's an exercise I'll leave for you.

Answer 2

This is called Combinations, and Python has standard function for that: itertools.combinations.

Keep in mind that number of combinations is n!/(k!*(n-k!)) - so it grows fast. For 100 creatures, it'll be 75 millions, and for 1000 creatures, over 8 trillions. Use iterators, don't try to make it into a list in memory.