Pandas value lookup but with duplicate values-CodePudding

I have a list of lists containing prices of items, the order in which these elements are in, also matter. I also have a dataframe with the items in these lists and their correlating prices. I'm trying to iterate through each list and basically replace the price element in the list of lists with the corresponding item. The problem I have is that there are two items with the same price. Currently my code is just adding both of these duplicate priced items to the list but I want it to create a separate list for both items.

Current code:

data = {'Item':['Apples', 'Cereal', 'Corn', 'Pasta', 'Detergent', 'Coffee', 'Ketchup', 'Oats', 'Olive Oil'],
        'Price':[4, 2, 6, 5, 10, 9, 2, 3, 1]}
 
df = pd.DataFrame(data)

combos = [[4, 2, 3, 6], [2, 10, 2, 4], [6, 1, 10, 2]]

testing = []

for list in combos:
    output = df.set_index('Price').loc[list, 'Item'].to_numpy().tolist()
    testing.append(output)
print(testing)

Output:

[['Apples', 'Cereal', 'Ketchup', 'Oats', 'Corn'], ['Cereal', 'Ketchup', 'Detergent', 'Cereal', 'Ketchup', 'Apples'], ['Corn', 'Olive Oil', 'Detergent', Cereal, 'Ketchup']]

Outcome I want:

[['Apples', 'Cereal', 'Oats', 'Corn'], ['Cereal', 'Detergent', 'Cereal', 'Apples'], ['Cereal', 'Detergent', 'Ketchup', 'Apples'], ['Ketchup', 'Detergent', 'Cereal', 'Apples'], ['Ketchup', 'Detergent', 'Ketchup', 'Apples'], ['Corn', 'Olive Oil', 'Detergent', 'Cereal'], ['Corn', 'Olive Oil', 'Detergent', 'Ketchup']]

CodePudding user response：

One way using itertools.product and chain:

from itertools import product, chain

prices = df.groupby("Price")["Item"].apply(list)
list(chain.from_iterable(product(*prices.loc[c]) for c in combos))

Output:

[('Apples', 'Cereal', 'Oats', 'Corn'),
 ('Apples', 'Ketchup', 'Oats', 'Corn'),
 ('Cereal', 'Detergent', 'Cereal', 'Apples'),
 ('Cereal', 'Detergent', 'Ketchup', 'Apples'),
 ('Ketchup', 'Detergent', 'Cereal', 'Apples'),
 ('Ketchup', 'Detergent', 'Ketchup', 'Apples'),
 ('Corn', 'Olive Oil', 'Detergent', 'Cereal'),
 ('Corn', 'Olive Oil', 'Detergent', 'Ketchup')]

CodePudding user response：

You can also use pd.MultiIndex.from_product to produce Cartesian product:

prices = df.groupby("Price")["Item"].apply(list)
out = []
for combo in combos:
    product = pd.MultiIndex.from_product(prices.loc[combo]).tolist() 
    out.extend(map(list, product))

Output:

[['Apples', 'Cereal', 'Oats', 'Corn'], 
 ['Apples', 'Ketchup', 'Oats', 'Corn'], 
 ['Cereal', 'Detergent', 'Cereal', 'Apples'], 
 ['Cereal', 'Detergent', 'Ketchup', 'Apples'], 
 ['Ketchup', 'Detergent', 'Cereal', 'Apples'], 
 ['Ketchup', 'Detergent', 'Ketchup', 'Apples'], 
 ['Corn', 'Olive Oil', 'Detergent', 'Cereal'], 
 ['Corn', 'Olive Oil', 'Detergent', 'Ketchup']]