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.
Add a comment
|
2 Answers
$\begingroup$
$\endgroup$
12
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]];
Grid[importeddata]
ListPlot[importeddata[[2 ;;, {1, 3}]] , Joined -> True,
PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {3}]]]]
ListPlot[importeddata[[2 ;;, {1, #}]] & /@ {2, 3, 4, 5}, Joined -> True,
PlotMarkers -> Automatic, PlotLegends -> LineLegend[importeddata[[1, {2, 3, 4, 5}]]]]
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}]
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]
-
2$\begingroup$ There are several simplifications that can be had. Don't bother with
LineLegend, it's the default, usedata[[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 specifyPlotLegendsat all. :) $\endgroup$– rcollyerCommented Mar 16, 2017 at 13:38 -
1$\begingroup$ @rcollyer, thank you. In version 9, using
data[[1, cols]]without theLineLegendwrapper gives only the markers without the lines in legend. $\endgroup$– kglrCommented Mar 17, 2017 at 8:07 -
1$\begingroup$ In v9, it looks like it isn't picking up the
Joined -> Truewhich it does in later versions, but you can swap inListLinePlotand the default legend isLineLegend. So, the list form would work. Also, if you are using v9, neitherAssociations nor the operator form ofMapare present. So,transformFwon't work unless you have v10. $\endgroup$– rcollyerCommented Mar 17, 2017 at 12:23 -
1$\begingroup$
Joinedwas formerlyPlotJoinedin earlier versions $\endgroup$ Commented Mar 17, 2017 at 14:33 -
1$\begingroup$ @george2079 in v9, where
PlotLegendswas implemented,PlotJoinedis no longer listed among the options forListPlotnor does it work. Although, it was still listed as a symbol. $\endgroup$– rcollyerCommented Mar 17, 2017 at 19:47
$\begingroup$
$\endgroup$
1
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 ;;]]]
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
answered Mar 16, 2017 at 14:30
-
$\begingroup$ Wao! you guys are excellent. @kglr, george2079 and rcollyer, thank you. $\endgroup$– DeanCommented Mar 17, 2017 at 13:17