4
$\begingroup$

i would like to know if there are any ways to link headings of a table i had imported with the data in the table itself. I imported a matrix from an excel file and i want to take a specific part of it. For example if in my table one row heads with "Italy" i want to know a procedure to extract in mathematica the content of the row (or the column or both of them) just writing (in mathematic) the name of the row (or the column)

Thanks! (sorry if i'had not explained it too much clearly)

$\endgroup$
4
$\begingroup$

Let's assume your excel sheet has three columns with headings Oct through Dez.

Import it

imp = Catenate @ Import["excel.xlsx"]

{{"Okt", "Nov", "Dez"}, {10., 11., 12.}, {1., 2., 3.}}

The Catenate is necessary to remove the outermost brackets.

Get header

head = imp[[1]]

{"Okt", "Nov", "Dez"}

Get values

vals = imp[[2 ;;]]

{{10., 11., 12.}, {1., 2., 3.}}

[[]] is a short form for Part and 2;; means: from the 2nd position until the end

Build an Association

data = Association@Thread[Rule[head, Transpose@vals]]

<|"Okt" -> {10., 1.}, "Nov" -> {11., 2.}, "Dez" -> {12., 3.}|>

You can now directly refer to your column names:

data["Nov"]

{11., 2.}

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thank you. Now i have to try it because i'm working with excel files with thousend of rows and columns. My real problem was to create an association with the heading of the row (for example) and the content of the row itself. So, when in mathematica i digit the name of the headings's row i can recall the row i need, like i sad if my row calls "Italy" (after i imported the file in mathematica), i digit "Italy" and i can get the informations, all the data in the row..e so.. $\endgroup$ – Andrea Villabruna Nov 27 '15 at 11:15
  • $\begingroup$ If your headings are the first items of the rows you would import with Transpose @ Catenate @ Import["excel.xlsx"]. The rest of my answer doesn't change. $\endgroup$ – eldo Nov 27 '15 at 11:23
  • $\begingroup$ the table i'm trying to import is an Input-Output table which has the headings i will associate with dataset in the first and second row , and in the first and second column $\endgroup$ – Andrea Villabruna Nov 27 '15 at 12:43
  • $\begingroup$ Could you show a small example of your data? Do you really need four different indexes? $\endgroup$ – eldo Nov 27 '15 at 13:08
  • $\begingroup$ [!file:///C:/Users/Andrea/Pictures/Immagine.png] sorry.i'm new in this forum ..I'm trying to understand how to put images...i don't know if it works..however it's necessary four different indexes because this is how this kind of table works $\endgroup$ – Andrea Villabruna Nov 27 '15 at 14:55
4
$\begingroup$

Looks like you are looking for the Dataset format. Using "ExampleData/elements.xls" to get an example .xls file:

ds = SemanticImport["ExampleData/elements.xls"]

enter image description here

Now

ds[1]

enter image description here

and

ds[All, "Name"]

enter image description here

| improve this answer | |
$\endgroup$
  • 1
    $\begingroup$ For this specific example using SemanticImport["ExampleData/elements.xls", {Integer, String, String, Real}] would result in a better formated Dataset. $\endgroup$ – Karsten 7. Nov 27 '15 at 16:40

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.