get all possible combinations of column values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi gurus...
I have to do some simple task but I don't know what to do... I need to get all combination (not permutation) of 2 values given a "group id" value
I have this
group id | value |
1 | a |
1 | b |
1 | c |
2 | b |
2 | c |
2 | d |
And I want to have this
group_id | combination |
1 | a b |
1 | a c |
1 | b c |
2 | b c |
2 | b d |
2 | c d |
I have to do this combinating 2 values by now, but it probably will be required combinate 3 values. I don't have a clue what to do, I don't even know what labels put to this post.... lol
any help?
Thanks in advice...
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
One thing you could try is select the variables in question and send them into the R tool, then use expand.grid() on that subset of variables.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
another alternative is to do a self-join. Join this data-set to itself using the GroupID field.
I've attached a worked example to this post - with two legs. One includes duplicate combinations (like aa; bb etc), and the other filters these out.
If this solves the problem, would you mind marking this as solved - or if you have further questions, then it may be worth attaching a sample workflow with a mocked up data-set so that the community can help you out by completing the workflow.
Thanks @iperich
Sean
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Self join did the trick. And if I want to mix 3 fields, I can combine the 3rd with the mix of the other two, checking that the combination of the first two doesn't contain the 3rd one.... Great!!
Nice one, Sean, thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @SeanAdams,
This solution works great with a project am working on as well.
But on top this, I have an additional requirement which although sounds simple is becoming cumbersome to achieve the required results.
To explain, lets consider the same example you mentioned the solution to. My requirement is to create a combination of the group-id along with the combinations of the values (screenshot attached).
Any idea how this can be achieved?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @KK17,
I think I have solved your example using the append fields tool and connecting the original data up twice so you end up with all combinations.
Then you can use a formula tool to combine the two different values for your new group and new value fields.
Please see the attached workflow.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JoeS,
Works perfectly fine and the issue is solved. Thanks a lot!
For some reason, I was grouping my data from 2 columns and then feeding in to the append tool which essentially was eliminating half of data. Silly me!
Thanks again! Cheers!
