Check the data and replace it with corresponding values
4 views (last 30 days)
Show older comments
I have attached here an excel sheet. You can help me with this either in excel , or matlab or python. In the excel sheet if the values of node 1 are present on nodes column, within the same row under x1, y1 and z1 replace the corresponding values. The same with node 2, copy values in x2, y2 and z2 , node 3 under x3, y3 and z3 and finally node 4 under x4,y4 and z4. Kindly please help me with a code so that I can work on it faster due to huge amount of data.
Thank you
Adeline
0 Comments
Accepted Answer
Mathieu NOE
on 9 Nov 2022
hello Adeline
try this code
to make it a bit more compact and faster I remove the empty columns in your data file. the code works even if you keep these empty columns as separators but matlab table size will increase and make the process a bit slower (and the display of the table in the command window is more filled with NaN columns so less comfortable to look at).
also the original data file was labelled x / y / x instead of x / y / z
this has to be corrected on your side to make the code work. FYI I send your corrected input data file in attachment + the result (out_data.xlsx)
in some lines "node" value would appear simulteanously among node 1/2/3/4 so the result is copied in for all matching cases.
%% load file as tables
table1 = readtable("data.xlsx");
[m,n] = size(table1);
for ci =1:m % m
% get node value and make char array
node_ch = num2str(table1.nodes(ci));
node1_ch = num2str(table1.node1(ci));
node2_ch = num2str(table1.node2(ci));
node3_ch = num2str(table1.node3(ci));
node4_ch = num2str(table1.node4(ci));
if contains(node1_ch,node_ch) % copy x/y/z to x1/y1/z1
table1.x1(ci) = table1.x(ci);
table1.y1(ci) = table1.y(ci);
table1.z1(ci) = table1.z(ci);
end
if contains(node2_ch,node_ch) % copy x/y/z to x2/y2/z 2
table1.x2(ci) = table1.x(ci);
table1.y2(ci) = table1.y(ci);
table1.z2(ci) = table1.z(ci);
end
if contains(node3_ch,node_ch) % copy x/y/z to x3/y3/z3
table1.x3(ci) = table1.x(ci);
table1.y3(ci) = table1.y(ci);
table1.z3(ci) = table1.z(ci);
end
if contains(node4_ch,node_ch) % copy x/y/z to x/y/z 4
table1.x4(ci) = table1.x(ci);
table1.y4(ci) = table1.y(ci);
table1.z4(ci) = table1.z(ci);
end
end
writetable(table1,"out_data.xlsx");
6 Comments
More Answers (0)
See Also
Categories
Find more on Spreadsheets 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!