how can i associate the headings of columns/rows with content of itself and then recall what i want after i've imported the excel file in mathematica

Question

Hello, i make another try... I have to import a file like this below (it is an excel file, i show you just the beginnig. it should have thousend of rows and columns). After i've imported the file i need to find a way to recall a specific number or a specific sector. For example if i want to know the value of Agricolture in EE region (EE is an abreviation for "Europe", it is not a costant).Until now i've tried to create an association with the headings and the content of the column/rows , but with any particular result. I hope i've been clear. Thanks

Update: I've imported a .txt 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"}}

Then i take the headings of the row csv[[All, 1 ;; 2]], do the same for the columns, and i've taken te datacsv[[3 ;;, 3 ;;]]

I try to create the association

md = AssociationThread[r1 -> (AssociationThread[c1 -> #] & /@ d)]  

and it works if i want to recall a row by writing the i,j position of the cell or writing the position of the row... but if i write the name of the Headings it doesn't work

Answer 1

Here the code for an example table (please provide copyable example data next time you post a question):

m= {{"", "", "EE", "EE", "EE", "EE", "EE"}, 
    {"", "", "Agriculture", "Forestry", "Fishing", "Mining", "Extraction"}, 
    {"EE", "Agriculture", 5.5646, 1.08922, 7.83459, 7.37088, 4.69533}, 
    {"EE", "Forestry", 7.02039, 0.839213, 4.81989, 6.74534, 3.58382}, 
    {"EE", "Fishing", 8.73866, 7.30894, 1.43181, 0.28529, 5.02924}, 
    {"EE", "Mining", 1.21729, 3.79903, 9.72889, 9.60559, 2.55934}, 
    {"EE", "Extraction", 7.13465, 5.61534, 4.93024, 0.765702, 4.18025}}

You have column and row names the same. That's rather confusing, but I'll leave it that way. I'm not sure what you want to do with the EE parts so I won't use them. Let's separate names and data:

colNames = m[[2, 3;;]];
rowNames = m[[3;;, 2]];
data = m[[3;;, 3;;]];

Now, we're going to make a Dataset with named columns and rows. We need an Association of Associations for that:

mds = AssociationThread[rowNames -> (AssociationThread[colNames -> #] & /@ data)]//Dataset

Extracting various parts from that (it's a Dataset, so we are allowed to use single brackets):

mds[1, 1]
(* 5.5646 *)

mds["Agriculture"]

mds[2 ;; 4, "Fishing"]

---

Update

It seems that you want to address the data with two names for both the rows and the columns. In that case you have to do it slightly different. You could either combine the name strings into a single "EE Agriculture" or use a list of those names {"EE", "Agriculture"}.

Approach 1:

We make a list of pairs of names:

colNames = m[[{1, 2}, 3 ;;]]\[Transpose]
rowNames = m[[3 ;;, {1, 2}]]
data = m[[3 ;;, 3 ;;]];

(*{{"EE", "Agriculture"}, {"EE", "Forestry"}, {"EE", "Fishing"}, {"EE", 
  "Mining"}, {"EE", "Extraction"}}

{{"EE", "Agriculture"}, {"EE", "Forestry"}, {"EE", "Fishing"}, {"EE", 
  "Mining"}, {"EE", "Extraction"}}*)

mds = AssociationThread[rowNames -> (AssociationThread[colNames -> #] & /@ data)]//Dataset;

Because we don't have strings as keys anymore, but instead pairs of strings, we now need Key as the reference:

 mds[Key[{"EE", "Agriculture"}], Key[{"EE", "Fishing"}]]
(* 7.83459 *)

mds[Key[{"EE", "Agriculture"}], 2 ;; 4] // Normal
(*<|{"EE", "Forestry"} -> 1.08922, {"EE", "Fishing"} -> .83459, 
  {"EE", "Mining"} -> 7.37088|>  *)

Approach 2 (combine strings):

colNames = m[[{1, 2}, 3 ;;]]\[Transpose]
rowNames = m[[3 ;;, {1, 2}]]
data = m[[3 ;;, 3 ;;]];

colNames = ToString[#1] <> " " <> ToString[#2] & @@@ colNames
rowNames = ToString[#1] <> " " <> ToString[#2] & @@@ rowNames;
(* {"EE Agriculture", "EE Forestry", "EE Fishing", "EE Mining", "EE Extraction"} *)

mds = AssociationThread[rowNames -> (AssociationThread[colNames -> #] & /@ data)]//Dataset

mds["EE Agriculture", "EE Fishing"]
(* 7.83459 *)

---

Update 2

Andrea: exactly copy and paste the code below. Do not change anything and check whether this works:

m = {{"", "", "EE", "EE", "EE", "EE", "EE"}, {"", "", "Agriculture", 
   "Forestry", "Fishing", "Mining", "Extraction"}, {"EE", 
   "Agriculture", 5.5646`, 1.08922`, 7.83459`, 7.37088`, 
   4.69533`}, {"EE", "Forestry", 7.02039`, 0.839213`, 4.81989`, 
   6.74534`, 3.58382`}, {"EE", "Fishing", 8.73866`, 7.30894`, 
   1.43181`, 0.28529`, 5.02924`}, {"EE", "Mining", 1.21729`, 3.79903`,
    9.72889`, 9.60559`, 2.55934`}, {"EE", "Extraction", 7.13465`, 
   5.61534`, 4.93024`, 0.765702`, 4.18025`}}; colNames = 
 Transpose[m[[{1, 2}, 3 ;; All]]]; rowNames = 
 m[[3 ;; All, {1, 2}]]; data = m[[3 ;; All, 3 ;; All]]; colNames = 
 Apply[ToString[#1] <> " " <> ToString[#2] &, 
  colNames, {1}]; rowNames = 
 Apply[ToString[#1] <> " " <> ToString[#2] &, rowNames, {1}]; mds = 
 Dataset[AssociationThread[
   rowNames -> (AssociationThread[colNames -> #1] &) /@ data]]