6
$\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$
6
  • 1
    $\begingroup$ have you tried anything? $\endgroup$
    – Fraccalo
    May 21, 2018 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, 2018 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$ May 23, 2018 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, 2018 at 13:41
  • $\begingroup$ @Fraccalo It is fine, the main message of my previous comment is that this should be simpler... $\endgroup$ May 23, 2018 at 14:12

3 Answers 3

5
$\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$
4
$\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$
0
2
$\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 and acknowledge you have read our privacy policy.

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