Combinations with one column's content fixed

[take2]

This question is a follow up of this question: Combinations in Calc

Now that @Karolus has explained how to create combinations with python macro, I’m trying to use it to make combinations with three columns. Actually, it’s still two columns and the third column is always the same. Here is an example.

This is in the document at the start:

A     B       C
Abc   12:34   FIXED
Def   56:78   FIXED
Ghi   90:12   FIXED
Jkl   34:56   FIXED
...

Desired outcome is this:

A     B     C    D      E
Abc  12:34  Def  56:78  FIXED
Abc  12:34  Ghi  90:12  FIXED
Abc  12:34  Jkl  34:56  FIXED
Def  56:78  Ghi  90:12  FIXED
Def  56:78  Jkl  34:56  FIXED
Ghi  90:12  Jkl  34:56  FIXED

Here is @Karolus’ solution for two columns:

from itertools import combinations

def combinations_of_two(*_):
    """
    combinations of 2 rows from Current Selection
    output to the columns right
    """

    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    data = sel.DataArray
    sheet = sel.Spreadsheet
    out = tuple( a + b for a, b in combinations( data, 2 ) )
    positions = (2, 1, 5, len(out))
    target = sheet.getCellRangeByPosition(*positions)
    target.setDataArray(out)

How can this be adapted to always add the third column? I tried with changing both occurences of “2” to “3”, but it seems that it’s not referring to the number of columns.

EDIT: I have attached an example of the data I’m working with and how it would be outputted in the ideal case. There are many sets of data and ideally it would notice when there are empty rows and then create another combination.

example.ods

[Lupp]

ask51731UpwardPairs002a.ods
The scaling-up was anticipated. In the same way you may add another 121 data columns.

= Editing with regard to the comment =

I’m afraid there will always be the next variant. I actually often use many helpers, but clearly they will mostly not be self-explaining as applying a function might be. Writing a tutorial has to wait.

I therefore betrayed some principles I regularly observe and wrote a function that might cover most of your ( @take2 ) needs. It will return the results in one chunk as an array. I think I mentioned already that there are few things with advantages only. In this case some restrictions or complications for maintenance must be accepted in addition to the general disadvantages of using custom code.

See attached! (Some debugging statements deleted meanwhile. Attachment now cleaner!)
ask51731CombinationGenerator003Examples.ods

= Editing with regard to the second comment =

Ok. There’s something like a tutorial now - and refined code as well, the ‘Releasable version’. I took this thread as an excuse for a quick relapse into programming which I once was fond of. If you study my explanations, the demonstrations (and the commented code, maybe, in addition) you should find out how to apply the functions (most preferable the last one in my eyes). Please feel free to ask additional questions.ask51731CombinationGenerator004_CodeDemoTutorial.ods

= Editing with regard to an expected variant of the task =

Anticipating a need possibly arising one day to also be able to generate combinations allowing for repetions of original items I reworked the code a bit and created a function using a Boolean parameter to control the distinction. It is contained and demonstrated in the new example ask51731CombinationGenerator005_CodeDemoTutorial.ods which will be most likely the last one. My schedule for this marked “Done”.

I would suggest to mainly use the newly appended function.

= Editing 2015-06-19 =

As you were afraid to have to adapt too many parameters when calling one of the functions I wrote a little frame for the call avoiding most of it having found I fond the time. There will regularly only one parameter, the range address, need adapting now. See attached.
51854Example.ods

[take2]

I get it now how your document works. The problem is that I don’t have one set of data, but many of them and with this solution I would have to change parameters for every one of them, right? Is there a way to make it work with multiple sets? I have attached an example of the data I’m working with.

[take2]

Thank you for a very detailed response. This obviously works, so I’ll accept the answer, but please explain how exactly should macro be applied. I get this error when I run it, no matter if I select the original data or not: “BASIC runtime error. Argument is not optional.”

[Lupp]

This error did surely not occur runnung the demo, did it? Regularly it points to the fact that a parameter position was left out when entering the actual parameters corresponding to the formal ones into a function call. Which line of the BASIC code threw the error?

Also note the new editing of my answer, please.

[take2]

No, it appeared when running the macro through Calc interface. This other example is well explained, thank you.

[Lupp]

That’s inevitable! If you try to run a function depending on parameters like a Sub it must fail this way. Parameters were not assigned values by the call and didn’t even get memory allocated like local variables.

By the way. How did you plan to pass any information needed for running (like parameters) to the Sub you originally wanted?

[karolus]

Modified Function : ( overwrites original Data )

from itertools import combinations

def combinations_of_two_with_tail(*_):
    """
    combinations of 2 rows from Current Selection
    output to the columns right
    """

    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    data = sel.FormulaArray
    sheet = sel.Spreadsheet
    out = tuple(a[:-1] + b for a, b in combinations(data,2))
    
    positions = (0, # 1st Column -
                 0, # 1st Row - from A1 
                 # to calculated lower right corner
                 len(out[0])-1, # last Columnindex
                 len(out)-1 )    # last RowIndex
    target = sheet.getCellRangeByPosition(*positions)
    target.setFormulaArray(out)

[take2]

Is there a way to output it in 4th column (or any other column to the right), similar to the first solution? The thing is that I have many different sets of data in the first 3 columns (separated by 1-2 empty rows) and when combining the data from one set I don’t want to delete the sets underneath. I have edited the question and added an ods example.