I have an Excel spreadsheet (link to it and its image given) that contains data I want to graph three dimensionally.

Purple is for the x-axis values (wavelength). Blue is for the y-axis values (absorbance). And green is the z-axis values (the time).

The real data sets will be much larger (thousands of columns and rows).

I would like to know how to graph this data as a 3D plot. I convert the data into an Excel readable file and it spits it out in an Excel sheet just like I have shown. I am unsure how to pull the data as its given in Excel and then graph it. Each time (green) goes with the y-values below it. I have looked around and couldn't find anything about it, so excuse my ignorance if this has been asked. Thank you for your time. Again, sorry if this has been asked a lot, so anything helps!


2 Answers 2

data = Import["test_data.xlsx"][[1]];

lambda = data[[2 ;;, 1]];      (* First column = wavelength values   *)
time = data[[1, 2 ;;]];        (* First row = time                   *)
abs = data[[2 ;;, 2 ;;]];      (* The rest are z = absorbance values *)

triplets = MapIndexed[{lambda[[#2[[1]]]], time[[#2[[2]]]], #1}&, abs, {2}]~Flatten~1

(* Out:
 {{196.8, 0., 100000.}, {196.8, 20.94, 0.001}, {196.8, 42570., 0.001}, 
  {202.5, 362100., 0.001}, {202.5, 384100., 0.001}}

You can then use ListPlot3D, ListPointPlot3D, or alternatively ListContourPlot and ListDensityPlot to plot your data, depending on what you want to achieve. Note that the data you shared does not amount to much, since it's mostly close to zero throughout. Perhaps more varied data would make for a more interesting plot.

  • $\begingroup$ Will I use something like 'fit2 = {{380, 550}, {0, 3}}; j1 = ListPlot3D[Table[data[[All, {1, i}]], {i, 2, 185}], PlotRange -> fit2] As the plot? Maybe not because that's what I was using for a 2D plot of Absorbance vs. Wavelength (ListLinePlot instead of 3D one). $\endgroup$
    – Jack Gray
    Commented Jul 3, 2018 at 4:30
  • $\begingroup$ @JackGray No, I would expect you to do something like ListPlot3D[triplets]. $\endgroup$
    – MarcoB
    Commented Jul 3, 2018 at 4:35
  • $\begingroup$ I guess I am not following. I tried something similar I used to generate a plot using individual files, which is: rangex = {{0, 4292000}, {480, 850}, {0, 3}}; ListPointPlot3D[data2, PlotRange -> rangex] I guess I am not surprised that it doesn't work. The ranges are time (0 to 4292000), wavelength (480 to 850), and absorbance (0,3). $\endgroup$
    – Jack Gray
    Commented Jul 3, 2018 at 4:47
  • $\begingroup$ Thanks for your help in this. I am new to the computational side of chemistry, as I am currently working on quantum dot nucleation and growth (kinetic vs thermodynamic) using in-situ UV-vis. $\endgroup$
    – Jack Gray
    Commented Jul 3, 2018 at 5:06
  • $\begingroup$ @JackGray I’m afraid I’m the one who’s not following now. Are we still talking about the data you described in the excel file? Because your sample data in the comments above looks nothing like the one in the excel file in the original question. $\endgroup$
    – MarcoB
    Commented Jul 3, 2018 at 5:34

When you look at y values, there is some outlier

TakeLargest[pts[[All, 2]], 10]

{100000., 43280., 10000., 196.4, 5.346, 5.164, 5.122, 5.072, 4.967, 4.895}

So we can delete 2,5,6 and 9th rows from excel file and put some number to A1.

pts = Flatten[
   Thread[{data[[2 ;;, 1]], data[[2 ;;, # + 1]], 
       data[[1, # + 1]]}] & /@ Range[n - 1], 1];

ListPointPlot3D[pts, BoxRatios -> {1, 1, 1}, 
 PlotTheme -> "Scientific", 
 AxesLabel -> {"wavelength", "absorbance", "time"}]

enter image description here


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.