Generate all possible combinations of values from a column

[JackTequila]

Hi all,

My doubt is, how to generate all possible combinations of elements from a given list and assign each combination a unique group ID? Here are some examples

Input 1:

Value

A

B

Output 1:

GroupID	Value
1	A
1	B
2	A
3	B

Input 2:

Value

A

B

C

Output 2:

GroupID	Value
1	A
1	B
1	C
2	A
2	B
3	A
3	C
4	B
4	C
5	A
6	B
7	C

OBS: The real case input right now has 15 values, and it should generate 32767 groups (2^n-1).

Thanks.

[Yoshiro_Fujimori]

@JackTequila ,

This can be an interesting weekly challenge.

I understand we cannot use Recursive call of a function on Alteryx (except for in Python / R Tools).

My best bet is using Iterative Macro, but not sure how it goes for now...

[Qiu]

@JackTequila 
I hope the order of GroupID is not significant here. 😁
As commented by @Yoshiro_Fujimori , I use the Iterative Macro for generation of combinations.

[aatalai]

Unless I miss understood the ask, couldn't you just append group id and values?

[Qiu]

@aatalai 
You are correct on Appending tools but there is more I believe. 😁

[JackTequila]

Your workflow seems to be working fine. However, unfortunately it is too compute expensive for my real case scenario (n=15). I tried to run it, gave up around 30 min of execution.

ChatGPT gave me a piece of python code able to run with n=15 in ~5 seconds using some iteration trick. Do you know if there is a way to convert this way of doing it to Alteryx? The python code works, so my quest should end here. But I'll have a hard time justifying the use of Python to my team, Alteryx's low code is practically a rule around here to facilitate future maintenance.

from ayx import Alteryx
import pandas as pd
import itertools

# Define the input DataFrame
input_df = Alteryx.read("#1")

# Extract values from the DataFrame
input_values = input_df['Column'].tolist()

# Create an empty list to hold the results
results = []

# Define a function to dynamically generate group values
def generate_group_values(input_values):
    group_id = 1
    length = len(input_values)
    
    # Generate all combinations for lengths from 1 to the length of input_values
    for r in range(1, length + 1):
        for combo in itertools.combinations(input_values, r):
            results.append({'Group': group_id, 'Column': combo})
            group_id += 1

# Generate the group values dynamically
generate_group_values(input_values)

# Expand the results list into rows of Group and Column
expanded_results = []
for item in results:
    group_id = item['Group']
    columns = item['Column']
    for col in columns:
        expanded_results.append({'Group': group_id, 'Column': col})

# Convert the results to a DataFrame
df = pd.DataFrame(expanded_results)

[SPetrie · Accepted solution]

I had a similar request recently and ended up making a batch macro for it. Its not as efficient as other code, but it seems to work well.

It can handle up to N=18 in a few seconds, but you will hit a wall soon afterwards. N=15 takes around 2 seconds in my flow example.

I concat the values to make it easier to read the output, but you can edit the macro to suit your own needs. 

[JackTequila]

Hi SPetrie. This is awesome

Can you please attach the workflow here? Its not showing up for me.

[SPetrie]

Sorry about that. I thought I had attached it.

[JackTequila]

This is really impressive, i ran it with n=20 and the execution time was ~7 seconds. After n=25 i hit the wall, but with this i have more than enough. Thanks a lot.