Re: Data format AFTER import into Mathematica from Excel.xls
- To: mathgroup at smc.vnet.net
- Subject: [mg131968] Re: Data format AFTER import into Mathematica from Excel.xls
- From: Alexei Boulbitch <Alexei.Boulbitch at iee.lu>
- Date: Thu, 7 Nov 2013 00:17:37 -0500 (EST)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
- Delivered-to: l-mathgroup@wolfram.com
- Delivered-to: mathgroup-outx@smc.vnet.net
- Delivered-to: mathgroup-newsendx@smc.vnet.net
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 at iee.lu