4
$\begingroup$

I have imported a matrix from a .txt file (it could be better from an Excel file):

 {{"", "", "EE", "EE", "EE", "EE", "EE"}, {"", "", "Agriculture", 
  "Forestry", "Fishing", "Mining ", "Extraction "}, {"EE", 
  "Agriculture", "0,131346361", "0,01587348", "0,003638642", 
  "8,36E-004", "1,67E-004"}, {"EE", "Forestry", "5,52E-004", 
  "0,155865778", "4,38E-005", "6,00E-004", "4,00E-005"}, {"EE", 
  "Fishing", "2,33E-004", "4,04E-005", "0,024580636", "4,60E-006", 
  "3,76E-006"}, {"EE", "Mining ", "5,71E-004", "2,78E-004", 
  "5,12E-005", "0,034537603", "3,69E-005"}, {"EE", "Extraction ", 
  "3,67E-004", "2,51E-004", "7,46E-004", "0,001576707", 
  "0,043538054"}}

I'll show you the excel file too:

enter image description here

Ideally I would like to maintain the same layout after I've imported the file in Mathematica, so that I can recall a specific number, or a specific row, or a specific sector, or a specific region. I don't know if it is possible to maintain the double headings (EE is an abbreviation for Europe)

$\endgroup$
5
  • $\begingroup$ Wrap your matrix in TableForm to see it. Extract parts using Part. $\endgroup$
    – bill s
    Nov 30 '15 at 14:40
  • $\begingroup$ This works writing the headings too? ( to find what i need) $\endgroup$ Nov 30 '15 at 14:43
  • $\begingroup$ If your matrix is called list, then list[[1,5]] is "EE", list[[2,5]] is "Fishing". $\endgroup$
    – bill s
    Nov 30 '15 at 14:45
  • $\begingroup$ ok..yes.. what you're saying is right, i was not clear..sorry..i have to find a way so if i write [EE,Agriculture] i can get the information i need (the entire row i.e)...(this because i just show you an example because i will work on matrix with hundreds of row and columns) $\endgroup$ Nov 30 '15 at 14:48
  • $\begingroup$ @AndreaVillabruna, unfortunately, the repeated "EE" are not primary keys by themselves, otherwise can structure your table into a nested Association table like this: mathematica.stackexchange.com/questions/57189/… $\endgroup$ Nov 30 '15 at 16:35
2
$\begingroup$

Try this:

lst = {{"", "", "EE", "EE", "EE", "EE", "EE"}, {"", "", "Agriculture",
     "Forestry", "Fishing", "Mining ", "Extraction "}, {"EE", 
    "Agriculture", "0,131346361", "0,01587348", "0,003638642", 
    "8,36E-004", "1,67E-004"}, {"EE", "Forestry", "5,52E-004", 
    "0,155865778", "4,38E-005", "6,00E-004", "4,00E-005"}, {"EE", 
    "Fishing", "2,33E-004", "4,04E-005", "0,024580636", "4,60E-006", 
    "3,76E-006"}, {"EE", "Mining ", "5,71E-004", "2,78E-004", 
    "5,12E-005", "0,034537603", "3,69E-005"}, {"EE", "Extraction ", 
    "3,67E-004", "2,51E-004", "7,46E-004", "0,001576707", 
    "0,043538054"}};
Grid[lst, Frame -> All, Dividers -> Directive[Gray, Thickness[4]], 
 ItemSize -> {Automatic, 2}, 
 Background -> {None, {LightYellow, {LightBlue, LightOrange}}, 
   1 -> LightYellow}]

returning this:

enter image description here

or this, if you want to have the Excel view:

 lst1 = {{"A", "B", "C", "D", "E", "F", "G"}, {1, "", "", "EE", "EE", 
    "EE", "EE", "EE"}, {2, "", "", "Agriculture", "Forestry", 
    "Fishing", "Mining ", "Extraction "}, {3, "EE", "Agriculture", 
    "0,131346361", "0,01587348", "0,003638642", "8,36E-004", 
    "1,67E-004"}, {4, "EE", "Forestry", "5,52E-004", "0,155865778", 
    "4,38E-005", "6,00E-004", "4,00E-005"}, {5, "EE", "Fishing", 
    "2,33E-004", "4,04E-005", "0,024580636", "4,60E-006", 
    "3,76E-006"}, {6, "EE", "Mining ", "5,71E-004", "2,78E-004", 
    "5,12E-005", "0,034537603", "3,69E-005"}, {7, "EE", "Extraction ",
     "3,67E-004", "2,51E-004", "7,46E-004", "0,001576707", 
    "0,043538054"}};
