1
$\begingroup$

Similar to the 1st case, now given a list

lst = {{"row1", "H1", "C1", 1},
       {"row1", "H1", "C2", 2}, 
       {"row1", "H1", "C3", 3}, 
       {"row2", "H2", "D1", 2}, 
       {"row2", "H2", "D2", 3}, 
       {"row2", "H2", "D3", 4}, 
       {"row3", "H1", "C2", 8}, 
       {"row3", "H2", "D2", 9}, 
       {"row3", "H2", "D3", 10}, 
       {"row4", "", "", ""},
       {"row5", "H2", "D2", 10}, 
       {"row5", "H2", "D3", 11}, 
       {"row5", "H3", "E1", 12},
       {"row6", "", "", ""}};

I want to get a sparse array like this:

lstSparse = {{"0", "H1", "0", "0", "H2", "0", "0", "H3"}, 
            {"0", "C1", "C2", "C3", "D1", "D2", "D3", "E1"}, 
            {"row1", 1, 2, 3, 0, 0, 0, 0}, 
            {"row2", 0, 0, 0, 2, 3, 4, 0}, 
            {"row3", 0, 8, 0, 0, 9, 10, 0}, 
            {"row4", 0, 0, 0, 0, 0, 0, 0}, 
            {"row5", 0, 0, 0, 0, 10, 11, 12},
            {"row6", 0, 0, 0, 0, 0, 0, 0}};

or ZEROs could be replaced by blank string too, to be like this.

$$\left(\begin{array}{cccccccc} \text{0} & \text{H1} & \text{0} & \text{0} & \text{H2} & \text{0} & \text{0} & \text{H3} \\ \text{0} & \text{C1} & \text{C2} & \text{C3} & \text{D1} & \text{D2} & \text{D3} & \text{E1} \\ \text{row1} & 1 & 2 & 3 & 0 & 0 & 0 & 0 \\ \text{row2} & 0 & 0 & 0 & 2 & 3 & 4 & 0 \\ \text{row3} & 0 & 8 & 0 & 0 & 9 & 10 & 0 \\ \text{row4} & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row5} & 0 & 0 & 0 & 0 & 10 & 11 & 12 \\ \text{row6} & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \end{array}\right) $$$$ \to $$$$ \left(\begin{array}{cccccccc} \text{} & \text{H1} & \text{} & \text{} & \text{H2} & \text{} & \text{} & \text{H3} \\ \text{} & \text{C1} & \text{C2} & \text{C3} & \text{D1} & \text{D2} & \text{D3} & \text{E1} \\ \text{row1} & 1 & 2 & 3 & \text{} & \text{} & \text{} & \text{} \\ \text{row2} & \text{} & \text{} & \text{} & 2 & 3 & 4 & \text{} \\ \text{row3} & \text{} & 8 & \text{} & \text{} & 9 & 10 & \text{} \\ \text{row4} & \text{} & \text{} & \text{} & \text{} & \text{} & \text{} & \text{} \\ \text{row5} & \text{} & \text{} & \text{} & \text{} & 10 & 11 & 12 \\ \text{row6} & \text{} & \text{} & \text{} & \text{} & \text{} & \text{} & \text{} \\ \end{array}\right)$$

Update: a more complicated test data could be found here:

URLDownload["https://i.stack.imgur.com/ztb03.png", "C:\\test.zip"];
ExtractArchive["C:\\test.zip", "C:\\"];

