Check the data and replace it with corresponding values

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

Answer 1

[Mathieu NOE]

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");

[Mathieu NOE]

hello

problem solved ?

[Adeline War]

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]

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]

the output excel files exceed the max size (5 MB)

[Adeline War]

@Mathieu NOE This worked. Thank you so much.

[Mathieu NOE]

My pleasure !