2
$\begingroup$

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

https://reference.wolfram.com/language/howto/ImportASpreadsheet.html <-- Import part of a spreadsheet using the Data statement

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

Improve this question
$\endgroup$

2 Answers 2

Reset to default
1
$\begingroup$

Edited based on the comments exchanged below.

Assuming that the spreadsheet contains

enter image description here

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

First read the table:

 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.}}
Improve this answer
$\endgroup$
5
  • $\begingroup$ Run of my "Attempt 2" => $\endgroup$
    – NJS
    Dec 16, 2018 at 18:33
  • $\begingroup$ 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? $\endgroup$
    – Themis
    Dec 16, 2018 at 18:41
  • $\begingroup$ 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). $\endgroup$
    – NJS
    Dec 16, 2018 at 18:50
  • $\begingroup$ 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. $\endgroup$
    – Themis
    Dec 16, 2018 at 19:06
  • $\begingroup$ 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? $\endgroup$
    – Themis
    Dec 16, 2018 at 19:36
1
$\begingroup$

Themis provided critically helpful guidance that prompted me to create real sample array to replace the one composed of integers: 

   A = 1  B = 2  C = 3  D = 4  E = 5 <--Column indices j  
1  0      1.1    2.2    3.3    4.4   <-- y-values (Row 1)  
2  1.11   11     12     13     14   
3  2.22   21     22     23     24    <-- Z-values (3 x 4 array)  
4  3.33   31     32     33     34    
   ^-- x-values (Column A = 1)  
^-- Row indices i

in order to distinguish between indices (i, j) and coordinates (x, y), associated x and y 'vectors' are with these indices:

x(1) = 0, x(2) = 1.11, ..., x(4) = 3.33
y(1) = 0, y(2) = 1.1, ...,  y(5) = 4.4.

Within this array are the Z-values, Z(row, col) = Z(i, j).

For example, Z(3, 4) = 23 at x(3) = 2.22, y(4) = 3.3, and Z(4, 5) = 34 at x(4) = 3.33, y(5) = 4.4, which form the respective coordinate triplets (x, y, Z): (2.22, 3. 3,23) and (3.33, 4.4, 34).

Notes

  • The x,y positions now run down, across, respectively in order to conform with row, column order.
  • In my actual data the Z-values are real and there are missing values as well as values not located on a uniform grid.

Themis also provided direction on how to read this array into Mathematica and manipulate it. An outline my solution follows:

  1. Use NotebookDirectory and SetDirectory to set the path to both .nb and data files.

  2. Pull the y coord's out of the array. Below, pull the x coord's out of the transposed array. And drop the leading unwanted element from eac

    myData = Import["<put the path here> sampleDataReal.xlsx", {"Data", 1}]
    y = myData[[1]]; y = Drop[y, {1}]; ly = Length[y]; 

    myDataT = Transpose[myData]; x = myDataT[[1]]; x = Drop[x, {1}]
    lx = Length[x]; (* lx, ly set required dimensions *)

    dropY = Drop[myDataT, {1}]; setZ = Transpose[dropY]; 
    Z = Drop[setZ, {1}]; (* Coord's x, y are extracted => Z *)

    xyZform = Table[{x[[i]], y[[j]], Z[[i, j]]}, {i, 1, lx}, {j, 1, ly}]

  {{{1.11, 1.1, 11.}, {1.11, 2.2, 12.}, ..., {1.11, 4.4, 14.}},
     {{2.22, 1.1, 21.}, {2.22, 2.2, 22.}, ..., {2.22, 4.4, 24.}},
     {{3.33, 1.1, 31.}, {3.33, 2.2, 32.}, ..., {3.33, 4.4, 34.}}}

    xyZflat = Flatten[xyZform, 1]
  1. The flattened form is ready to plot, several ways: 
    lplt = ListPlot3D[xyZflat]; 
    lptplt = ListPointPlot3D[xyZflat, PlotStyle -> PointSize[0.03]];
    Show[lplt, lptplt]  (* Overlay plot with data points *)
    lcontPlt2 = ListContourPlot[xyZflat]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.