Grid[lst1, Frame -> All, Dividers -> Directive[Gray, Thickness[4]], 
 ItemSize -> {Automatic, 2}, 
 Background -> {None, {LightYellow, {LightBlue, LightOrange}}, 
   1 -> LightYellow}]

yielding this:

enter image description here

Have fun!

Later edit: To address your question in the comment. It is possible to extract rows or columns corresponding different items, such as "Agriculture" or "Italy". Observing that such item as "Agriculture" is met more than once, I suggest that you do it in a semi-automatic regime, that is, you chose quasi-manually which of them you pick up. Here is how you might do that:

Position[lst, "Agriculture"]

returns (* {{2, 3}, {3, 2}} *) corresponding to two different positions of the item "Agriculture". Let us take first of them and pick up the row:

    lst[[2, All]]

(*   {"", "", "Agriculture", "Forestry", "Fishing", "Mining ", "Extraction \
"}   *)

Now let us take for the same item the column:

lst[[All, 3]]

(*  {"EE", "Agriculture", "0,131346361", "5,52E-004", "2,33E-004", \
"5,71E-004", "3,67E-004"}   *)

Have fun still!

$\endgroup$
4
  • $\begingroup$ can i recall a specific data just writing for example "Agriculture"? $\endgroup$ Nov 30 '15 at 16:04
  • $\begingroup$ @Andrea Villabruna What do you mean by "recall a specific data"? If you have in mind to replace ´"Agriculture" ` everywhere by, say, {1,2,3}, it's easy. Make lst/."Agriculture"->{1,2,3}. Done. $\endgroup$ Dec 1 '15 at 8:32
  • $\begingroup$ Sure..if i've imported a matrix whit thousend of rows and columns i would like to have the possibilty to extract a row or a specific data. For example if i want to extract a specific sector or a specific country and i don't know where they are i would like to write "Agriculture" or "Italy" or "Italy-Agriculture" and get the informations i need (and then maybe modify them) @Alexei Boulbitch $\endgroup$ Dec 1 '15 at 9:28
  • $\begingroup$ @Andrea Villabruna Have a look at the later edit to my answer. $\endgroup$ Dec 1 '15 at 9:50
2
$\begingroup$

Alexei's answer is great, but I think you could make it easier to grab a column by the label, creating a custom function to do this.

Firstly though, perhaps you want to import the data as numbers, so that you can perform operations on them. I notice that all the elements after the third row and the third column are in a numeric format where the comma is for the decimal place and E is used to denote the power of ten. This will convert them to a numeric format,

lst[[3 ;;, 3 ;;]] = 
  Map[ToExpression[StringReplace[#, {"," -> ".", "E" -> "*^"}]] &, 
   lst[[3 ;;, 3 ;;]], {2}];

Then you can look at the table using Alexei's call to Grid

Grid[lst, Frame -> All, Dividers -> Directive[Gray, Thickness[4]], 
 ItemSize -> {Automatic, 2}, 
 Background -> {None, {LightYellow, {LightBlue, LightOrange}}, 
   1 -> LightYellow}]

enter image description here

Now here are functions that extract rows or columns based on the label,

extractRow[label_] := Extract[lst, First@Position[lst[[2]], label]];
extractColumn[label_] := 
 Extract[lst, {All, First@First@Position[lst[[All, 2]], label]}];

Edit: The above code works for me, but does not work for OP - perhaps a bug in version 10.0 Windows? So the functions can be rewritten to use Part instead of Extract

extractRow[label_] := lst[[First[First[Position[lst[[2]], label]]]]];
extractColumn[label_] := 
  lst[[All, First[First[Position[lst[[All, 2]], label]]]]];

These work like this,

extractRow["Fishing"]
extractColumn["Fishing"]
(* {"EE", "Fishing", 0.000233, 0.0000404, 0.0245806, 4.6*10^-6, 3.76*10^-6} *)
(* {"EE", "Fishing", 0.00363864, 0.0000438, 0.0245806, 0.0000512, 0.000746} *)
$\endgroup$
9

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.