13
$\begingroup$

I have a large Excel .xls file. Miniature version shown below:

(formatting should appear as two columns...)

Test1   Test2
75        64
58        85
81        55
64        63
70        94
88        90
78        96
96        98
54        97
75        61

I would like to do some statistical analysis on the file in Mathematica 8.0.4.

Additionally I am using Mathematica Link for Excel 3.5.

The issue I'm now attempting to address concerns the "headers," or "column labels."

I need to tell Mathematica to disregard the headers. I want to use each respective header, however, as the name for the "variable stream" (Mathematica-speak for each column of data.)

What might be the best ways of dealing with such headers?

=====

Additional edit:

The "one-liner" version seems to work just fine. Thank you Vitaliy.

Follow-up Question:

If the Excel .xls spreadsheet being imported is rather large -- 200 columns, 5000 rows -- will that present a problem with the Mathematica "one-liner" approach Vitaliy has suggested? i.e., memory inadequacy, inability to make subsequent steps?

What I mean here is, is it completely okay to import an entire large Excel .xls all at once? Or is it far better to ONLY import "columns" as needed, that will immediately be used in subsequent Mathematica steps?

I'd prefer to import all at once -- using Vitaliy's method -- if that's totally acceptable.

$\endgroup$
4
  • 1
    $\begingroup$ Do you need to use Mma link for Excel? Is it not enough to read the files? $\endgroup$ Jul 25, 2012 at 3:21
  • 2
    $\begingroup$ You could Import the file and then Drop the first row that contains the headers. $\endgroup$ Jul 25, 2012 at 3:34
  • $\begingroup$ I am glad I could help. Could you please explain how is the last part of your question (additional edits 1,2,3) connected to the 1s part (especially, "each respective header, however, as the name for the "variable stream") ? You can import and plot your data easily without treating headers as variables. $\endgroup$ Jul 27, 2012 at 7:30
  • 1
    $\begingroup$ (2) and (3) are separate questions from this one - please post them as another question(s). You'll need to Transpose your data if you have imported it as described in this question so why not just Import it normally as data = Rest@Import["myfile.xls",{"Sheets","Data"}]? $\endgroup$
    – Verbeia
    Jul 27, 2012 at 7:46

3 Answers 3

13
$\begingroup$

If your Excel file test.xls is very simple:

enter image description here

Then the code is a one-liner (if I understand correctly what is needed):

Set@@@Transpose[{ToExpression[First[#]], Transpose[Rest[#]]}&@Import["test.xls"][[1]]]

To check:

 {Paris, Moscow}

{{1., 2., 3., 4., 5., 6., 7., 8.}, {12., 23., 34., 45., 56., 67., 78., 89.}}

The rest is more complex cases. Imagine that you have an test.xls file with some not-trivial text distribution inside:

enter image description here

Doing simply this will import the whole thing:

Import["test.xls"]

{{{"", "", "", ""}, {"", "Paris ", "Moscow", "New York"}, 
{"", 1., 12., 56.}, {"", 2., 23., 56.}, {"", 3., 34., 56.}, 
{"", "New", "Old", "Current"}, {"", 4., 45., 56.}, 
{"", 5., 56., 56.}, {"", 6., 67., 56.}, {"", 7., 78., 56.}}}

Because Excel mostly stores numbers as reals you can do the trick:

DeleteCases[Import["test.xls"], _String, Infinity]

{{{}, {}, {1., 12., 56.}, {2., 23., 56.}, {3., 34., 56.},{}, 
{4., 45., 56.}, {5., 56., 56.}, {6., 67., 56.}, {7., 78., 56.}}}

No text, but now you got some empty lists to remove:

DeleteCases[DeleteCases[Import["test.xlsx"], _String, Infinity], {}, Infinity]

{{{1., 12., 56.}, {2., 23., 56.}, {3., 34., 56.}, {4., 45., 56.}, 
{5., 56., 56.}, {6., 67., 56.}, {7., 78., 56.}}}

Now you done - go play with your data:

PieChart[% // First, ColorFunction -> "Rainbow"]

enter image description here

This should work pretty universally, because you can do all sorts of pattern and string filtering. But you need to be careful in general case, where uneven text distribution may produce ragged data arrays with unexpected structure.

Now, addressing @belisarius comment, if you want to use your headers as variables and asign to them your data, then something like this will do. You probably have then a simpler file with headers only:

enter image description here

This will do:

headers = DeleteCases[Cases[Import["test.xls"], _String, Infinity], ""] //ToExpression;

data = DeleteCases[DeleteCases[Import["test.xls"], _String, Infinity], {},Infinity] 
       // First // Transpose;

Set @@@ Transpose[{headers, data}];

Now let's check:

 {Paris, Moscow}

{{1., 2., 3., 4., 5., 6., 7., 8.}, {12., 23., 34., 45., 56., 67., 78., 89.}}

This will work even if you have some empty rows/columns around your .XLS table in Excel file.

$\endgroup$
5
  • $\begingroup$ I think you overlooked this part of the question . I want to use each respective header, however, as the name for the "variable stream" $\endgroup$ Jul 25, 2012 at 4:08
  • $\begingroup$ @belisarius Thanks, I updated the answer. $\endgroup$ Jul 25, 2012 at 5:03
  • $\begingroup$ Nice! +1 ... and seven more chars $\endgroup$ Jul 25, 2012 at 5:36
  • $\begingroup$ @VitaliyKaurov Thanks for your "one-liner." Seems to work well. Additional questions in post above.... $\endgroup$
    – nkormanik
    Jul 27, 2012 at 6:48
  • $\begingroup$ @belisarius Thanks for pointing out that I really needed the column headers. Hopefully I'll be able to use those after import as variable names (?) in subsequent commands. $\endgroup$
    – nkormanik
    Jul 27, 2012 at 6:50
10
$\begingroup$

If you know the number of header rows, you can always just Drop that number of rows as part of the code to import the data. For example, suppose your file is called "myfile.xls" and the worksheet is called "Data". Then your code would be:

data = Drop[Import["myfile.xls",{"Sheets","Data"}],1]

or using Part with Span (;;):

data = Import["myfile.xls",{"Sheets","Data"}][[2;;]]

Alternatively if you do not know the number of header rows in advance, you could use Cases to select only those rows with numerical data in them:

data =  Cases[Import["myfile.xls",{"Sheets","Data"}],{__?NumericQ} ]

Since you are using Mathematica link for Excel, you should be able to define the appropriate data range using the second example, but you might need to have a rawdata intermediate expression that reads the whole worksheet, and then do something like:

data = rawdata[[2;;]]

Not having Mathematica Link for Excel, I can't verify exactly how to address the Excel data, but this should be enough of a pointer to get you started.

To then assign the data to the label names given in the headers
Then forget about dropping or using Cases as shown above, because you will instead need to import the whole data set including the header rows, and Transpose it.

data = Transpose @ Import["mydata.xls", {"Sheets", "Data"}]

Then this will work:

MapThread[ Set[#1, #2] & , {ToExpression /@ (First /@ data), Rest /@ data}, 1]

Notice that you need to use the FullForm syntax of Set, rather than the shorthand #1 = Rest[#2], in this expression.

EDIT Per Mike Honeychurch's suggestion, if your header labels have non-alphanumeric characters, you should also use StringReplace to edit these out:

MapThread[ Set[#1, #2] &, {ToExpression /@ 
 StringReplace[(First /@ data), {" " -> "", "-" -> ""}], Rest /@ data}, 1]

You might need to add rules to the second argument of the StringReplace function, depending on your input.

$\endgroup$
3
  • $\begingroup$ As I understands it, he wants to assign the lists to symbols whose names are test1 and test2 $\endgroup$ Jul 25, 2012 at 3:41
  • $\begingroup$ Nice alternative to using Symbol $\endgroup$ Jul 25, 2012 at 3:59
  • 1
    $\begingroup$ I'd add a string replace step as well for the general case in which a column is named e.g. "test 2" $\endgroup$ Jul 25, 2012 at 4:13
5
$\begingroup$

I Think what you want is something like

(Evaluate[{Symbol@#[[1, 1]], Symbol@#[[1, 2]]}] = Transpose@##[[2 ;;]]) &@ 
                                                         Import["C:\\test1.xls"][[1]]);
{Test1, test2}

(*
{{1., 2., 3., 4., "", "", "", "", "", ""}, {2., 4., 5., 7., "", "", "", "", "", ""}}
*)

Which assigns the columns to Mathematica symbols whose names are those of the headers

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.