Group data in specific potition (data redistribution)

1 次查看(过去 30 天)
I have a file with 19 columns. The 2, 8, 14 th column has letters. The 2nd has suffix *N, the 8th column has suffix *E, and the 14 column has suffix *Z.
after each column follow (horizontically) in turn four numbers belonging to each column. I want to make a code that takes first the elements of the column ending in N, then the elements of the column ending in E and finally the elements of the column ending in Z.
Ι am uploading the input file I have , and the output file I would like to create in order to understand what I want to do
Could you help me please?
Thank you in advance

回答(2 个)

Mathieu NOE
Mathieu NOE 2021-1-8
Hello Ivan
see below
I added a 3rd line in your input file (just copied the first line) so that the output I generate can be compared to your template (with 3 data lines)
So far I believe it works !
all the best
nb_of_elements = 5;
data1 = readcell('Input.xlsx');
data1_str = string(data1);
[m,n] = size(data1);
ind_N = endsWith(data1_str,'N'); % search for strings ending with N
out_N = find_my_data(data1,ind_N,nb_of_elements)
ind_E = endsWith(data1_str,'E'); % search for strings ending with E
out_E = find_my_data(data1,ind_E,nb_of_elements)
ind_Z = endsWith(data1_str,'Z'); % search for strings ending with Z
out_Z = find_my_data(data1,ind_Z,nb_of_elements)
% put the 3 together + 1st column and 1st row
out_NEZ = [data1(1,2:n); out_N out_E out_Z];
out_NEZ = [data1(:,1) out_NEZ];
writecell(out_NEZ, 'file_out.xlsx');
%%%%%%%%%%%%%%%%
function out = find_my_data(array,ind,nb_of_elements)
[m,n] = find(ind>0);
% sort in ascending order for m
[ms,inds] = sort(m);
ns = n(inds);
% create output cell array
out = [];
for ci = 1:length(ms)
mm = ms(ci);
nn = ns(ci);
out = [out ; array(mm,nn:nn+nb_of_elements)];
end
end
  4 个评论

请先登录,再进行评论。


Mathieu NOE
Mathieu NOE 2021-1-11
hello back
so I modified the code to respect empty sections -
hope it helps !
% data1 = readcell('Input.xlsx');
data1 = readcell('Input_2.xlsx');
data1_str = string(data1);
[m,n] = size(data1);
ind_N = endsWith(data1_str(2:m,:),'N'); % search for strings ending with N
[mN,nN] = find(ind_N>0);
ind_E = endsWith(data1_str(2:m,:),'E'); % search for strings ending with E
[mE,nE] = find(ind_E>0);
ind_Z = endsWith(data1_str(2:m,:),'Z'); % search for strings ending with Z
[mZ,nZ] = find(ind_Z>0);
% nb of elements are defined by length of "zeros" after the last "1" in one
% of the ind_N / ind_E / ind_Z arrays
NN = max([max(nN) max(nE) max(nZ)]);
nb_of_elements = n - NN;
out_N = find_my_data(data1(2:m,:),ind_N,nb_of_elements);
out_E = find_my_data(data1(2:m,:),ind_E,nb_of_elements);
out_Z = find_my_data(data1(2:m,:),ind_Z,nb_of_elements);
% put the 3 together + 1st column and 1st row
out_NEZ = [data1(1,2:n); out_N out_E out_Z];
out_NEZ = [data1(:,1) out_NEZ];
writecell(out_NEZ, 'file_out11.xlsx');
%%%%%%%%%%%%%%%%
function array_out = find_my_data(array,ind,nb_of_elements)
[m,n] = find(ind>0);
% sort in ascending order for m
[ms,inds] = sort(m);
ns = n(inds);
% create output cell array
array_out = cell(size(array));
for ci = 1:length(ms)
mm = ms(ci);
nn = ns(ci);
array_out(mm,2:2+nb_of_elements) = array(mm,nn:nn+nb_of_elements);
end
array_out = array_out(:,2:2+nb_of_elements); % remove the unnecessary columns
end
  7 个评论
Mathieu NOE
Mathieu NOE 2021-2-26
yet another code modification
the issue is related to the fact that your new excel file would generate "missing" values when read with readcell, so I prefer to use readtable instead.
so this is a fix for that issue, but the code is not optimal IMO.
also , your file structure is always evolving ... making the coding a bit difficult.
data1 = readtable('group_test.xlsx')
data1C = table2cell(data1);
data1_str = string(data1C);
[m,n] = size(data1);
ind_N = endsWith(data1_str(2:m,:),'N'); % search for strings ending with N
[mN,nN] = find(ind_N>0);
ind_E = endsWith(data1_str(2:m,:),'E'); % search for strings ending with E
[mE,nE] = find(ind_E>0);
ind_Z = endsWith(data1_str(2:m,:),'Z'); % search for strings ending with Z
[mZ,nZ] = find(ind_Z>0);
% nb of elements are defined by length of "zeros" after the last "1" in one
% of the ind_N / ind_E / ind_Z arrays
NN = max([max(nN) max(nE) max(nZ)]);
nb_of_elements = n - NN;
out_N = find_my_data(data1C(2:m,:),ind_N,nb_of_elements);
out_E = find_my_data(data1C(2:m,:),ind_E,nb_of_elements);
out_Z = find_my_data(data1C(2:m,:),ind_Z,nb_of_elements);
% put the 3 together + 1st column and 1st row
out_NEZ = [data1C(2:m,1) out_N out_E out_Z]; % updated code
% out_NEZ = [data1C(1,1:size(out_NEZ,2)); out_NEZ]; % updated code
writecell(out_NEZ, 'out.xlsx');
%%%%%%%%%%%%%%%%
function array_out = find_my_data(array,ind,nb_of_elements)
[m,n] = find(ind>0);
% sort in ascending order for m
[ms,inds] = sort(m);
ns = n(inds);
% create output cell array
array_out = cell(size(array));
for ci = 1:length(ms)
mm = ms(ci);
nn = ns(ci);
array_out(mm,2:2+nb_of_elements) = array(mm,nn:nn+nb_of_elements);
end
array_out = array_out(:,2:2+nb_of_elements); % remove the unnecessary columns
end

请先登录,再进行评论。

类别

Help CenterFile Exchange 中查找有关 Calendar 的更多信息

Community Treasure Hunt

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

Start Hunting!

Translated by