# Import specific data from Excel to make a plot

I need some help with this: I have imported data from an Excel file and I need to make a contour plot of it. The data are in table-like form. Here is a sample of the first few 'rows':

{{{"",  0.4,      0.5,      0.6,     0.7,     0.8,     0.9,     1.},
{0.3, 0.00122,  0.00182,  0.00247, 0.00311, 0.00382, 0.00453, 0.00524},
{0.4, 0.000949, 0.00149,  0.0021,  0.00272, 0.00337, 0.00406, 0.00477},
{0.5, 0.000714, 0.00123,  0.00176, 0.00233, 0.00295, 0.00361, 0.00425},
{0.6, 0.000519, 0.000982, 0.00147, 0.002,   0.00257, 0.00317, 0.00382},
...}}


In the first row I have values for one metric in each cell, let’s say from 0.2, 0.3,…,1. In the first column I have values for the second metric, with values 0.3,0.4,….1.

The upper corner is blank, if we consider it as table.

I need triplets of numbers created like this: I take a value from the first/main row, a value from the first/main column, and the value at their intersection. So the first triplet would be {0.4, 0.3, 0.00122}...

If I insert the data manually, I get exactly what I want with ListContourPlot[{x1,y1,f1},{x2,y2,f2},...]. But it takes a lot of time and seems rather nonsense!

At a second level, I would like to transpose the axes and make a second contour plot. So the first triplet would now be {0.3, 0.4, 0.00122} etc.

Since I’m new to Mathematica and with no knowledge of programming I am not sure how to get what I want. Some attempts I made were in vain.

-
Hi Nik. Have you managed to import your data from Excel yet? If you have, you should add a few rows of the resulting list to your question, then specify which elements you want to be plotted – cormullion Jun 1 '13 at 17:07
Unfortunately not. The point is that my "low reputation" won't let me upload a picture to make my question more specific. If anyone is willing, I will gladly send a personal mail with an excel and a mathematica file attached... – Nik Gkav Jun 1 '13 at 20:30
You should be able to upload code/error messages tho..? – cormullion Jun 1 '13 at 20:35
@cormullion: Let's assume I keep only the data I need for the plot, erase everything else and Import the remaining in mathematica. What I get with Import looks like this: {{{"", 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.}, {0.3, 0.00122, 0.00182, 0.00247, 0.00311, 0.00382, 0.00453, 0.00524}, {0.4, 0.000949, 0.00149, 0.0021, 0.00272, 0.00337, 0.00406, 0.00477}, {0.5, 0.000714, 0.00123, 0.00176, 0.00233, 0.00295, 0.00361, 0.00425}, {0.6, 0.000519, 0.000982, 0.00147, 0.002, 0.00257, 0.00317, 0.00382}, ... }}. So how I take the triplets next? – Nik Gkav Jun 1 '13 at 23:17

I was going to propose this, which is similar to Mr.Wizard's answer, but using a simple table rather than rules. I've already removed the outer braces from data:

dimensions = Dimensions[data]


{5, 8}

triplets = Flatten[
Table[
{
data[[1, column]],
data[[row, 1]],
data[[row, column]]
},
{row, 2, First[dimensions]},
{column, 2, Last[dimensions]}] , 1];

ListContourPlot[triplets, ColorFunction -> "SolarColors"]


-
Works for me. +1 – Mr.Wizard Jun 2 '13 at 8:20

Since the x and y values are evenly spaced, I will mention that another option is to give ListContourPlot just the data array rather than a list of triplets, and use the DataRange option to specify the ranges of the axes.

Starting from Mr Wizard's row, col and tbl2:

ListContourPlot[tbl2, DataRange -> {row[[{1, -1}]], col[[{1, -1}]]}]


