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$
2
  • $\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$ Dec 13, 2015 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$ Dec 14, 2015 at 8:15

2 Answers 2

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$
8
  • $\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$ Dec 13, 2015 at 16:53
  • $\begingroup$ Use MemberQ instead of ContainsAny. That appears to speed it up some. $\endgroup$
    – Edmund
    Dec 13, 2015 at 17:14
  • $\begingroup$ if i use MemberQ, must I write all the labels by myself of it's automatic? $\endgroup$ Dec 13, 2015 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, 2015 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$ Dec 14, 2015 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$
4
  • $\begingroup$ Thanks for it, but is not what i looking for $\endgroup$ Dec 13, 2015 at 17:04
  • $\begingroup$ Well, it was intended as a starting point. Did you notice the first element of the output, for starters? $\endgroup$ Dec 13, 2015 at 17:08
  • $\begingroup$ sorry, i don't understand what do you mean. $\endgroup$ Dec 13, 2015 at 19:14
  • $\begingroup$ I don't understand what you mean, either. Did you even look at the output after executing this? $\endgroup$ Dec 13, 2015 at 19:56

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.