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: Re: Data format AFTER import into Mathematica from Excel.xls
Replies: 0  

Advanced Search

Back to Topic List Back to Topic List  
Alexei Boulbitch

Posts: 479
Registered: 2/28/08
Re: Data format AFTER import into Mathematica from Excel.xls
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

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.