Get combinations that match a criteria

Viewed 242

Problem: I have a table where I need to extract all valid combinations of rows (or columns if I transpose the table). the columns only have values "+" or "-" and a combination is considered valid when in at least one of the lines of the combination has "+", that is, any combination with "-" in all the lines is not valid.

Example table:

   Guns  P_01 P_02 P_03 P_04 P_05 P_06 P_07
0  G_01    +    -    +    +    +    -    +
1  G_02    +    +    +    -    +    +    -
2  G_03    -    -    -    +    +    +    +
3  G_04    +    +    +    -    -    -    -
4  G_05    +    +    +    -    -    -    -
5  G_06    -    -    -    +    +    +    +
6  G_07    +    +    +    -    -    -    -

Example valid combination:

0  G_01    +    -    +    +    +    -    +
1  G_02    +    +    +    -    +    +    -

Example invalid combination:

3  G_04    +    +    +    -    -    -    -
4  G_05    +    +    +    -    -    -    -

To get all the combinations, I try to use the itertools combination, and put the result into a list:

dfcomb =  [] 
dfcomb = df.apply(lambda r: list(combinations(r, 2)), axis=0)

Output:

         Guns     P_01    P_02    P_03    P_04    P_05    P_06    P_07
0   (G_01, G_02)  (+, +)  (-, +)  (+, +)  (+, -)  (+, +)  (-, +)  (+, -)
1   (G_01, G_03)  (+, -)  (-, -)  (+, -)  (+, +)  (+, +)  (-, +)  (+, +)
2   (G_01, G_04)  (+, +)  (-, +)  (+, +)  (+, -)  (+, -)  (-, -)  (+, -)
3   (G_01, G_05)  (+, +)  (-, +)  (+, +)  (+, -)  (+, -)  (-, -)  (+, -)
4   (G_01, G_06)  (+, -)  (-, -)  (+, -)  (+, +)  (+, +)  (-, +)  (+, +)
5   (G_01, G_07)  (+, +)  (-, +)  (+, +)  (+, -)  (+, -)  (-, -)  (+, -)
6   (G_02, G_03)  (+, -)  (+, -)  (+, -)  (-, +)  (+, +)  (+, +)  (-, +)
7   (G_02, G_04)  (+, +)  (+, +)  (+, +)  (-, -)  (+, -)  (+, -)  (-, -)
8   (G_02, G_05)  (+, +)  (+, +)  (+, +)  (-, -)  (+, -)  (+, -)  (-, -)
9   (G_02, G_06)  (+, -)  (+, -)  (+, -)  (-, +)  (+, +)  (+, +)  (-, +)
10  (G_02, G_07)  (+, +)  (+, +)  (+, +)  (-, -)  (+, -)  (+, -)  (-, -)
11  (G_03, G_04)  (-, +)  (-, +)  (-, +)  (+, -)  (+, -)  (+, -)  (+, -)
12  (G_03, G_05)  (-, +)  (-, +)  (-, +)  (+, -)  (+, -)  (+, -)  (+, -)
13  (G_03, G_06)  (-, -)  (-, -)  (-, -)  (+, +)  (+, +)  (+, +)  (+, +)
14  (G_03, G_07)  (-, +)  (-, +)  (-, +)  (+, -)  (+, -)  (+, -)  (+, -)
15  (G_04, G_05)  (+, +)  (+, +)  (+, +)  (-, -)  (-, -)  (-, -)  (-, -)
16  (G_04, G_06)  (+, -)  (+, -)  (+, -)  (-, +)  (-, +)  (-, +)  (-, +)
17  (G_04, G_07)  (+, +)  (+, +)  (+, +)  (-, -)  (-, -)  (-, -)  (-, -)
18  (G_05, G_06)  (+, -)  (+, -)  (+, -)  (-, +)  (-, +)  (-, +)  (-, +)
19  (G_05, G_07)  (+, +)  (+, +)  (+, +)  (-, -)  (-, -)  (-, -)  (-, -)
20  (G_06, G_07)  (-, +)  (-, +)  (-, +)  (+, -)  (+, -)  (+, -)  (+, -)

But now I'm stuck, I know that I should use a loop to verify if any combination is valid or not, but how can I do that?

2 Answers

