How to write a matrix based from three columns of data using the first two columns as references?
10 views (last 30 days)
Show older comments
Alben Bagabaldo
on 10 Nov 2019
Commented: Alben Bagabaldo
on 12 Nov 2019
What's the best way to transform the following data into an array/ into an origin-destination matrix?
My original data somewhat look like the ones below:
The first column is the origin, second column is the destination, and the third column is the number of trips. (Please note that the dataset is also not sorted yet.)
The output should be the data from the third column only (number of trips), and arranged in a way that the rows is ordered with respect to the first column (origin) and the columns is ordered with respect to the second column (destination) of the original dataframe.
The output should look like the ones below (only the ones highlighted in yellow):
My data is already loaded in Matlab. Excel screenshots are for illustration purposes only of how I want to manipulate the data.
0 Comments
Accepted Answer
Image Analyst
on 10 Nov 2019
Edited: Image Analyst
on 10 Nov 2019
I believe this should work:
% Create sample data because the poster forgot to include code to generate it.
[orig, dest] = meshgrid(1000:1002, 1000:1004)
numTrips = randi(9, length(dest(:)), 1)
odTrips = [orig(:), dest(:), numTrips] % Stitch together to form a 2-D matrix.
% Now we have the missing data and we can begin:
% Extract out the columns we need from the 2-D array.
orig = odTrips(:, 1)
dest = odTrips(:, 2)
numTrips = odTrips(:, 3)
% Figure out how many rows and columns there should be.
rows = length(orig) / length(unique(dest))
columns = length(dest) / length(unique(orig))
% Now reshape
numTrips = reshape(numTrips, rows, columns)
You'll see
odTrips =
1000 1000 1
1000 1001 1
1000 1002 4
1000 1003 6
1000 1004 7
1001 1000 5
1001 1001 1
1001 1002 6
1001 1003 2
1001 1004 2
1002 1000 1
1002 1001 2
1002 1002 2
1002 1003 2
1002 1004 3
1003 1000 3
1003 1001 2
1003 1002 3
1003 1003 9
1003 1004 7
1004 1000 6
1004 1001 2
1004 1002 2
1004 1003 1
1004 1004 9
rows =
5
columns =
5
numTrips =
1 5 1 3 6
1 1 2 2 2
4 6 2 3 2
6 2 2 9 1
7 2 3 7 9
More Answers (0)
See Also
Categories
Find more on Matrices and Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!