Converting a table to a matrix based on coordinates

Hi,

I have a table which contains 3 variables: X coordinate (X), Y coordinate (Y) and a value in point (Int). X and Y coordinates are equally spaced. I want to convert it to a matrix, in which the position of an Int value in the matrix will represent its coordinates. How to do this?

Thanks in advance

Answer 1

[chicken vector]

Be careful because Matlab uses inverted indeces so this way you have X as rows and Y as columns.

Just invert the indeces in the loop to invert this behaviour.

% Setup table for example:
x = 0:.1:.5;
y = 0:.2:1;
z = 0:5;
T = table(x', y', z', 'VariableNames', {'X', 'Y', 'Value'})

T = 6×3 table
     X      Y     Value
    ___    ___    _____

      0      0      0  
    0.1    0.2      1  
    0.2    0.4      2  
    0.3    0.6      3  
    0.4    0.8      4  
    0.5      1      5  

Now we extract some information about X and Y coordinates.

If your X and Y are in the form:

1:10

ans = 1×10

1 2 3 4 5 6 7 8 9 10

Then this part is not required.

% Initialise conversion:
nData = size(T,1);
matrixData = zeros(nData);
xStep = diff(T{[1,2],1});
yStep = diff(T{[1,2],2});
xOffset = xStep - T{1,1};
yOffset = yStep - T{1,2};

Finally we loop over the each row of the table to move the values in the matrix:

% Allocate table's values:
for j = 1 : nData
    xMatrix = int64((T{j,1} + xOffset) / xStep);
    yMatrix = int64((T{j,2} + yOffset) / yStep);
    matrixData(xMatrix, yMatrix) = T{j, 3};
end

This is the result:

% Display result:
matrixData

matrixData = 6×6

0 0 0 0 0 0 0 1 0 0 0 0 0 0 2 0 0 0 0 0 0 3 0 0 0 0 0 0 4 0 0 0 0 0 0 5

[Natalia]

Thanks for a quick answer :) I'm afraid your solution doesn't take into account that in the table there are multiple rows with the same X or Y, only X and Y combinations are unique - sorry, I should've specified it from the beginning. As an effect I get a 1050x1050 matrix, not 35x30. Is there any way to assign both X and Y to an Int value?

[chicken vector]

I can make this code more general than it is now and it will probably work for you, but if you tell me the initial values of X and Y and their step it will be much easier.

Meanwhile you can try this:

% Setup fake table for example:
xData = 0:.1:.3;
yData = -.2:.2:.2;
nX = length(xData);
nY = length(yData);
x = repmat(xData, nY, 1);
y = repmat(yData', 1, nX);
z = randi(10, nX, nY);
T = table(x(:), y(:), z(:), 'VariableNames', {'X', 'Y', 'Value'})

T = 12×3 table
     X      Y      Value
    ___    ____    _____

      0    -0.2      4  
      0       0      7  
      0     0.2      8  
    0.1    -0.2      8  
    0.1       0      4  
    0.1     0.2      9  
    0.2    -0.2     10  
    0.2       0      7  
    0.2     0.2      2  
    0.3    -0.2      4  
    0.3       0      5  
    0.3     0.2      9  

% Extract data from table:
xCoord = unique(T{:,1});
yCoord = unique(T{:,2});

% Initialise conversion:
matrixData = zeros(length(xCoord), length(yCoord));
xStep = diff(xCoord([1,2]));
yStep = diff(yCoord([1,2]));
xOffset = xStep - xCoord(1);
yOffset = yStep - yCoord(1);

% Allocate table's values:
for j = 1 : length(T{:,1})
    xMatrix = int64((T{j,1} + xOffset) / xStep);
    yMatrix = int64((T{j,2} + yOffset) / yStep);
    matrixData(xMatrix, yMatrix) = T{j, 3};
end

matrixData

matrixData = 4×3

4 7 8 8 4 9 10 7 2 4 5 9

bar3(matrixData, 'r');

[Natalia]

Here is the table (unfortunately needs some adjustments: Delimiter=" ", DecimalSeparator="," and stuff). The step is 1.5 for both X and Y.

[chicken vector]

This works for me:

% Load table:
filename = 'NG-204-205 5% wytl mapa2_fit2 IntG.txt';
T = readtable(filename, 'Decimal', ',');

% Extract data from table:
xCoord = unique(T{:,1});
yCoord = unique(T{:,2});

% Initialise conversion:
matrixData = zeros(length(xCoord), length(yCoord));
xStep = diff(xCoord([1,2]));
yStep = diff(yCoord([1,2]));
xOffset = xStep - xCoord(1);
yOffset = yStep - yCoord(1);

% Allocate table's values:
for j = 1 : length(T{:,1})
    xMatrix = int64((T{j,1} + xOffset) / xStep);
    yMatrix = int64((T{j,2} + yOffset) / yStep);
    matrixData(xMatrix, yMatrix) = T{j, 3};
end

[Natalia]

Thanks a lot! This looks exactly like what I need :)

[chicken vector]

Glad to help