If you think of + and - as True and False and apply an or operation for each column:

G_01    +    -    +    +    +    -    +
G_02    +    +    +    -    +    +    -
---------------------------------------
OR      +    +    +    +    +    +    +  ==> All true. This combo is valid

G_04    +    +    +    -    -    -    -
G_05    +    +    +    -    -    -    -
---------------------------------------
OR      +    +    +    -    -    -    -  ==> Not all true. This combo is invalid

The only remaining problem is how to compare them quickly. We can use numpy's array broadcasting feature to do that. Very briefly, array broadcasting is the act of performing operations on arrays of different sizes:

# When you compare an array to a scalar, the logical action is to compare
# every element of the array that scalar
[a, b, c] > d is equivalent to [a > d, b > d, c > d]

# If you want to compare every element of a list against every element of
# another list, things get a little tricky
[a, b, c] > [d, e] ???

# The trick is to raise the raise the second array up another dimension so you
# you can a comparison matrix. The first array remains 1D, the second array is
# now 2D
[a, b, c] > [[d], [e]]

# One way to visualize it
   d        e
a  a > d    a > e
b  b > d    b > e
c  c > d    c > e

Here's the answer to your question:

# Life is a lot easier if you put Guns on the index
df.set_index('Guns', inplace=True)

# A 2D array of True/False
a = df.applymap(lambda x: x == '+').to_numpy()

# A 3D array to be used for in the OR operation
b = a[:, None]

# OR-ing every gun with every other gun
c = np.all(a | b, axis=-1)

# This is what c looks like, with some labels added
#        G_01   G_02   G_03   G_04   G_05   G_06   G_07
#      |------------------------------------------------
# G_01 | False   True  False  False  False  False  False    ==> (G_01, G_02) is valid
# G_02 |  True  False   True  False  False   True  False    ==> (G_02, G_01), (G_02, G_03) and (G_02, G_06) are valid
# G_03 | False   True  False   True   True  False   True
# G_04 | False  False   True  False  False   True  False
# G_05 | False  False   True  False  False   True  False
# G_06 | False   True  False   True   True  False   True
# G_07 | False  False   True  False  False   True  False

# Obviously (G_01, G_02) and (G_02, G_01) are the same combo so we don't need
# to collect both of them. We only need to work with the upper triangle in the
# matrix (`triu` means triangle upper)
valid_combinations = [(df.index[i], df.index[j]) for i,j in np.dstack(np.triu_indices_from(c))[0] if c[i][j]]

Since it uses numpy's broadcasting, it benefits from all the underlying vectorization. I ran a 1000 x 1000 dataframe (1M elements) in less than 3 seconds.


Edit: to expand this to cover combinations of arbitrary size, you just keep raising the comparison matrix with each iteration:

def get_valid_combos(df, combo_size=2):
    assert combo_size >= 2, 'combo_size must be at least 2'

    a = df.applymap(lambda x: x == '+').to_numpy()
    result = a

    while combo_size > 1:
        a = a[:, None]
        result = result | a
        combo_size -= 1

    result = result.all(axis=-1)

    # Return True if array is monotonically increasing to avoid duplicates
    # like (G_1, G_2, G_3) and (G_1, G_3, G_2)
    is_increasing = lambda arr: (np.diff(arr) > 0).all()
    valid_indicies = np.array(result.nonzero()).transpose()
    return [tuple(df.index[idx]) for idx in valid_indicies if is_increasing(idx)]

Note that this grows exponentially. If you have n guns, it requires n ^ combo_size space and likely n ^ (2 * combo_size) time. There are opportunities for optimization: if G_1 and G_2 make a valid combination, anything with these 2 are also valid and hence save us some time. But I'm too lazy now.

You can use a for-loop to iterate over all of the combinations that you generated, and check them with an if-statement. An any can check if there are columns where both of them had a - symbol. Once you've figured out if a combination is valid, you can append it to a list of valid combinations.

valid_combinations = []
for combination in combinations:
    if not any(p[0] == "-" and p[1] == "-" for p in combination):
        valid_combinations.append(combination)

This can be simplified using a list comprehension:

valid_combinations = [combination for combination in combinations if not any(p[0] == "-" and p[1] == "-" for p in combination)]
Related