list of items and group of alternative items

Question

I have the following list of item

The numbers at the side of some items indicate that they are alternatives. So, in the previous example B, C and D are alternatives, and F and G are alternatives. Due to the presence of these groups of alternative items the list corresponds to the following lists

I have to write a Mathematica script which takes an Excel table like the first as input and gives the second table as output. Of course the number of total items of the first table is random, the number of groups of alternative items is random and the number of item inside each group of alternative items is random. Could you suggest me how I can set up that script?

Answer 1

Assume you have the previous input which corresponds to the table

groupped={{"A", "G"}, {"E", "F"}, {"B", "H"}, {"C", "D"}}

Note how the first list corresponds to items with no alternatives while the following lists contain the sets of alternative options.

(

If table={{"A", Null}, {"B", 2}, {"C", 3}, {"D", 3}, {"E", 1}, {"F", 1}, {"G", Null}, {"H", 2}}

is the input then

groupped = Part[SortBy[GatherBy[table /. Null -> 0, Last], #[[-1]][[-1]] &], All, All, 1]

)

The following code produces all the possible combinations of items:

{first, rest} = {First[#], Rest[#]} &@groupped; Flatten[Outer[Flatten[{first, {##}}] &, Sequence @@ rest], 2]

In this particular example, the output is

{{"A", "G", "E", "B", "C"}, {"A", "G", "E", "B", "D"}, {"A", "G", "E", "H", "C"}, {"A", "G", "E", "H", "D"}, {"A", "G", "F", "B", "C"}, {"A", "G", "F", "B", "D"}, {"A", "G", "F", "H", "C"}, {"A", "G", "F", "H", "D"}}

or in tabular form

Answer 2

EDIT: I edited the answer so that now it conforms with the format of the imported .xls file.

You Import the data with

data = Import["data.xls"]

{{{"A", ""}, {"B", 1.}, {"C", 1.}, {"D", 1.}, {"E", ""}, {"F", 2.}, {"G", 2.}, {"H", ""}}}

That's a list with one element: a list of the tuples, so let's do

list = First@data

{{"A", ""}, {"B", 1.}, {"C", 1.}, {"D", 1.}, {"E", ""}, {"F", 2.}, {"G", 2.}, {"H", ""}}

I will employ the function removeFrom from this answer:

removeFrom[b_List, a_List] := Module[{f}, f[_] = 0;
(f[#] = -#2) & @@@ Tally[a];
Pick[b, UnitStep[f[#]++ & /@ b], 1]]

Find positions of non-alternatives:

complPos = Partition[Position[list, ""][[All, 1]], 1]

{{1}, {5}, {8}}

Positions of alternatives:

pos = removeFrom[Partition[Range[Length@list], 1], complPos]

{{2}, {3}, {4}, {6}, {7}}

Non-alternative elements:

ones = Flatten@Table[list[[i, 1]], {i, complPos}]

{"A", "E", "H"}

Alternatives with their indices:

twos = Flatten[Table[list[[i]], {i, pos}], 1]

{{"B", 1.}, {"C", 1.}, {"D", 1.}, {"F", 2.}, {"G", 2.}}

Strip them of the indices and group the corresponding alternatives:

grouped = GatherBy[twos, Last]
elems = Table[grouped[[i, All, 1]], {i, 1, Length@grouped}]

{{{"B", 1.}, {"C", 1.}, {"D", 1.}}, {{"F", 2.}, {"G", 2.}}}

{{"B", "C", "D"}, {"F", "G"}}

Make all possible combinations of the alternatives:

tuples = Tuples[elems]

{{"B", "F"}, {"B", "G"}, {"C", "F"}, {"C", "G"}, {"D", "F"}, {"D", "G"}}

Make all combinations including the non-alternatives:

result = Map[Sort, Table[Join[tuples[[i]], ones], {i, 1, Length@tuples}]]

{{"A", "B", "E", "F", "H"}, {"A", "B", "E", "G", "H"}, {"A", "C", "E", "F", "H"}, {"A", "C", "E", "G", "H"}, {"A", "D", "E", "F", "H"}, {"A", "D", "E", "G", "H"}}

Display it as a table where the rows are the possibilites:

TableForm@result

or if you want the columns to be the answers:

TableForm@Transpose@result

If you want the result to be really in the form you posted, i.e. with empty places in the table, then do

allElems = Sort@Flatten@Join[ones, elems]

{"A", "B", "C", "D", "E", "F", "G", "H"}

del = Table[Flatten@Table[
Position[allElems, removeFrom[allElems, result[[j]]][[i]]], {i, 1,
  Length@removeFrom[allElems, result[[j]]]}], {j, 1, Length@result}]

{{3, 4, 7}, {3, 4, 6}, {2, 4, 7}, {2, 4, 6}, {2, 3, 7}, {2, 3, 6}}

final = Table[ReplacePart[allElems, 
Table[del[[j, k]] -> {}, {k, 1, Length@del[[j]]}]], {j, 1, Length@result}]

{{"A", "B", {}, {}, "E", "F", {}, "H"}, {"A", "B", {}, {}, "E", {}, "G", "H"}, {"A", {}, "C", {}, "E", "F", {}, "H"}, {"A", {}, "C", {}, "E", {}, "G", "H"}, {"A", {}, {}, "D", "E", "F", {}, "H"}, {"A", {}, {}, "D", "E", {}, "G", "H"}}

And finally (for possibilities in rows)

TableForm@final

or (for answers in columns)

TableForm@Transpose@final

NOTE: Keep in mind that C and D are protected symbols; if you use strings "C" and "D" like above then there is no problem, but the code may crash if you decide on some point to change strings into expressions. That's why I used in the first version of my answer lower-case letters a,b,c,.... Additionally, I have a concern regarding the indices in data/list: in MMA there's a substantial difference between 1 and 1.. If the programme that was used to produce the .xls file made an error and instead of 1. placed in some place, e.g., 1.00000000000000001, the code will not give the correct answer.