# 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

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

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

• Have you looked at Dataset and AssociationThread in the documentation. Can you post the code you've tried thus far. – Edmund Nov 29 '15 at 12:50
• Also, it would help if you posted a bit of example data instead of an image. Either make an Excel file with some representative example data (i.e. have unique row labels) and post the results of the import. – Edmund Nov 29 '15 at 14:35
• data1 = Drop[ Join@Import[ "C:\\Users\\Andrea\\Desktop\\three_region_60_sector_iot.xls", \ {"Sheets", "iot"}], {1, 2}, {1, 2}] // TableForm (whit this command i've imported the data without the headings) , the i've used alfa = Import[ "C:\\Users\\Andrea\\Desktop\\three_region_60_sector_iot.xls", \ {"Sheets", "iot"}] and then headers = Join@Take[alfa, {3, 180}, {1, 2}] (so i've taken the headings )....in the end i've tried io = Association@Thread[Rule[headers, data1]]....but in this case ii doesn't work as i would – Andrea Villabruna Nov 29 '15 at 14:58
• Edit the question and post the output from alfa[[ 1 ;; 10 , 1 ;; 10 ]]. This will give a starting point for people so that they don't have to create a dataset to work on. If the data is sensitive then make up a dummy set of data in Excel, import it and post the result. – Edmund Nov 29 '15 at 15:33
• sorry...i'm having problems about posting what i need because i'm not an expert user – Andrea Villabruna Nov 29 '15 at 16:27

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:

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"}}*)

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["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 =
rowNames -> (AssociationThread[colNames -> #1] &) /@ data]]
• Sorry if I've created in you these problems..i will try your method and then i'll tell you if it works. The issue about the same name of rows and columns it is because this is a matrix called Input-Output table ..it needs double entries – Andrea Villabruna Nov 30 '15 at 8:12
• No..it doesn't work – Andrea Villabruna Nov 30 '15 at 9:08
• Firstable...which kind of import did you do? – Andrea Villabruna Nov 30 '15 at 9:18
• I' ve make a try with your example , but it is wrong – Andrea Villabruna Nov 30 '15 at 11:56
• @andrea See update. You made an error by using code meant for single rows of names and applying that unmodified to double rows of names. – Sjoerd C. de Vries Nov 30 '15 at 15:21