-
@ Simon Woods:Thanks, too, for your answer! Equally excellent! What does {row[[{1, -1}]], col[[{1, -1}]]} performs? (begginner's ignorance). The same request for you how: can I change the order of triplets, or equivalently, interchange the axes in the final plot? – Nik Gkav Jun 2 '13 at 18:45
@NikGkav, row[[{1,-1}]] gives a list containing the first and last elements of row. It's the same as {First[row], Last[row]}. To interchange the axes you will need to transpose the data array and also swap row for col in the DataRange, so it would be ListContourPlot[Transpose[tbl2], DataRange -> {col[[{1, -1}]], row[[{1, -1}]]}] – Simon Woods Jun 2 '13 at 19:21
@ Simon Woods: thanks a lot for the clarification and answer as a whole! – Nik Gkav Jun 2 '13 at 21:53
Somewhat shorter: ListContourPlot[tbl2, DataRange -> {row, col}[[All, {1, -1}]]] (BTW, nice answer.) – Mr.Wizard Jun 6 '13 at 12:11
@Mr.Wizard, nice one, shorter and I think more readable too. – Simon Woods Jun 6 '13 at 13:23

Starting with your data as given:

tbl = {{{"", 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.}, {0.3, 0.00122, 0.00182, 0.00247, 0.00311,
0.00382, 0.00453, 0.00524}, {0.4, 0.000949, 0.00149, 0.0021, 0.00272, 0.00337,
0.00406, 0.00477}, {0.5, 0.000714, 0.00123, 0.00176, 0.00233, 0.00295, 0.00361,
0.00425}, {0.6, 0.000519, 0.000982, 0.00147, 0.002, 0.00257, 0.00317, 0.00382}}};


We might first strip the outer level of brackets, then extract three sections to work with:

tbl = First @ tbl;

row = tbl[[1, 2 ;;]]
col = tbl[[2 ;;, 1]]
tbl2 = tbl[[2 ;;, 2 ;;]]

{0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.}

{0.3, 0.4, 0.5, 0.6}

{{0.00122, 0.00182, 0.00247, 0.00311, 0.00382, 0.00453, 0.00524},
{0.000949, 0.00149, 0.0021, 0.00272, 0.00337, 0.00406, 0.00477},
{0.000714, 0.00123, 0.00176, 0.00233, 0.00295, 0.00361, 0.00425},
{0.000519, 0.000982, 0.00147, 0.002, 0.00257, 0.00317, 0.00382}}


Now we can get your triplets with any one of these lines:

dat = Most @ ArrayRules @ tbl2 /. ({c_, r_} -> val_) :> {row[[r]], col[[c]], val}

dat = Join @@ MapIndexed[{row[[Last @ #2]], col[[First @ #2]], #} &, tbl2, {2}]

dat = {row[[Last @ #]], col[[First @ #]], #2} & @@@ Most @ ArrayRules @ tbl2

dat = {#[[2]], #[[1]], #2} & @@@ Thread[{Tuples @ {col, row}, Join @@ tbl2}]

dat = Join @@ Array[{row[[#2]], col[[#]], tbl2[[##]]} &, Dimensions @ tbl2]

(* from Simon Woods; I wish I'd thought of this! *)
dat = Join[Tuples@{row, col}, {tbl2} ~Flatten~ {3, 2}, 2]


And plot them:

ListContourPlot[dat]


Or in one expression (after tbl = First @ tbl;):

ListContourPlot[
{tbl[[1, 1 + Last@#]], tbl[[1 + First@#, 1]], #2} & @@@ Most@ArrayRules@tbl[[2 ;;, 2 ;;]]
]


And here cormullion's method in my terse style:

ListContourPlot[
Array[
{tbl[[1, #2]], tbl[[#, 1]], tbl[[##]]} &,
Dimensions[tbl] - 1, 2
] ~Flatten~ 1
]

-
+1 A good introduction to the power of Mathematica for the OP! :) – cormullion Jun 2 '13 at 8:19
@cormullion Thanks. I added a third flavor; hopefully he likes one of them. Yours is very nice, and probably easier to understand for someone coming from a different language. – Mr.Wizard Jun 2 '13 at 8:37
I wonder how long ago it was since you were a beginning wizard (sorcerer's apprentice) - you're scarily good sometimes...:) – cormullion Jun 2 '13 at 8:48
@cormullion It's been over twelve years since my introduction, but it seems like a lot less. – Mr.Wizard Jun 2 '13 at 11:57
Also dat = Join[Tuples@{row, col}, {tbl2}~Flatten~{{3, 2}}, 2] – Simon Woods Jun 6 '13 at 13:54