Can I used Unique to find unique x,y,z coordinates in table data?

3 views (last 30 days)
Jorge
Jorge on 4 Sep 2024
Edited: dpb on 6 Sep 2024
I have x,y,z positional data collected and arranged into a table, under two different conditions. For example, I have entries like
x
x1,y1,z1, value1, value 2, ...,0
x2,y1,z1, value3, value4, ...,0
...
x1,y1,z1,value9,value10, ...,1
x2,y1,z1,value11,value 12, ...,1
The last variable in the table is the "different' condition under which the same variables were measured at the same locations.
I want to find the unique x,y,z coordinates in the table and use these to create a summary table that shows the values under the two conditions under which the data was collected. i could use "find" and a vector of coordinates to find these on the table, but I think it would be more elegant if I obtained the unique coordinates from the table, and uses these to find the "other" entry in the table at the same x,y,z location that has the same data collected under the different condition. I've attached some of my data.
for example, I'd like to find the two entries with 1775,575,125 coordinates, and create another table that lists the coordinates, the meanZM and meanGAIpred value from the clamped=1 entry, and the meanInvCurrentfrom the clamped=0 entry.
I'll get sometihng working using "find" in the meantime....
Thank you.

Sign in to answer this question.

Accepted Answer

dpb
dpb on 4 Sep 2024
Ran in:
load matlab
whos
Name Size Bytes Class Attributes ans 1x34 68 char cmd 1x1 294 string cmdout 1x33 66 char gdsCacheDir 1x14 28 char gdsCacheFlag 1x1 8 double i 0x0 0 double managers 1x0 0 cell managersMap 0x1 8 containers.Map mshDir 1x18 36 char prefDir 1x1 170 string status 1x1 8 double summaryTable1 223x9 18861 table
tT=summaryTable1;
clearvars -except tT
head(tT)
X-pos Y-pos Z-pos meanInvCurrent meanVaACcurrent meanVaDCcurrent meanZm meanGAIpred clamped _____ _____ _____ ______________ _______________ _______________ _______ ___________ _______ 1775 575 125 56.138 56.95 0.26575 0.70237 569.5 1 1775 600 125 57.294 38.9 0.16425 1.0283 389 1 1775 625 125 57.556 26.544 0.12891 1.507 265.44 1 1775 650 125 58.437 22.581 0.12894 1.7714 225.81 1 1775 675 125 59.781 27.619 0.0625 1.4483 276.19 1 1775 700 125 61.306 41.875 0.082031 0.95522 418.75 1 1775 725 125 59.975 58.737 0.093781 0.70204 587.38 1 1775 575 125 57.163 0.1875 0.089875 Inf 1.875 0
tT.Properties.VariableNames=strrep(tT.Properties.VariableNames,'-pos','');
[g,idX,idY,idZ]=findgroups(tT.X,tT.Y,tT.Z);
g
g = 223x1
1 2 3 4 5 6 7 1 2 3
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
[idX,idY,idZ]
ans = 112x3
1775 575 125 1775 600 125 1775 625 125 1775 650 125 1775 675 125 1775 700 125 1775 725 125 1800 575 125 1800 600 125 1800 625 125
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
Then use the group number and indices into the group with rowfun or splitapply

More Answers (1)

