I have my data on an excel file (with five columns and 15 rows) and would like to import the file into mathematica and then: (a). Plot column 1 against column 3 (b). Plot column 1 against column 2, 3, 4 and 5 (multiple curves) However, I would like the legend labels to be picked directly form the column heading (y1, y2, y3, y4). I would appreciate comments and suggestions.


To create an excel file with 5 columns and 15 rows:

headers = ToString /@ {y1, y2, y3, y4, y5};
values = SortBy[RandomInteger[99, {15, 5}], First];
Export["example.xlsx", Join[{headers}, values]];

Import the excel file and take its first part:

importeddata = Import["example.xlsx"][[1]];

Mathematica graphics

ListPlot[importeddata[[2 ;;, {1, 3}]] , Joined -> True, 
   PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {3}]]]] 

Mathematica graphics

ListPlot[importeddata[[2 ;;, {1, #}]] & /@ {2, 3, 4, 5}, Joined -> True, 
   PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {2, 3, 4, 5}]]]] 

Mathematica graphics

Or define a function that takes as input a dataset and columns to plot:

lpF[data_, cols_] := ListPlot[data[[2 ;;, {1, #}]] & /@ cols, Joined -> True, 
   PlotMarkers -> Automatic, PlotLegends -> LineLegend[data[[1, cols]]]];

lpF[importeddata, {2, 3, 5}]

Mathematica graphics

Update: As noted by @rcollyer in the comments, in version 10 and later versions, you can transform your data using

transformF[data_, cols_]:=<|Map[#[[1, 2]]->#[[2 ;;]]&]@ Map[data[[All, {1, #}]] &]@cols|>;

and get the same result without having to use the option PlotLegends:

ListPlot[transformF[importeddata, {2,3,5}], Joined->True, PlotMarkers->Automatic]
|improve this answer|||||
  • 1
    $\begingroup$ There are several simplifications that can be had. Don't bother with LineLegend, it's the default, use data[[1, cols]] directly. Better yet, convert your data to this form <|Map[#[[1, 2]] -> #[[2 ;;]] &]@ Map[data[[All, {1, #}]] &]@cols|>, then you don't need to specify PlotLegends at all. :) $\endgroup$ – rcollyer Mar 16 '17 at 13:38
  • $\begingroup$ @rcollyer, thank you. In version 9, using data[[1, cols]] without the LineLegend wrapper gives only the markers without the lines in legend. $\endgroup$ – kglr Mar 17 '17 at 8:07
  • $\begingroup$ In v9, it looks like it isn't picking up the Joined -> True which it does in later versions, but you can swap in ListLinePlot and the default legend is LineLegend. So, the list form would work. Also, if you are using v9, neither Associations nor the operator form of Map are present. So, transformF won't work unless you have v10. $\endgroup$ – rcollyer Mar 17 '17 at 12:23
  • $\begingroup$ Joined was formerly PlotJoined in earlier versions $\endgroup$ – george2079 Mar 17 '17 at 14:33
  • $\begingroup$ @george2079 in v9, where PlotLegends was implemented, PlotJoined is no longer listed among the options for ListPlot nor does it work. Although, it was still listed as a symbol. $\endgroup$ – rcollyer Mar 17 '17 at 19:47

If I understand the question he has an "x" column and a bunch of "y" columns:

make an example:

header = {"x", "sin", "cos"};
x = Range[0, 2 Pi, 1/100];
values = Transpose@{x, Sin[x], Cos[x]};
Export["example.xlsx", Join[{headers}, values]];
Clear[header, x]

read it back:

{header, data} = Through[{First, Rest}@Import["example.xlsx"][[1]]];
{x, y} = Through[{First, Rest}@Transpose[data]];
ListPlot[Transpose[{x, #}] & /@ y, Joined -> True, 
 PlotLegends -> header[[2 ;;]]]

enter image description here

sometimes its convenient to actually copy the x vals and associate with each y column:

ydata = Transpose[{x, #}] & /@ y;
ynames = header[[2 ;;]];

then you can just do:

ListPlot[ydata, Joined -> True, PlotLegends -> ynames ]

same plot

|improve this answer|||||
  • $\begingroup$ Wao! you guys are excellent. @kglr, george2079 and rcollyer, thank you. $\endgroup$ – Dean Mar 17 '17 at 13:17

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.