Import two variable data from excel

Question

I'm quite new to Mathematica. I've been looking for the information but couldn't find it. I have several sets of data corresponding to the exposure of photographic paper.

On the first row I have the printing range (contrast) of the paper, and should be the x axis. On the first column I have the Density (darknes of the paper), which is the result (z axis) and in between, the needed exposure needed to get the corresponding density which should be on the y axis. There are more columns but I have only included a few. I've been able to, manually insert the data of one column and get a polynomial fit for the curve. But I would like to import automatically all the data at once and get a 2 variable fit of the data. I know I can do it manually but it will be a lot of work since I have several data sets to include.

This is the code I use to, manually insert the data of each column:

data2 = {{1.02 - 1.48, 0.04}, {1.12 - 1.48, 0.04}, {1.22 - 1.48, 
0.04}, {1.42 - 1.48, 0.05}, {1.62 - 1.48, 0.12}, {1.78 - 1.48, 
0.27}, {1.98 - 1.48, 0.47},{2.19 - 1.48, 0.70}, {2.35 - 1.48, 1.00}, {2.52 - 1.48, 1.32}, {2.64 - 1.48, 1.60}, {2.77 - 1.48, 1.90}, {2.95 - 1.48, 2.1}, {3.05 - 1.48, 2.11}, {3.15 - 1.48, 2.11}, {3.25 - 1.48, 2.11}, {3.45 - 1.48, 2.11}, {3.65 - 1.48, 2.11}};
Polinomica := Fit[data2, {1, x, x^2, x^3, x^4, x^5, x^6, x^7, x^8, x^9, x^10,  x^11, x^12}, x];

I know it's not elegant but I'm in process of learning the syntax. Sorry.

Answer 1

Once you've imported the data (perhaps using Import["filename.xlsx"], or perhaps by exporting from Excel to csv and importing that), you might get a slightly strange-shaped data set, like

data = {{, 1.8, 1.6, 1.45}, {0.05, -0.23, -0.18, -0.14}, {0.12, 0.03, 0.04, 0.07}, {0.27, 0.21, 0.22, 0.25}};

I'd make a table out of this data like

tab = Catenate@Table[{data[[1, 2 ;;]][[x]], Transpose[data][[1, 2 ;;]][[y]], 
Transpose[data][[2 ;;, 2 ;;]][[x, y]]}, 
{x, Range@Length@data[[1, 2 ;;]]}, 
{y,Range@Length@Transpose[data][[1, 2 ;;]]}]

There probably are neater ways of writing this, but I think that this is nice and explicit, showing what's going on. Probably slower than alternatives if you've got a very large dataset.

Then fit via

fit = Fit[tab, {1, x, x^2, y, y^2}, {x, y}]

and plot as

Show[ListPointPlot3D[tab, PlotStyle -> PointSize[0.03]], 
Plot3D[fit, {x, Min@data[[1, 2 ;;]], Max@data[[1, 2 ;;]]}, 
{y, Min@Transpose[data][[1, 2 ;;]], Max@Transpose[data][[1, 2 ;;]]}]]

Answer 2

To read in an Excel File, e.g. with the following content:

you can do the following:

Step 1: get the file:

file = SystemDialogInput["FileOpen"];
in = Import[file, "XLSX"]

Step 2: identify row and column headers:

keys = First @ in[[1]]

and

values = Rest @ in[[1]]

Step 3: Make a Dataset

Dataset@(AssociationThread[keys -> #] & /@ values)

Result:

Answer 3

(*Import data from the first sheet*)
table = Import["Workbook.xlsx", {"Data", 1}]
(*Extract x and y vectors*)
x = table[[1, 2 ;;]];
y = table[[2 ;;, 1]];
(*Extract data for z*)
data = table[[2 ;;, 2 ;;]];
(*Make a list of {x,y,z} points*)
points = Flatten[MapThread[Append, {Outer[List, x, y], data}, 2], 1]

Answer 4

For importing the data use the syntax Import["your.xls file" {"Data", # of sheet(s), # of row(s), # of column(s)}. you can specify the numbers or All if you are interested in all stuff. that will already create for you a two dimensional dataset to work with.