search for a string in other file and return new files based on multiple condiion

Hi and I hope you can give me some advice or suggestion,

I have bunch of files and what I would like is to create new files based on a condition. For example my input files are curva_pbc_uof_20150103.txt and curva_pbc_uof_20150104.txt and condition is in cond.txt file.

I would like to create new files where fourth column of my files matches conditions, so if I have ABO1 in curva_pbc_uof_20150103.txt and ABO1 in cond.txt, I should keep those rows, and in addition only those rows where the last column is equal to C. All other rows should be neglected.

Thanks a lot!

Answer 1

[Andrei Bobrov]

n = dir('curva_pbc_uof_*.txt');
nn = {n.name}';
k = numel(nn);
c = readtable('cond.txt','ReadVariableNames',false);
c = regexp(c.Var1,'\w*','match','once');
T_out = cell(k,1);
for ii = 1:k
    T = readtable(nn{ii},'delimiter',';','DatetimeType','text','ReadVariableNames',true);
    if ii == 1
        a0 = T.Properties.VariableNames;%if ii = 1
        a1 = a0(6:7);
    end
    T = T(:,~strncmp(a0,'Var',3));
    T.Fecha = datetime(T.Fecha,'F','dd/MM/uuuu');
    T{:,a1} = regexprep(T{:,a1},{'\.',','},{'','\.'});
    b = array2table(str2double(T{:,a1}),'v',a1);
    T(:,a1) = [];
    T(:,a1) = b;
    T_out{ii} = T(ismember(T.Unidad,c) & strcmp(T.Ofertada_O__Casada_C_,'C'),:);
end

[sensation]

Hi Andrei, thanks for the msg.

I tried to run the code but it failes on the 5th line saying that once is not allowed. I run it then without once and understand what the code does untill the very last line with ismember. I get an error:

Error using cell/ismember (line 34) Input A of class cell and input B of class cell must be cell arrays of strings, unless one is a string.

The idea is I see to have in this case two tables that match condtion in cond.txt and C.

[Andrei Bobrov]

Version of your MATLAB?

[sensation]

2016a

[sensation]

It works fine now, it was an issue with header lines only:).

If I want now to have sum of energy (column 7 in Tout) and max price(column 8) for each day and each Unidad, you suggest me to use accumarray? Thanks a lot!

[sensation]

Hi Andrei, I updated code with following statements:

[ii,jj,kk]=unique(T(:,4));
T_out{ii} = T(ismember(T.Unidad,c) & strcmp(T.Ofertada_O__Casada_C_,'C'),:);
S=[ii num2cell(accumarray(kk,[T_out{k}(:,7)]'))];
M=[ii num2cell(accumarray(kk,T_out{k}(:,8)',[],@max))]

But I am getting a message: Undefined function 'ctranspose' for input arguments of type 'table'.

Any idea how to acces and write those data?

Cheers,

Marko

[Andrei Bobrov]

Hi Marco!

Try:

S=[ii num2cell(accumarray(kk,[T_out{k}{:,7}]'))];
M=[ii num2cell(accumarray(kk,T_out{k}{:,8}',[],@max))];

or other variant

Tall = cat(1,T_out{:});
[g,T_last] = findgroups(Tall(:,[2,4]));
T_last.sum = splitapply(@sum,Tall(:,7),g);
T_last.max = splitapply(@max,Tall(:,8),g);

[sensation]

Thanks Andrei! One quick quesiton: is there a filter so I can have in T_last unique dates as rows and Unidad as column headers and their values in the table. One table for sum and one for max like:

 Max       ABO1 ABO2 etc
03/01/2015   9.5   2.2
04/01/2015   nan   2.3
etc

The headers correspond to the initial cond.txt file. So sometimes I will also have nan values for those Unidad (codes) that were not present in T_last. Thanks for any hint!

[Andrei Bobrov]

c = unique(c);
[lo,ii] = ismember(T_last{:,2},c);
[g1,dat] = findgroups(T_last(:,1));
n = strcat('v_',c(:)');
sum1 = [dat,array2table(accumarray([g1,ii],T_last.sum,[],[],nan),'v',n)];
max1 = [dat,array2table(accumarray([g1,ii],T_last.max,[],[],nan),'v',n)];

[sensation]

Great!:) thanks a lot and have a nice wk!