5
$\begingroup$

I would like to convert a matrix into a dataset. The structure of the matrix is:

  {{"5", "4", "3", "2", "1"},
   {"A", 3, 1, 2, 0, 4}, 
   {"B", 8, 29, 55, 14, 22}, 
   {"C", 15, 87, 418, 728, 340}, 
   {"D", 41, 28, 154, 821, 939}, 
   {"E", 3, 21, 78, 257, 594}}

The first nested list are "column" headers, while the first element of each subsequent list are the "row" headers. The structure of the dataset should be:

Column1    Column2    Value
A          5          3
A          4          1
A          3          2

...

E          5          3
E          4          21
E          3          78
E          2          257
E          1          594
$\endgroup$
  • 1
    $\begingroup$ have you tried anything? $\endgroup$ – Fraccalo May 21 '18 at 16:24
  • 1
    $\begingroup$ I appreciate all of the responses. The Wolfram documentation on reshaping lists and building associations from anything other than simple lists is a little bit thin. Parsing each of these was a good learning experience. Thanks. $\endgroup$ – dixontw May 23 '18 at 11:40
  • $\begingroup$ @Fraccalo This is not a trivial question for both newcomers and experienced WL users. I would forgive OP if he could not come up with anything that works. By the way, transformations to data long forms are single command one-liners in R. (With a certain popular, usually preloaded package.) (Also, R-immitaion packages in Python.) $\endgroup$ – Anton Antonov May 23 '18 at 13:33
  • $\begingroup$ @AntonAntonov sure, it wasn't intended as an accusation, I apologise if it sounds like it :D I was just asking if he had any kind of code we could have fixed/improved/commented! Then I got too busy and wasn't able to answer the question. Sorry again :) $\endgroup$ – Fraccalo May 23 '18 at 13:41
  • $\begingroup$ @Fraccalo It is fine, the main message of my previous comment is that this should be simpler... $\endgroup$ – Anton Antonov May 23 '18 at 14:12
4
$\begingroup$

Given:

m = { {"5", "4", "3", "2", "1"},
      {"A", 3, 1, 2, 0, 4}, 
      {"B", 8, 29, 55, 14, 22}, 
      {"C", 15, 87, 418, 728, 340}, 
      {"D", 41, 28, 154, 821, 939}, 
      {"E", 3, 21, 78, 257, 594} };

Then:

Dataset @ Flatten @ Array[
  <|"Column1" -> m[[#+1, 1]], "Column2" -> m[[1, #2]], "Value" -> m[[#+1, #2+1]]|>&
, {Length[m] - 1, Length[m[[1]]]}
]

dataset screenshot

$\endgroup$
3
$\begingroup$

This code produces the requested long form of the data:

data = {{"5", "4", "3", "2", "1"}, {"A", 3, 1, 2, 0, 4}, {"B", 8, 29, 
 55, 14, 22}, {"C", 15, 87, 418, 728, 340}, {"D", 41, 28, 154, 821, 
 939}, {"E", 3, 21, 78, 257, 594}};

data2 = Most[ArrayRules[SparseArray[data[[2 ;;, 2 ;;]], Automatic, Null]]];
data2 = data2 /. {r_Integer, c_Integer} :> {data[[2 ;;, 1]][[r]], data[[1]][[c]]};
Dataset[Flatten@*List @@@ data2]

enter image description here

$\endgroup$
1
$\begingroup$

Also

columns = Join @@ Join[Outer[List,m[[2;;,1]], m[[1]]], List /@ # & /@ m[[2;;, 2;;]], 3]
Dataset[Association[{"rowLabel" -> #, "colLabel" -> #2, "value" -> #3}]& @@@ columns]

enter image description here

$\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.