Voss
Voss on 4 Sep 2024
Ran in:
load('matlab.mat')
T = summaryTable1
T = 223x9 table
X-pos Y-pos Z-pos meanInvCurrent meanVaACcurrent meanVaDCcurrent meanZm meanGAIpred clamped _____ _____ _____ ______________ _______________ _______________ _______ ___________ _______ 1775 575 125 56.138 56.95 0.26575 0.70237 569.5 1 1775 600 125 57.294 38.9 0.16425 1.0283 389 1 1775 625 125 57.556 26.544 0.12891 1.507 265.44 1 1775 650 125 58.437 22.581 0.12894 1.7714 225.81 1 1775 675 125 59.781 27.619 0.0625 1.4483 276.19 1 1775 700 125 61.306 41.875 0.082031 0.95522 418.75 1 1775 725 125 59.975 58.737 0.093781 0.70204 587.38 1 1775 575 125 57.163 0.1875 0.089875 Inf 1.875 0 1775 600 125 55.969 0.097656 0.13303 Inf 0.97656 0 1775 625 125 55.419 0 0.10941 Inf 0 0 1775 650 125 54.812 0.019563 0.14475 Inf 0.19562 0 1775 675 125 53.869 0 0.12903 Inf 0 0 1775 700 125 52.644 0.49703 0.50881 Inf 4.9703 0 1775 725 125 51.175 0 0.13287 Inf 0 0 1800 575 125 52.869 10.794 0.31688 3.7059 107.94 1 1800 600 125 58.263 34.294 0.19556 1.1664 342.94 1
[g_idx,result] = findgroups(T(:,[1 2 3]));
vars_1 = {'meanZm','meanGAIpred'};
vars_0 = {'meanInvCurrent'};
N = size(result,1);
vars = [vars_1 vars_0];
result(:,vars) = array2table(NaN(N,numel(vars)));
for ii = 1:N
tmp = T(g_idx == ii,:);
idx = tmp{:,'clamped'} == 1;
if any(idx)
result(ii,vars_1) = tmp(idx,vars_1);
end
if any(~idx)
result(ii,vars_0) = tmp(~idx,vars_0);
end
end
disp(result)
X-pos Y-pos Z-pos meanZm meanGAIpred meanInvCurrent _____ _____ _____ _______ ___________ ______________ 1775 575 125 0.70237 569.5 57.163 1775 600 125 1.0283 389 55.969 1775 625 125 1.507 265.44 55.419 1775 650 125 1.7714 225.81 54.812 1775 675 125 1.4483 276.19 53.869 1775 700 125 0.95522 418.75 52.644 1775 725 125 0.70204 587.38 51.175 1800 575 125 3.7059 107.94 58.744 1800 600 125 1.1664 342.94 57.569 1800 625 125 0.75641 528.81 57.144 1800 650 125 0.67298 594.38 56.594 1800 675 125 0.72984 548.06 55.6 1800 700 125 1.0615 376.81 54.144 1800 725 125 3.3197 120.5 52.525 1825 575 125 0.5805 689.06 59.138 1825 600 125 0.70828 564.75 58.256 1825 625 125 0.82443 485.19 58.063 1825 650 125 0.87146 459 57.606 1825 675 125 0.83399 479.62 56.544 1825 700 125 0.76786 529.38 54.85 1825 725 125 0.59651 670.56 52.881 1850 575 125 1.0767 371.5 58.931 1850 600 125 1.3196 303.12 58.538 1850 625 125 1.4849 269.38 58.625 1850 650 125 1.5444 259 58.256 1850 675 125 1.4877 268.88 57.075 1850 700 125 1.3295 300.88 55.075 1850 725 125 1.0953 365.19 52.738 1875 575 125 1.7482 228.81 58.919 1875 600 125 2.0343 196.62 59.031 1875 625 125 2.2285 179.5 59.431 1875 650 125 2.2989 174 59.138 1875 675 125 2.1342 190.81 57.775 1875 700 125 2.0408 196 55.431 1875 725 125 1.7544 228 52.7 1900 575 125 2.4797 161.31 59.594 1900 600 125 2.8009 142.81 60.362 1900 625 125 3.0075 133 61.169 1900 650 125 3.0963 129.19 60.962 1900 675 125 3.0075 133 59.406 1900 700 125 2.7972 143 56.619 1900 725 125 2.4683 162.06 53.231 1925 575 125 3.2258 124 61.681 1925 600 125 3.5976 111.19 63.2 1925 625 125 3.8462 104 64.487 1925 650 125 3.819 105.38 64.462 1925 675 125 3.821 104.69 62.544 1925 700 125 3.5875 111.5 59.131 1925 725 125 3.2258 124 54.925 1950 575 125 3.9653 100.88 64.794 1950 600 125 4.3896 91.125 61.688 1950 625 125 4.6658 85.731 59.325 1950 650 125 4.7478 84.25 58.437 1950 675 125 4.6539 85.95 59.031 1950 700 125 4.3686 91.562 60.856 1950 725 125 3.9555 101.12 58.106 1975 575 125 4.5661 88 59.031 1975 600 125 5.1489 77.688 54.738 1975 625 125 5.4201 73.8 52.2 1975 650 125 5.5316 72.312 51.35 1975 675 125 5.4201 73.8 52.188 1975 700 125 5.1216 78.1 54.706 1975 725 125 4.6682 85.687 58.031 2000 575 125 5.3468 74.812 53.138 2000 600 125 5.814 68.8 49.063 2000 625 125 6.0976 65.6 46.688 2000 650 125 6.1825 64.7 45.95 2000 675 125 6.0976 65.6 46.763 2000 700 125 5.8045 68.912 49.244 2000 725 125 5.2819 75.731 53.256 2025 575 125 5.8332 68.575 48.906 2025 600 125 6.3634 62.894 45.106 2025 625 125 6.6667 60 42.712 2025 650 125 6.734 59.4 42.006 2025 675 125 6.6667 60 42.95 2025 700 125 6.2696 63.8 45.381 2025 725 125 5.7513 69.55 49.256 2050 575 125 6.1538 65 46.381 2050 600 125 6.6667 60 42.575 2050 625 125 6.9565 57.5 40.306 2050 650 125 7.1048 56.3 39.681 2050 675 125 6.9565 57.5 40.575 2050 700 125 6.6129 60.513 43.094 2050 725 125 6.0895 65.688 46.862 2075 575 125 6.2902 63.594 45.456 2075 600 125 6.8847 58.1 41.719 2075 625 125 7.1272 56.125 39.5 2075 650 125 7.2727 55 38.906 2075 675 125 7.1048 56.3 39.837 2075 700 125 6.734 59.4 42.356 2075 725 125 6.1538 65 46.163 2100 575 125 6.1753 64.775 46.131 2100 600 125 6.6584 60.075 42.506 2100 625 125 6.9703 57.387 40.256 2100 650 125 7.1048 56.3 39.706 2100 675 125 6.9843 57.275 40.688 2100 700 125 6.6667 60 43.169 2100 725 125 6.0608 66 46.937 2125 575 125 5.9428 67.312 48.4 2125 600 125 6.4155 62.35 44.831 2125 625 125 6.6667 60 42.625 2125 650 125 6.788 58.931 42.038 2125 675 125 6.6667 60 43.006 2125 700 125 6.2696 63.8 45.394 2125 725 125 5.7143 70 49.294 2150 575 125 5.4201 73.8 52.231 2150 600 125 5.8802 68.025 48.5 2150 625 125 6.1538 65 46.312 2150 650 125 6.3813 62.769 45.688 2150 675 125 6.0976 65.6 46.65 2150 700 125 5.7982 68.987 49.156 2150 725 125 5.291 75.6 NaN
  10 Comments
Show 8 older comments
Jorge
Jorge on 6 Sep 2024
good stuff....thank you. Like I said earlier, all this discussion and examples have been exxtremely useful and instructional. Thank you!
dpb
dpb on 6 Sep 2024
You're welcome, as noted, I think varfun and rowfun and related functionality with splitapply and/or groupsummary is exceedingly powerful and often not or underappreciated as to just what/how much can be done with them.
And, I think teaching/extending users' acquaintance with the features available is a valuable function of the forum, not just answering a given question...

Sign in to comment.

Categories

Find more on Reporting and Database Access in Help Center and File Exchange

Tags

Products


Release

R2023b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!