Python - Combination

Question

A challenge on working with finding which 2 Suppliers can produce all products. In this Case, Supplier A & Supplier B are sufficient to fulfill(Either A or B can produce the product), which means only contracting with A & B can complete the production. May I seek some helps from here? Thanks

    ID Top 1 Group Top 2 Group Top 3 Group
0    1  Supplier A  Supplier B  Supplier C
1    2  Supplier B  Supplier A         NaN
2    3  Supplier C  Supplier A         NaN
3    4  Supplier A  Supplier B  Supplier C
4    5  Supplier A  Supplier B         NaN
5    6  Supplier B  Supplier C  Supplier A
6    7  Supplier B         NaN         NaN
7    8  Supplier A  Supplier B  Supplier C
8    9  Supplier A         NaN         NaN
9   10  Supplier A  Supplier C  Supplier B
10  11  Supplier A  Supplier B  Supplier C
11  12  Supplier B  Supplier A         NaN
12  13  Supplier C  Supplier A  Supplier B
13  14  Supplier B  Supplier C  Supplier A
14  15  Supplier B  Supplier C         NaN

{'ID': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10, 10: 11, 11: 12, 12: 13, 13: 14, 14: 15}, 'Top 1 Group': {0: 'Supplier A', 1: 'Supplier B', 2: 'Supplier C', 3: 'Supplier A', 4: 'Supplier A', 5: 'Supplier B', 6: 'Supplier B', 7: 'Supplier A', 8: 'Supplier A', 9: 'Supplier A', 10: 'Supplier A', 11: 'Supplier B', 12: 'Supplier C', 13: 'Supplier B', 14: 'Supplier B'}, 'Top 2 Group': {0: 'Supplier B', 1: 'Supplier A', 2: 'Supplier A', 3: 'Supplier B', 4: 'Supplier B', 5: 'Supplier C', 6: nan, 7: 'Supplier B', 8: nan, 9: 'Supplier C', 10: 'Supplier B', 11: 'Supplier A', 12: 'Supplier A', 13: 'Supplier C', 14: 'Supplier C'}, 'Top 3 Group': {0: 'Supplier C', 1: nan, 2: nan, 3: 'Supplier C', 4: nan, 5: 'Supplier A', 6: nan, 7: 'Supplier C', 8: nan, 9: 'Supplier B', 10: 'Supplier C', 11: nan, 12: 'Supplier B', 13: 'Supplier A', 14: nan}}

Desired Outcome(The Maximum products each combination can produce):

[{('Supplier A',): "13"},  
{('Supplier B',): "13"},  
{('Supplier C',): "10"},  
{('Supplier A', 'Supplier B'): "15"},  
{('Supplier A', 'Supplier C'): "14"},  
{('Supplier B', 'Supplier C'): "14"},  
{('Supplier A', 'Supplier B', 'Supplier C'): "15"}]

Answer 1

Using combination of pandas, numpy and itertools it can be done in this way.

import itertools
import pandas as pd
import numpy as np
df = pd.read_csv(io.StringIO("""    ID  Top 1 Group  Top 2 Group  Top 3 Group
0   1   Supplier A  Supplier B  Supplier C
1   2   Supplier B  Supplier A  NaN
2   3   Supplier C  Supplier A  NaN
3   4   Supplier A  Supplier B  Supplier C
4   5   Supplier A  Supplier B  NaN
5   6   Supplier B  Supplier C  Supplier A
6   7   Supplier B  NaN  NaN
7   8   Supplier A  Supplier B  Supplier C
8   9   Supplier A  NaN  NaN
9   10  Supplier A  Supplier C  Supplier B
10  11  Supplier A  Supplier B  Supplier C
11  12  Supplier B  Supplier A  NaN
12  13  Supplier C  Supplier A  Supplier B
13  14  Supplier B  Supplier C  Supplier A
14  15  Supplier B  Supplier C  NaN"""), sep="\s\s+", engine="python").replace({None:np.nan, "NaN":np.nan})

# columns that contain suppliers
cols = [c for c in df.columns if "Top" in c]
# get unique suppliers
suppl = np.unique(np.concatenate([df[c].dropna() for c in cols]))

result = []
for sn in range(len(suppl)):
    # generate combinations of suppliers
    for combi in itertools.combinations(suppl, sn+1):
        # generate a truth matrix and then work out if all rows have been fulfilled
        result.append({combi:df.loc[:,cols].isin(list(combi)).T.any().all()})

output

[{('Supplier A',): False},
 {('Supplier B',): False},
 {('Supplier C',): False},
 {('Supplier A', 'Supplier B'): True},
 {('Supplier A', 'Supplier C'): False},
 {('Supplier B', 'Supplier C'): False},
 {('Supplier A', 'Supplier B', 'Supplier C'): True}]

updated

From comments two additional requirements are required

1. count how many items are filled by a combination of suppliers
2. record items that are filled by a combination of suppliers

The second new requirement means you use truth matrix as mask to get IDs that are filled.

# columns that contain suppliers
cols = [c for c in df.columns if "Top" in c]
# get unique suppliers
suppl = np.unique(np.concatenate([df[c].dropna() for c in cols]))

result = []
for sn in range(len(suppl)):
    # generate combinations of suppliers
    for combi in itertools.combinations(suppl, sn+1):
        # generate a truth matrix and then work out if all rows have been fulfilled
        mask = df.loc[:,cols].isin(list(combi)).T.any()
        result.append({combi:mask.all(),
                      "placed":mask.sum(),
                      "filled":df.loc[mask, "ID"].values.tolist() })
        
result

output

[{('Supplier A',): False,
  'placed': 13,
  'filled': [1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14]},
 {('Supplier B',): False,
  'placed': 13,
  'filled': [1, 2, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15]},
 {('Supplier C',): False,
  'placed': 10,
  'filled': [1, 3, 4, 6, 8, 10, 11, 13, 14, 15]},
 {('Supplier A', 'Supplier B'): True,
  'placed': 15,
  'filled': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]},
 {('Supplier A', 'Supplier C'): False,
  'placed': 14,
  'filled': [1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15]},
 {('Supplier B', 'Supplier C'): False,
  'placed': 14,
  'filled': [1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15]},
 {('Supplier A', 'Supplier B', 'Supplier C'): True,
  'placed': 15,
  'filled': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]}]