6
$\begingroup$

enter image description here

 {{"", "", "ITA", "ITA", "ITA", "EU", "EU", "EU", "RDM", "RDM", "RDM", 
  "ITA", "ITA", "EU", "EU", "RDM", "RDM", ""}, {"", "", "agri", "ind",
   "serv", "agri", "ind", "serv", "agri", "ind", "serv", "cons", 
  "inv", "cons", "inv", "cons", "inv", "tot"}, {"ITA", "agri", 3, 2, 
  4, 10, 15, 1, 1, 5, 2, 20, 0, 16, 0, 10, 0, 89}, {"ITA", "ind", 24, 
  75, 55, 4, 55, 20, 1, 40, 15, 50, 20, 10, 12, 40, 10, 431}, {"ITA", 
  "serv", 8, 47, 30, 1, 44, 32, 1, 25, 15, 68, 2, 20, 2, 12, 1, 
  308}, {"EU", "agri", 3, 1, 1, 120, 120, 35, 70, 105, 22, 38, 1, 379,
   12, 233, 5, 1145}, {"EU", "ind", 15, 55, 45, 225, 650, 166, 12, 
  410, 132, 95, 18, 790, 280, 383, 90, 3366}, {"EU", "serv", 1, 47, 
  25, 95, 374, 430, 120, 410, 340, 65, 12, 920, 15, 650, 10, 
  3514}, {"RDM", "agri", 1, 0, 1, 68, 22, 75, 350, 480, 120, 22, 0, 
  222, 8, 1450, 55, 2874}, {"RDM", "ind", 12, 27, 22, 255, 646, 170, 
  750, 1640, 470, 120, 32, 500, 400, 1450, 220, 6714}, {"RDM", "serv",
   0, 12, 12, 45, 285, 325, 322, 450, 450, 22, 1, 450, 10, 848, 34, 
  3266}, {"", "va", 22, 165, 113, 322, 1155, 2260, 1247, 3149, 1700, 
  "", "", "", "", "", "", 10133}, {"", "out", 89, 431, 308, 1145, 
  3366, 3514, 2874, 6714, 3266, 500, 86, 3307, 739, 5076, 425, ""}}

This is my starting point. I've imported the matrix. I'm trying to write a procedure to extract the matrix of "ITA" but I'm not able to create an association. I want to write ITA and get the 3x3 matrix with the respective labels and a 3x2 too. it is important to maintain the two labels.

I show you the result i need to reach (in mathematica) because i don't know if i've explained it well: enter image description here

$\endgroup$
  • $\begingroup$ It is unclear whether what you want is actually two tables, one $3 \times 3$ plus labels and one $3 \times 2$ plus labels, or one table, $3 \times 5$ plus labels. Do you actually require two sets of labels on each axis or would one set of labels that rendered wide for rows and as two lines for columns suffice? You have been relatively dismissive of the answers so far, but have not been clear in indicating their deficiencies. $\endgroup$ – Eric Towers Dec 13 '15 at 20:47
  • $\begingroup$ Sorry i Would be more clear. I want two tables, one 3×3 plus labels and one 3×2 plus labels, but i would like to know a procedure to get this tables recalling them just writing a specific key ( in this case as you see I would like to digit "ITA" and get first the matrix 3x3 with all data of ITA and the in a second moment the 3x2). I require two sets of labels $\endgroup$ – Andrea Villabruna Dec 14 '15 at 8:15
5
$\begingroup$

This Dataset based method allows use of the column and row names instead of column and row indices.

ds uses a list of two elements for the column and row names since they are in two parts. data is the data in the post.

ds = Dataset@
  Association[#[[1 ;; 2]] -> 
      AssociationThread[Transpose@data[[1 ;; 2, 3 ;;]], #[[3 ;;]]] & /@ 
    data[[3 ;;, All]]];

Now with ds selection can be made on the column and row names.

Normal@ds[
  KeySelect[First@# == "ITA" &], 
  KeySelect[First@# == "ITA" \[And] ContainsAny[{Last@#}, {"agri", "ind", "serv"}] &]
  ]

(*
<|
  {"ITA", "agri"} -> 
    <|{"ITA", "agri"} -> 3, {"ITA", "ind"} -> 2, {"ITA", "serv"} -> 4|>, 
  {"ITA", "ind"} -> 
    <|{"ITA", "agri"} -> 24, {"ITA", "ind"} -> 75, {"ITA", "serv"} -> 55|>,
  {"ITA", "serv"} -> 
    <|{"ITA", "agri"} -> 8, {"ITA", "ind"} ->  47, {"ITA", "serv"} -> 30|>
|>
*)

Similarly for

Normal@ds[
  KeySelect[First@# == "ITA" &], 
  KeySelect[First@# == "ITA" \[And] ContainsAny[{Last@#}, {"cons", "inv"}] &]
]

Hope this helps.

$\endgroup$
  • $\begingroup$ This solution works if we work with small database... i would like to find a way that works with hundreds or even more rows and columns $\endgroup$ – Andrea Villabruna Dec 13 '15 at 16:53
  • $\begingroup$ Use MemberQ instead of ContainsAny. That appears to speed it up some. $\endgroup$ – Edmund Dec 13 '15 at 17:14
  • $\begingroup$ if i use MemberQ, must I write all the labels by myself of it's automatic? $\endgroup$ – Andrea Villabruna Dec 13 '15 at 19:16
  • $\begingroup$ I'm not certain what it is you are asking. With MemberQ you would replace the existing ContainsAny with MemberQ[{"agri", "ind", "serv"}, Last@#]. If you want all of the "ITA" columns then just do the "ITA" search on the columns and not the \[And] part of the criteria. $\endgroup$ – Edmund Dec 13 '15 at 19:31
  • $\begingroup$ ok, but if i have i.e. 1000 rows and 1000 columns it is impossible for me to write every headings by my self $\endgroup$ – Andrea Villabruna Dec 14 '15 at 8:17
3
$\begingroup$

I'm sure there's a better way to do this:

data = (* OP's data *);
GroupBy[Flatten[MapThread[Append,
                          {Outer[Riffle, Take[data, {3, -3}, 2], 
                                 Transpose[Take[data, 2, {3, -2}]], 1],
                           Take[data, {3, -3}, {3, -2}]}, 2], 1],
        {Take[#, 2] &, #[[3]] &, #[[4]] & -> Last}, Dataset[Map[Last, #, {2}]] &]
$\endgroup$
  • $\begingroup$ Thanks for it, but is not what i looking for $\endgroup$ – Andrea Villabruna Dec 13 '15 at 17:04
  • $\begingroup$ Well, it was intended as a starting point. Did you notice the first element of the output, for starters? $\endgroup$ – J. M.'s technical difficulties Dec 13 '15 at 17:08
  • $\begingroup$ sorry, i don't understand what do you mean. $\endgroup$ – Andrea Villabruna Dec 13 '15 at 19:14
  • $\begingroup$ I don't understand what you mean, either. Did you even look at the output after executing this? $\endgroup$ – J. M.'s technical difficulties Dec 13 '15 at 19:56

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.