3 Excel COLUMNS x,y,z imported into work space as vectors, z is a function of x,y how do i get the data into a 2d lookup table

Hi all, im sure this has been answered but i cant seem to find an answer to what is probably a simple answer. 3 columns of data, x,y,z

z is a function of x,y. im inporting the data as 3 vectors. i can seem to get it into the correct form for a look up table in simlink, My end goal is to be able to interpolate for any x,y input and get an aproxamated output for z,

Answer 1

[Jon]

Here is an example MATLAB script that you could run to assign the necessary variables in your base workspace before running your Simulink model. The idea is that you have to make a two dimensional array, where the rows correspond to your x values in your Excel Sheet and the columns the y values in your Excel Sheet, with the elements given by your third, z, column in your spreadsheet.

% script to put 2D look up table into base workspace before running Simulink
% read in the three columns of data from Excel, third column is function of
% first two
T = readtable('look2.xlsx');

% sort the rows of the table so that z values will be columnwise values of
% m by n look up array 
T = sortrows(T,{'y','x'});

% find the unique x and y entries to be used for looking up values in two d
% array in Simulink
x = unique(T.x);
y = unique(T.y);

% determine the dimension of the look up array
m = numel(x); % number of unique x entries
n = numel(y); % number of unique y entries

% make 2d look up array Z to be used in Simulink from the z column
Z = reshape(T.z,m,n);

[stephen collier]

Thanks Jon,

Thats a great help, What version of Sumilink did you save the .slx model in? Im getting a version error, I did update but still getting the error

[Jon]

I am running R2021b.

There isn't too much in my example model. We could probably figure out how to make one that you can open but here are some key screenshots.

[Jon]

Here is a screenshot of the example model

Answer 2

[stephen collier]

Thanks Jon,

That works well, however when i substitute my own data i get a reshape error, error snip attached.

[Jon]

The 2d lookup table requires that you have data sampled on a rectangular grid. The grid spacing doesn't need to be uniform, but it needs to be a grid. In other words you need to have a length m vector of x values, a length n vector of y values, and a length mxn vector of z values corresponding to every possible pair of x and y values. Your data is not sampled on a grid. Please see attached figure plotting the location of the x and y data points.

The error you are getting is because you have m = 811 (number of unique x values) and n = 360 (number of unique y values) so you should be supply 811x360 = 291960 z values (required number of rows in your Excel File) but you only have 984. Thus it is not possilbe to reshape your 984 values into a 811x360 array.

In order to use a 2-d look up table you would somehow have to preprocess the data to interpolate values on a grid. I can see this would be quite problematic though. For example, how would you make any reasonable interpolation for a value of x = 800, y = 20, where you have no z data values at all?

I think you may need to revaluate your approach here. If you only have z values for a very limited range of x and y then you need to ensure that the x and y values that you would be looking up are close to other known values. In your case it looks like you may only have data for roughly an ellipse of values in the x y plane, so unless you somehow restrict your x and y values to this region where you have samples it will not be possible to get realistic results.

[Jon]

By the way, I just noticed you put your response in as an answer. Since your entry was not actually an answer but a response to an answer it would be better in the future to put this type of response in as a comment and just keep one thread.

[stephen collier]

Thank you Jon,

your explanation has given me greater understanding of how the look up table works, it is possible to limit x and y their relationship is linear, x is an irridance plot any y is a temperature value. ill need to find another method