# Get .csv or .xlsx real data, some missing, into Mathematica and form it for curve fitting and 3D plotting

Problem: My data consists of Real values at Real $$x, y$$ positions. It is stored in the array-forms: comma-separated (type .csv) and spreadsheet (type .xlsx). As a Mathematica Dataset, it may be referred to as a "a table with named columns and named rows" and "association of associations". I use Windows 10; MacBook when on the road.

Questions: Using a (row,col) array sequence, e.g., if the $$x,y$$-positions run across, down, respectively, how do I: a) Import this data so that each value(x,y) is associated with $$x, y$$, for example: Value(2,3) is at position x(3), y(2)? I prefer doing this in two steps: Import then convert. Note: Import of .csv formatted data only accessed the first row whereas import of .xlsx accessed the entire file (see below). b) Use Fit, FindFit and List- or FunctionInterpolation to obtain values to replace those missing in my data? Also, how to denote values as missing (I cannot use zero)? c) Plot the resulting data in 3-D and as contours?

Attempts to solve the problem using the sample data set:

0, 1, 2, 3, 4
1, 11,12,13,14
2, 21,22,23,24
3, 31,32,33,34
4, 41,42,43,44


(the actual set is real and much larger), stored as shown and as an Excel .xlsx file follow.

Attempt 1. Import the data with

  Import["C:\\Users\\njs\\Documents\\Computing\\Software\\Mathematica\\


sampleData3.xlsx"]

The result is an apparently correct list of lists, but TableForm displays the data transposed which I don't understand. Aside: When typing a comment below this transposed result, values in the table were appended with super fix 'grave' symbols. Why? What do they mean?

Attempt 2. Import and flatten it using a Data statement:

    myData = Flatten[
MapIndexed[{Sequence @@ #2, #1} &,
Import["/Users/nick/Documents/Mathematica/sampleData3.xlsx",
{"Data", 1, {2, 3, 4, 5}, {2, 3, 4, 5}}
], (* <-- close Import. NOTE: {"Data", 1, {2;;5},{2;;5} did not   work *)
{2}],  (* <-- Close MapIndexed *)
1]  (* <-- Close Flatten *)


This gives a good result, but I cannot explicitly list all the rows and columns in the much larger actual data set. How to list to end of rows, columns?

Some queer results: In a .nb file, when typing a comment below the Imported output, a red square (akin to a bullet) appeared at the head of the comment. What does the red square (bullet) mean? How did it occur? How to be rid of it? The 'grave' symbols were mentioned above.

Key References:

Import Excel sheet into 3D array? <-- Use Flatten w/ Sequence to get (x,y,f) values

Other References: https://reference.wolfram.com/language/ref/ListInterpolation.html
https://reference.wolfram.com/language/ref/Dataset.html <-- > Details => 3D plot https://reference.wolfram.com/language/ref/FunctionInterpolation.html <-- 1D only

Edited based on the comments exchanged below.

and that this means that at $$x=2$$, $$y=4$$ the $$z$$ value is 34. To enter these results in mathematica:

 data = Flatten[Import["sampleData3.xlsx", "Data"], 1];


Next construct the {x,y,z} triplets:

xyz = Flatten[Table[{i - 1, j - 1, data[[i, j]]}, {i, 1, 5}, {j, 1, 5}], 1]


This gives:

{{0, 0, 0.}, {0, 1, 1.}, {0, 2, 2.}, {0, 3, 3.}, {0, 4, 4.},
{1, 0, 1.}, {1, 1, 11.}, {1, 2, 12.}, {1, 3, 13.}, {1, 4, 14.},
{2, 0, 2.}, {2, 1, 21.}, {2, 2, 22.}, {2, 3, 23.}, {2, 4, 24.},
{3, 0, 3.}, {3, 1, 31.}, {3, 2, 32.}, {3, 3, 33.}, {3, 4, 34.},
{4, 0, 4.}, {4, 1, 41.}, {4, 2, 42.}, {4, 3, 43.}, {4, 4, 44.}}

• Run of my "Attempt 2" => – NJS Dec 16 '18 at 18:33
• Your "Attempt 2" gives the message "Import::someelem: One or more elements in the part specification "{2,3,4,5}" are not present when importing as XLSX." and the result is {{1, 1, 11.}, {1, 2, 12.}, {1, 3, 13.}, {1, 4, 14.}, {2, 1, 31.}, {2, 2, 32.}, {2, 3, 33.}, {2, 4, 34.}, $Failed,$Failed}, which does not match the data. Is that what you are asking? – Themis Dec 16 '18 at 18:41
• I don't understand Mathematica. In order to do curve fitting to obtain missing data and then to plot the results, I think I need 3-tuples of the form given by my Attempt 2, for example, (x,y,value) = (1,3,13). I note some of your results are wrong, e.g., (2,2,32) should be (2,2,22). – NJS Dec 16 '18 at 18:50
• I think you need to provide a clearer example of the data in your excel file. You give three lines of unequal length, and it appears that two commas are missing, between 14 and 2 in line #2, and between 34 and 4 in line #3. Until you clarify I do not understand how these values are supposed to be read. – Themis Dec 16 '18 at 19:06
• My revised answer now gives the same result as your Attempt 2, but I don't understand what is the problem then. The data are in the proper format for fitting a function $f(x,y)$. Isn't that what you want? – Themis Dec 16 '18 at 19:36