Drexel dragonThe Math ForumDonate to the Math Forum



Search All of the Math Forum:

Views expressed in these public forums are not endorsed by Drexel University or The Math Forum.


Math Forum » Discussions » Software » comp.soft-sys.math.mathematica

Topic: Data format AFTER import into Mathematica from Excel.xls worksheet
Replies: 1   Last Post: Nov 7, 2013 12:21 AM

Advanced Search

Back to Topic List Back to Topic List Jump to Tree View Jump to Tree View  
Nicholas Kormanik

Posts: 15
Registered: 12/8/09
Re: Data format AFTER import into Mathematica from Excel.xls worksheet
Posted: Nov 7, 2013 12:21 AM
  Click to see the message monospaced in plain text Plain Text   Click to reply to this topic Reply


Very clear explanation, Alexei. Thank you so much for your effort.

Nicholas Kormanik


-----Original Message-----
From: Alexei Boulbitch [mailto:Alexei.Boulbitch@iee.lu]
Sent: Wednesday, November 06, 2013 2:14 AM
Cc: nkormanik@gmail.com
Subject: Re: Data format AFTER import into Mathematica from Excel.xls
worksheet

I have a large Excel worksheet (200 columns, 4,000 rows). Every column has
a unique and carefully chosen title/label.

I'm attempting to use it within Mathematica to create a large number of
contour plots (1,000) -- using three columns at a time, for x, y, z.

The import, I suppose, occurs. At least it appears to have.

Here's the question:

-- Is Mathematica using my column titles, so that I can refer to them in
commands subsequent to import?

-- If not, is there any format I can put my data into to begin with so that
Mathematica will readily recognize my named columns?


I certainly wanted to be getting on with the contour plots
(SmoothDensityHistogram), but am having trouble getting my data into the
required form.

Any help appreciated.

Nicholas Kormanik
nkormanik at gmail.com

Hi, Nicholas,

The answer to your question is - yes. Mathematica imports the Excel file as
it is with all its numeric and textual elements. In the result you get a
nested list. I just prepared a small rectangular Excel table for the example
purposes with the first line consisting of the column headings: "Time", "
StartTemperature", "Temperature", "Density", " Number". The rest elements
are something else including numerical data. I just took a part of my old
table with results. That is, I guess, what you need, but much smaller. I
cannot, unfortunately show the excel file file here, since the MathGroup
only accepts plain text messages.

Now, my notebook is in the same directory.
This imports the Excel file called "example.xls":

lst = Import[NotebookDirectory[] <> "example.xls"] // First

The result is the list, and it is shown here:

{{"Time", " StartTemperature", "Temperature", "Density",
" Number"}, {"18:21:35", "40,000", "39,995", "---",
"1"}, {"11:07:13", "40,000", "39,999", "1,006553",
"2"}, {"11:08:44", "40,000", "40,000", "1,006638",
"3"}, {"11:09:02", "40,000", "39,999", "1,006659",
"4"}, {"11:44:21", "40,000", "39,999", "1,007191",
"1"}, {"11:45:51", "40,000", "39,999", "1,007164",
"2"}, {"11:52:34", "Out of range", "Out of range", "Out of range",
"1"}}

If there is a large list to import there are several possibility to check,
if you have, indeed, imported it or not:

a) Just check this:
Length[lst]

8
If you get anything over 0 you have, indeed, imported something.

b)Then check this:

lst[[1]]
lst // Last

{"Time", " StartTemperature", "Temperature", "Density", " Number"}

{"11:52:34", "Out of range", "Out of range", "Out of range", "1"}

You will see the first and the last elements of the list. If, say, the first
element is too long or nested or both, check this:
lst[[1, 1]]

"Time"
And so on, until you get the vision of what did you import.

c) For very long lists one can always use the short form:

Short[lst]

Here it is senseless, though, since this list is already short.


OK, so we have got the list, its first line being the line is the column
headings. You can address it as follows:


lst[[1]]

{"Time", " Set Temp.", "Temperature", "Density", " Number"}

You might wish to somehow format it. You may do it as follows:

MapAt[Style[#, 16, Bold, Blue] &, lst, {1}]

This returns the same list but the headings line will be formatted according
to your wish. Try it.

To view it in a form of a table you may use, say, Grid, or TableForm. But
this is already another subject to be discussed separately, if needed.


Now, to address the individual columns by their names I can offer a simple
function as follows:

getColumn[lst_List, columnName_String] := Module[{pos},
pos = Position[Transpose[lst], columnName][[1, 1]];
Transpose[lst][[pos]]
]
Its first argument is the list in question, and the second one is the string
- the name of the column:

getColumn[lst, "Temperature"]

{"Temperature", "39,995", "39,999", "40,000", "39,999", "39,999", \
"39,999", "Out of range"}

Have fun, Alexei


Alexei BOULBITCH, Dr., habil.
IEE S.A.
ZAE Weiergewan,
11, rue Edmond Reuter,
L-5326 Contern, LUXEMBOURG

Office phone : +352-2454-2566
Office fax: +352-2454-3566
mobile phone: +49 151 52 40 66 44

e-mail: alexei.boulbitch@iee.lu






Point your RSS reader here for a feed of the latest messages in this topic.

[Privacy Policy] [Terms of Use]

© Drexel University 1994-2014. All Rights Reserved.
The Math Forum is a research and educational enterprise of the Drexel University School of Education.