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: ValueAB Output 1: GroupIDValue1A1B2A3B Input 2: ValueABC Output 2: GroupIDValue1A1B1C2A2B3A3C4B4C5A6B7C OBS: The real case input right now has 15 values, and it should generate 32767 groups (2^n-1). Thanks.

Generate all possible combinations of values from a column

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

Output 1:

GroupID	Value
1	A
1	B
2	A
3	B

Input 2:

Value

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.

Transformation

Preparation

Accepted answers

SPetrie

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.

All comments

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.

0528-JackTequila.yxzp

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)

Generate all possible combinations of values from a column.yxmd

SPetrie

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.

Generate.yxzp

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.

Qiu

@JackTequila
I was not really considering the computing cost, and I guess I need to keep that in mind. 😁
@SPetrie gives very good one and I will definitely going to study it. Thanks.

Qiu

@SPetrie
Maybe you should submit this as an idea for weekly challenge. 😂

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703