lstData = FromCharacterCode[BinaryReadList["C:\\test.txt"]];
lst = (StringSplit[StringReplace[#, {"{" -> "", "}" -> ""}], ","] & /@
     StringSplit[lstData, "\r\n"]);

test data behind picture

$\endgroup$
2
$\begingroup$
ClearAll[f]
f = Module[{spa, headers, 
      rc = Select[FreeQ[""]]@SortBy[#[[All, {1, 2, 3, 4}]], {#[[3]] &, First}], 
      rows = DeleteDuplicates[#[[All, 1]]], nr, nc, cIndex, rIndex},
    {nr, nc} = Length /@ {rows, DeleteDuplicates@rc[[All, 3]]};
    cIndex = MapIndexed[# -> #2[[1]] &, DeleteDuplicates@rc[[All, 3]]];
    rIndex = MapIndexed[# -> #2[[1]] &, rows];
    spa = SparseArray[Thread[rc[[All, {1, 3}]]->rc[[All, 4]] /. rIndex /. cIndex], 
      {nr, nc}];
    headers = PadLeft[#, 1 + Length@#] & /@ 
      {SequenceReplace[#[[1]], {b : (a_String) ..} :>
        Sequence[a, ## & @@ ConstantArray[0, Length[{b}] - 1]]], #[[2]]} &[
          Transpose[DeleteDuplicatesBy[Last]@rc[[All, {2, 3}]]]];
    Join[headers, Join[List /@ rows, spa, 2]]] &;

TeXForm @ f @ lst

$\left( \begin{array}{cccccccc} 0 & \text{H1} & 0 & 0 & \text{H2} & 0 & 0 & \text{H3} \\ 0 & \text{C1} & \text{C2} & \text{C3} & \text{D1} & \text{D2} & \text{D3} & \text{E1} \\ \text{row1} & 1 & 2 & 3 & 0 & 0 & 0 & 0 \\ \text{row2} & 0 & 0 & 0 & 2 & 3 & 4 & 0 \\ \text{row3} & 0 & 8 & 0 & 0 & 9 & 10 & 0 \\ \text{row4} & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row5} & 0 & 0 & 0 & 0 & 10 & 11 & 12 \\ \text{row6} & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \end{array} \right)$

lst2 = Join[lst, {{"row7", "", "", ""}, {"row8", "H1", "C4", 10}, {"row9", 
     "H2", "C5", 20}, {"row10", "H3", "E2", 30}}];
TeXForm @ f @ lst2

$\left( \begin{array}{ccccccccccc} 0 & \text{H1} & 0 & 0 & 0 & \text{H2} & 0 & 0 & 0 & \text{H3} & 0 \\ 0 & \text{C1} & \text{C2} & \text{C3} & \text{C4} & \text{C5} & \text{D1} & \text{D2} & \text{D3} & \text{E1} & \text{E2} \\ \text{row1} & 1 & 2 & 3 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row2} & 0 & 0 & 0 & 0 & 0 & 2 & 3 & 4 & 0 & 0 \\ \text{row3} & 0 & 8 & 0 & 0 & 0 & 0 & 9 & 10 & 0 & 0 \\ \text{row4} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row5} & 0 & 0 & 0 & 0 & 0 & 0 & 10 & 11 & 12 & 0 \\ \text{row6} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row7} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row8} & 0 & 0 & 0 & 10 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row9} & 0 & 0 & 0 & 0 & 20 & 0 & 0 & 0 & 0 & 0 \\ \text{row10} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 30 \\ \end{array} \right)$

$\endgroup$
  • $\begingroup$ Thanks!Also I hope H1``H2``H3 use the original values not H with index insted. $\endgroup$ – Jerry Nov 28 '18 at 3:46
  • $\begingroup$ Perfect codes help me lean sparse arrays! $\endgroup$ – Jerry Nov 28 '18 at 4:58
  • $\begingroup$ I made a test appending {"row8", "H1", "C4", 10}, {"row9", "H2", "C5", 20}, {"row10", "H3", "E2", 30} to lst, but the result does not appear as I wanted $\endgroup$ – Jerry Nov 28 '18 at 7:29
  • $\begingroup$ Index number after row\C-D-E-\H might be not existing, so Sort might be applied before formating lists. $\endgroup$ – Jerry Nov 28 '18 at 7:37
  • $\begingroup$ @Jerry, you are right about the need to sort if the input list is not in the right order. I will post an update with a fix. $\endgroup$ – kglr Nov 28 '18 at 7:44
0
$\begingroup$

Thanks to @kglr, I could developed fMakeSparseList based on Dataset operation.

fMakeSparseList[ds_, {indexColName_?StringQ, classColName_?StringQ, 
   headColName_?StringQ, valueColName_?StringQ}] :=
    Module[ {dsCols, rc, rows, rowc, rowh, cols, nr, nc, cIndex, rIndex, 
      spa, ch, ch2, headers, result},

        dsCols = Normal@Keys@First@ds;
        rc = Select[FreeQ[""]]@
          ds[SortBy[
            ToExpression[
             "{#" <> classColName <> ",#" <> headColName <> ",# " <> 
              indexColName <> "}&"]]];
        rows = DeleteDuplicates@ds[All, indexColName];
        rowc = Complement[dsCols, {classColName, headColName, valueColName}];
        rowh = Values@DeleteDuplicates@ds[All, rowc];
        cols = DeleteDuplicates@rc[All, headColName];
        {nr, nc} = Length /@ {rows, cols};

        (*building sparse array*)
        cIndex = Normal@MapIndexed[# -> #2[[1]] &, cols];
        rIndex = Normal@MapIndexed[# -> #2[[1]] &, rows];
        spa = SparseArray[
          Thread[Values@Normal@rc[All, {indexColName, headColName}] -> 
              Normal@rc[All, valueColName]] /. rIndex /. cIndex, {nr, nc}];

        (*Headers*)
        ch = Transpose[
          DeleteDuplicatesBy[Last]@
           Values@Normal@rc[[All, {classColName, headColName}]]];
        ch2 = {SequenceReplace[#[[1]], {b : (a_String) ..} :> 
              Sequence[a, ## & @@ ConstantArray[0, Length[{b}] - 1]]], #[[
             2]]} &@ch;
        headers = PadLeft[#, Length@rowc + Length@#] & /@ ch2;
        result = Join[headers, Join[Flatten /@ List /@ rowh, spa, 2]];

        (*return*)
        Return[result];
    ]

before use fMakeSparseList, the list should be converted to dataset,

fGetDatasetFromList[list_?ListQ,cols_?ListQ] :=
    Module[ {ds,cols2,i},
        (*headers*)
        cols2 = If[ Length[cols]<=0,
                    Table["Col"<>ToString[i],{i,1,Length[Transpose@list]}],
                    cols
                ];
        ds = Dataset[list][All,AssociationThread[cols2,Range[Length[cols2]]]];
        (*return*)
        Return[ds];
    ]
fGetDatasetFromList[list_?ListQ] := fGetDatasetFromList[list,{}]

Now a new demo source list is listed as

lst = {{"row1", "", "H1", "C1", 1}, 
       {"row1", "", "H1", "C2", 2}, 
       {"row1", "", "H1", "C3", 3}, 
       {"row2", "id", "H2", "D1", 2}, 
       {"row2", "id", "H2", "D2", 3}, 
       {"row2", "id", "H2", "D3", 4}, 
       {"row3", "id", "H1", "C2", 8}, 
       {"row3", "id", "H2", "D2", 9}, 
       {"row3", "id", "H2", "D3", 10}, 
       {"row4", "id", "", "", ""}, 
       {"row5", "id", "H2", "D2", 10}, 
       {"row5", "id", "H2", "D3", 11}, 
       {"row5", "id", "H3", "E1", 12}, 
       {"row6", "id", "", "", ""}, 
       {"row7", "id", "H1", "C1", 9}, 
       {"row8", "id", "H1", "C4", 10}, 
       {"row9", "id", "H2", "C5", 20}, 
       {"row10", "id", "H3", "E2", 30},
       {"row11", "", "H3", "E2", 31}};

 ds = fGetDatasetFromList[lst];

enter image description here

then apply fMakeSparseList on ds and columns to get the result.

fMakeSparseList[ds, {"Col1", "Col3", "Col4", "Col5"}] // TableForm//TeXForm

$$ \left( \begin{array}{ccccccccccc} 0 & 0 & \text{H1} & 0 & 0 & \text{H2} & 0 & 0 & 0 & \text{H3} & 0 \\ 0 & 0 & \text{C1} & \text{C2} & \text{C4} & \text{C5} & \text{D1} & \text{D2} & \text{D3} & \text{E1} & \text{E2} \\ \text{row1} & \text{} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row2} & \text{id} & 0 & 0 & 0 & 0 & 2 & 3 & 4 & 0 & 0 \\ \text{row3} & \text{id} & 0 & 8 & 0 & 0 & 0 & 9 & 10 & 0 & 0 \\ \text{row4} & \text{id} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row5} & \text{id} & 0 & 0 & 0 & 0 & 0 & 10 & 11 & 12 & 0 \\ \text{row6} & \text{id} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row7} & \text{id} & 9 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row8} & \text{id} & 0 & 0 & 10 & 0 & 0 & 0 & 0 & 0 & 0 \\ \text{row9} & \text{id} & 0 & 0 & 0 & 20 & 0 & 0 & 0 & 0 & 0 \\ \text{row10} & \text{id} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 30 \\ \text{row11} & \text{} & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 0 & 31 \\ \end{array}\right)$$

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.