Check the data and replace it with corresponding values

2022 11 9
1 个回答
回答已采纳
更新时间：2023 2 17
6 次查看（30 天）

0 个投票

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

请先登录，再回答此问题。

 采纳的回答

Mathieu NOE
Mathieu NOE 2022-11-9

0 个投票

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 个评论
显示 4更早的评论 隐藏 4更早的评论

Adeline War
Adeline War 2022-11-22
Thank you @Mathieu NOE but this is not what I wanted. If you see in the table we have node 1, node 2, node 3 and node 4. Node 1 corresponnds to data that has to be copied in x1, y1 and z1, Node 2 in x2, y2 and z2 and so on. What we need to do is, pick the first row in column one(node1) and find it in "nodes" column. If it exist, we need to copy the corresponding x, y and z data to the "nodes column into x1, y1 and z1. Next we can go for column 2( node2) and check the number in the "nodes" column. If it exist, we need to put the x, y and z data in x2, y2 and z2 and this continues for all data.
For example we can see that node 339732 has values -20.2283(x), 19.398(y) and 7.21337(z). When we find this number in any of the columns node1 to node 4. These values needs to be copied in their respective places. If it is node 1 then in x1,y1 ans z1. If it is node 4 then x4, y4 and z4.
I hope you can understand my requirement? Please let me know if I need to elaborate?
Mathieu NOE
Mathieu NOE 2022-11-23
hello again
oh yes , you are right and I was completely wrong ....
Now I think this is what you wanted ...
again, I removd the empty columns in your data file to make it visually more comfortable on my screen, but that is not a big deal to work with the original data file (will do the job as well without any code modification)
attached again both excel files
updated code :
%% load file as table
table1 = readtable("data.xlsx");
% table variables : node1 node2 node3 node4 nodes x y z x1 x2 x3 x4 y1 y2 y3 y4 z1 z2 z3 z4
[m,n] = size(table1);
nodes_data = table1.nodes; % this vector contains NO NaNs
node1_data = (table1.node1);% this vector contains (trailing) NaNs
node2_data = (table1.node2);% this vector contains (trailing) NaNs
node3_data = (table1.node3);% this vector contains (trailing) NaNs
node4_data = (table1.node4);% this vector contains (trailing) NaNs
%% main loop
for ck = 1:numel(nodes_data)
% process node 1 data (to be copied to x1/y1/z1)
ind_nod1 = find(node1_data == nodes_data(ck));
if~isempty(ind_nod1)
table1.x1(ind_nod1) = table1.x(ck);
table1.y1(ind_nod1) = table1.y(ck);
table1.z1(ind_nod1) = table1.z(ck);
end
% process node 2 data (to be copied to x2/y2/z2)
ind_nod2 = find(node2_data == nodes_data(ck));
if~isempty(ind_nod2)
table1.x2(ind_nod2) = table1.x(ck);
table1.y2(ind_nod2) = table1.y(ck);
table1.z2(ind_nod2) = table1.z(ck);
end
% process node 3 data (to be copied to x3/y3/z3)
ind_nod3 = find(node3_data == nodes_data(ck));
if~isempty(ind_nod3)
table1.x3(ind_nod3) = table1.x(ck);
table1.y3(ind_nod3) = table1.y(ck);
table1.z3(ind_nod3) = table1.z(ck);
end
% process node 4 data (to be copied to x4/y4/z4)
ind_nod4 = find(node4_data == nodes_data(ck));
if~isempty(ind_nod4)
table1.x4(ind_nod4) = table1.x(ck);
table1.y4(ind_nod4) = table1.y(ck);
table1.z4(ind_nod4) = table1.z(ck);
end
end
writetable(table1,"out_data.xlsx");
Mathieu NOE
Mathieu NOE 2022-11-23
编辑：Mathieu NOE 2022-11-23
the output excel files exceed the max size (5 MB)

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

版本

R2022a

标签

Community Treasure Hunt

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

Start Hunting!

Translated by