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.sstatic.net/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 Answers 2

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$
10
  • $\begingroup$ Thanks!Also I hope H1``H2``H3 use the original values not H with index insted. $\endgroup$
    – Jerry
    Commented Nov 28, 2018 at 3:46
  • $\begingroup$ Perfect codes help me lean sparse arrays! $\endgroup$
    – Jerry
    Commented Nov 28, 2018 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
    Commented Nov 28, 2018 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
    Commented Nov 28, 2018 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
    Commented Nov 28, 2018 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 and acknowledge you have read our privacy policy.

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