Check the data and replace it with corresponding values

6 views (last 30 days)
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

Answers (1)

Mathieu NOE
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");
  4 Comments
Mathieu NOE
Mathieu NOE on 23 Nov 2022
Edited: Mathieu NOE on 23 Nov 2022
the output excel files exceed the max size (5 MB)

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!